The Essential PROC SQL Handbook for SAS Users

Katherine Prairie

Description

Navigate the world of the powerful SQL procedure with Katherine Prairie's Essential PROC SQL Handbook for SAS Users. Written in an easy-to-use, logical format, this comprehensive reference focuses on the functionality of the procedure, as well as the accomplishment of common tasks using PROC SQL, enabling readers to quickly develop and enhance their SQL skills. Features include more than 300 examples of PROC SQL code, plus queries and diagrams showing how the statements are processed, tips and techniques highlighting "need-to-know" concepts, and an appendix designed specifically for SQL Pass-Through Facility and SAS/ACCESS users. This practical guide is written for SAS users of all levels who want to learn how to integrate the SQL procedure into their Base SAS and/or SAS/ACCESS programs as well as SQL programmers who want to adapt their current skills to SAS.

This book is part of the SAS Press program.

Sujets

• Macro

Informations

• Publié par: SAS Institute
• Date de parution: 21 juin 2005
• Nombre de lectures: 2
• EAN13: 9781612906881
• Langue: English
• Poids de l'ouvrage: 3 Mo

Extrait

The 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 authorized electronic editions and do not participate in or encourage electronic piracy of copyrighted materials. 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, March 2005 2nd printing, 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 atsupport.sas.com/publishingor call 1-800-727-3228. ® SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks 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

ivContents

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

Contentsv

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 B481Bluesky Publishing Tables Appendix C493Information for Database Users Quick References 525 References 543 Index 545

viContents

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

2The 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.