Table Of ContentThe correct bibliographic citation for this manual is as follows:Prairie, Katherine. 2005.The Essential PROC
SQL Handbook for SAS®Users.Cary, NC: SAS Institute Inc.
The Essential PROC SQL Handbook for SAS®Users
Copyright © 2005,SAS Institute Inc.,Cary, NC, USA
ISBN 978-1-59047-571-3
All rights reserved. Produced in the United States of America.
For a hard-copy book:No part of this publication may be reproduced, stored in a retrieval system, or
transmitted, in any form or by any means, electronic, mechanical, photocopying, or otherwise, without the
prior written permission of the publisher, SAS Institute Inc.
For a Web download or e-book:Your use of this publication shall be governed by the terms established by
the vendor at the time you acquire this publication.
The scanning, uploading,and distribution of this book via the Internet or any other means without the
permission of the publisher is illegal and punishable by law. Please purchase only authorizedelectronic
editions and do not participate in or encourage electronic piracy of copyrightedmaterials. Your support of
others’ rights is appreciated.
U.S. Government Restricted Rights Notice: Use, duplication, or disclosure of this software and related
documentation by the U.S. government is subject to the Agreement with SAS Institute and the restrictions set
forth in FAR 52.227-19, Commercial Computer Software-Restricted Rights (June 1987).
SAS Institute Inc., SAS Campus Drive, Cary, North Carolina 27513-2414
1st printing, March2005
2ndprinting, August 2011
SAS®Publishing provides a complete selection of books and electronic products to help customers use SAS
software to its fullest potential. For more information about our e-books, e-learning products, CDs, and hard-
copy books, visit the SAS Publishing Web site at support.sas.com/publishingor call 1-800-727-3228.
SAS®and all other SAS Institute Inc. product or service names are registered trademarks ortrademarks of SAS
Institute Inc. in the USA and other countries. ® indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies.
Contents
Foreword vii
Chapter 1 Introduction to PROC SQL 1
A little history 2
PROC SQL statements 3
Why learn PROC SQL? 3
Adaptability 7
When not to use PROC SQL 9
The PROC SQL advantage 10
Chapter 2 Working with PROC SQL 11
PROC SQL concepts 12
PROC SQL versus SAS DATA Step 20
PROC SQL naming convention 25
PROC SQL comments 27
LIBNAME statement 28
DESCRIBE statement 30
Working with databases 31
PROC SQL options 35
PROC SQL output options 41
Chapter 3 Understanding the SELECT Statement 45
SELECT statement 46
SQL Pass-Through Facility SELECT statement 50
SELECT and FROM clauses 52
SELECT clause 54
FROM clause 69
WHERE clause 72
iv Contents
ORDER BY clause 97
GROUP BY clause 102
HAVING clause 115
ON clause 123
USING clause 125
INTO clause 127
Putting it all together 128
Chapter 4 Functions 131
SAS functions and PROC SQL 131
Single functions 135
Summary Functions 150
Chapter 5 Working with Two or More Tables 161
Join operations 162
Set operators 210
Subqueries 231
Putting it all together 261
Chapter 6 Creating and Managing Tables and Views 265
Why use tables and views? 266
Creating tables 271
Creating views 292
Effective use of indexes 302
Modifying tables and views 331
Putting it all together 376
Chapter 7 Building Interactive Applications 385
Validating SQL statements 386
Use of macro variables 386
SAS Component Language (SCL) for user input 411
htmSQL 419
Dictionary tables 435
Putting it all together 448
Contents v
Chapter 8 PROC SQL Enhancements in Version 9 453
Working with PROC SQL 454
Understanding the SELECT statement 461
Functions 462
Creating and maintaining tables 462
Interactive applications 462
SAS/ACCESS interface 465
Appendix A Bluesky Publishing Tables 469
Appendix B Bluesky Publishing Tables 481
Appendix C Information for Database Users 493
Quick References 525
References 543
Index 545
vi Contents
Foreword
This book is designed to introduce PROC SQL to programmers who are working with
database-specific SQL and who would like to try working with SAS. For those with SAS
experience, it is an introduction to the powerful PROC SQL toolkit. There are some
examples comparing PROC SQL statements to SAS procedures and DATA step
statements. However, the focus is on the accomplishment of common tasks using
PROC SQL.
For those experienced in database-specific SQL, it provides a gentle introduction to the
flexibility offered by SAS. Within a single SAS session a user can connect to multiple
databases simultaneously, issuing queries with added flexibility and functionality unique
to SAS. Although this book does not cover other SAS procedures and Base SAS, it does
introduce some SAS concepts outside of PROC SQL.
For all users, there are examples of every feature of PROC SQL and plenty of tips and
hints. There is sufficient coverage of database concepts that you should have no
difficulty accessing tables stored in relational databases using PROC SQL. Information
on both the SQL Pass-Through Facility and the SAS/ACCESS LIBNAME engine are
provided.
A Quick Reference has been provided at the back of the book. In this section you will
find key details for every major concept and term presented in the book together with
page references.
Technical notes
A single set of tables with data applicable to a small ficticious book publishing company,
Bluesky Publishing, is used throughout the book. Table names have been italicized to
distinguish them in written paragraphs. Details on the table structure and data are
included in Appendix A, “Bluesky Publishing Tables.”
Throughout the program examples, capitalization, indentation, and space have been
added for readability. PROC SQL statements are not case-sensitive; only quoted strings
included in SQL statements are case-sensitive. In SQL statements a single space is
viii
required between and after keywords, aliases, and other character elements that are not
separated by a delimiter such as a quote, parenthesis, or comma. Additional space added
for readability does not affect the execution of an SQL statement.
PROC SQL, QUIT, LIBNAME, and TITLE statements have been included in examples
where appropriate for completeness. A PROC SQL statement invokes the SQL
procedure, and each subsequent statement ending in a semicolon is executed. PROC
SQL remains active until a QUIT statement is encountered.
A LIBNAME statement can be issued before or after the PROC SQL statement. It
remains in effect for the duration of the SAS session unless it is cleared or another
LIBNAME statement is issued changing the libref. TITLE statements can also be issued
before or after the PROC SQL statement and remain in effect until changed.
When syntax is provided, an ellipsis (…) is used to indicate more of the same option
repeated any number of times. A single vertical bar (|) is used to separate items, each of
which is a valid option. Optional clauses are enclosed in angle brackets (<>) within
statement syntax.
In the code used in this book the following OPTIONS statement was issued at the start of
the SAS session to control the PROC SQL output shown throughout the book unless
otherwise indicated.
OPTIONS linesize=256 nocenter nonumber stimer nodate;
All PROC SQL statements included in this book were run in both SAS Version 8 and
SAS Version 9. Testing in SAS Version 9 was conducted in all versions up to and
including SAS Version 9.1.2 (TS1M2). Enhancements made to PROC SQL in SAS
Version 9 are outlined in Chapter 8, “PROC SQL Enhancements in SAS Version 9.”
Most of the SQL statements in this book can also be run in earlier SAS versions such as
SAS Version 6 or 7.
1
C h a p t e r
Introduction to PROC SQL
A little history 2
PROC SQL statements 3
Why learn PROC SQL? 3
Reporting that suits your needs 4
The power to create 4
Ease of maintenance 5
Security 5
Data integrity checks 6
Optimized performance 6
Adaptability 7
Flexibility in a changing environment 7
Table merges and Cartesian products 7
Fuzzy logic 7
Criteria built from stored data 8
Work within a database from a SAS session 8
Interactive and Web-based applications 9
When not to use PROC SQL 9
The PROC SQL advantage 10
2 The Essential PROC SQL Handbook
A little history
SQL is a powerful, flexible, fourth-generation sublanguage that enables complex
processing through a few simple statements. You need only to indicate the desired
outcome rather than outline each of the steps necessary to reach that outcome because
SQL is a nonprocedural language. SQL statements allow for the complete creation,
maintenance, and reporting of relational database systems using English-like statements.
In the mid-1970s the Structured Query Language (SQL) was developed by IBM
researchers in San Jose, California, to support a new relational database model. In June
1970, Dr. E. F. Codd, a researcher with IBM, published his mathematical theory of data
management in a paper entitled "A Relational Model of Data for Large Shared Data
Banks." His ideas resulted in the definition of a new form of data storage structure, a
table consisting of rows and columns. The relational database model was thus born from
tables and the relationships between tables.
SQL was designed to enable access to data stored in a relational database. It allows you
to create, alter, and delete tables as well as modify or delete existing records or add new
records to tables.
By the late 1980s and early 1990s, each database vendor had its own version of SQL. In
an effort to minimize the inconsistencies and provide portability of SQL statements, the
American National Standards Institute (ANSI) developed a set of international standards
to be applied to the language. Several standards have been published by ANSI since
1986, including SQL-89, SQL-92, SQL-99 and SQL-2003.
Each successive SQL language release extends functionality. However, the foundations
of the SQL language have remained mostly unchanged. Vendors that are compliant with
the ANSI SQL-92 standard, for example, are also compliant with the SQL-99 core
function standards.
The power and ease-of-use of SQL has resulted in its use in hundreds of database
products today. Companies such as Oracle, Microsoft, Sybase, and IBM depend heavily
on SQL in their database products regardless of operating system. As a result, anyone
working with databases today must be proficient in SQL. The ANSI standards have
resulted in a set of more or less common statements with agreed upon functionality from
each vendor. However, many different ideas and syntactical differences are found in
each flavor of SQL.
PROC SQL underwent major change in SAS Version 8, resulting in a more versatile
procedure that is also more closely in line with the ANSI SQL-92 standard. The new
version extends the functionality of the SQL language with elements from Base SAS.
