The correct bibliographic citation for this manual is as follows: Schreier, Howard. 2008. PROC SQL by Example: Using SQL within SAS®. Cary, NC: SAS Institute Inc. PROC SQL by Example: Using SQL within SAS® Copyright © 2008, SAS Institute Inc., Cary, NC, USA ISBN 978-1-59994-297-1 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, September 2008 2nd printing, October 2012 SAS Institute Inc. 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 Books Web site at support.sas.com/bookstore or 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 Acknowledgments vii Chapter 1 Introduction 1 1.1 More about SQL 2 1.2 More about This Book 5 Chapter 2 Basic Building Blocks 11 2.1 The Simplest SELECT Statement 13 2.2 A More Selective SELECT 15 2.3 Storing Results 16 2.4 Column Subsets 17 2.5 New Columns 19 2.6 Aggregation 20 2.7 Conditionality 26 2.8 Filtering 28 2.9 Reordering Rows 32 2.10 Elimination of Duplicates 33 2.11 Summary 35 Chapter 3 More Building Blocks 37 3.1 Combining Summary Statistics with Original Detail 38 3.2 Summary Statistics Based on Distinct Values 43 3.3 Preprocessing the Source with Inline Views 46 3.4 Summary 51 iv Contents Chapter 4 Joins 53 4.1 Avoiding Ambiguity in Column References 54 4.2 The Simplest Merges and Joins 56 4.3 Matching with Nonrepeating Keys 59 4.4 Matching with Repeating Keys 68 4.5 More about Joins and Merges 75 4.6 More about Joins 77 4.7 Summary 79 Chapter 5 Subqueries 81 5.1 Contexts That Expect Subqueries 84 5.2 General Usage of Subqueries 93 5.3 Summary 95 Chapter 6 Set Operators 97 6.1 The Contrast between Joins and Set Operators 98 6.2 Set Operators: Preview 99 6.3 Concatenation and Interleaving with OUTER UNION 104 6.4 Data Type Compatibility 110 6.5 Overview: UNION, INTERSECT, and EXCEPT 111 6.6 UNION 113 6.7 INTERSECT 123 6.8 EXCEPT 126 6.9 Summary 129 Contents v Chapter 7 Global Statements, Options, and Session Management 131 7.1 Global Statements 133 7.2 PROC SQL Options 136 7.3 Summary 139 Chapter 8 Using the Macro Facility with PROC SQL 141 8.1 Generating PROC SQL Code 141 8.2 Populating Macro Variables 144 8.3 Summary 160 Chapter 9 Table Maintenance and Alternate Strategies 161 9.1 Environment for Examples 164 9.2 Distinguishing Persistence from Replacement 166 9.3 Life Cycle of a Table 168 9.4 Data Maintenance 173 9.5 Metadata Maintenance 183 9.6 Changing Structure 185 9.7 Changing Features 187 9.8 Summary 203 Chapter 10 Views 205 10.1 Defining Views 207 10.2 Using Views 207 10.3 Inspecting Views 210 10.4 Changing a Table via a View 212 10.5 Managing Library References 214 10.6 Summary 218 vi Contents Chapter 11 PROC SQL as a Report Generator 219 11.1 Simple Reports 220 11.2 Complex Reports 222 11.3 Reports with Long Character Strings 225 11.4 PROC SQL and the Output Delivery System 229 11.5 Summary 231 Chapter 12 Mixed Solutions 233 12.1 Example: Schedule Matrix 234 12.2 Example: Identifying Spikes in a Series 238 12.3 Example: Using PROC TRANSPOSE to Normalize 242 12.4 Summary 248 Chapter 13 Performance Tuning 249 13.1 Resource Example: The Effect of an Index 250 13.2 Code Example: The Advantage of Equijoins 251 13.3 Summary 254 Chapter 14 Documentation Roadmap 255 14.1 Where to Start? 256 14.2 Following Cross References 261 14.3 The Three Expressions Revisited 264 14.4 Could It Be More Logical? 266 14.5 Summary 268 Appendix A SASHELP.CLASS Data Set 271 Appendix B Online Resources 273 Index 275 Acknowledgments My name is the only one on the cover, but that doesn't mean I did it by myself. I thank the people at SAS Press and the reviewers whom they enlisted; all have been consistently helpful. I also thank those who taught me and, especially, those who encouraged me to teach myself. viii 1 C h a p t e r Introduction 1.1 More about SQL 2 1.2 More about This Book 5 SAS defines Structured Query Language (SQL) as “a standardized, widely used language that retrieves data from and updates data in tables and the views that are based on those tables” (see Base SAS 9.2 Procedures Guide: Procedures: The SQL Procedure: Overview). SQL is not an exclusive feature of SAS; it has been implemented by many vendors, and is especially widespread in the relational database management system (RDBMS) world. The SAS implementation of SQL is available in the SQL procedure (PROC SQL), part of Base SAS. Some but not all PROC SQL capabilities are paralleled in the DATA step and in other SAS procedures. Thus, PROC SQL can be employed as a substitute for other elements of SAS or as a complement to those elements. This book is intended for readers who are familiar with SAS but not with SQL, and who want to add PROC SQL to their SAS toolkits. It will also be useful to those familiar with other implementations of SQL who want to learn SAS. 2 PROC SQL by Example: Using SQL within SAS 1.1 More about SQL PROC SQL is different from other SAS components and different from other software implementations of SQL. Standards and Extensions American National Standards Institute (ANSI) standard SQL is not a complete and self- sufficient language. For example, consider the definition quoted in the preceding section; it mentions retrieval and updating of data held in tables, but says nothing about how a table is populated in the first place. There are two possible approaches to the incompleteness. One is to include extensions (capabilities not required by the standard) in an SQL implementation to make the language more complete. Thus, for example, RDBMS vendors typically extend SQL with tools to import and export large volumes of data. The other approach, and the one followed by SAS, is to embed SQL into a language that provides the missing features. So, for example, a SAS application might use a PROC IMPORT step to load data, before turning to PROC SQL for processing and analysis of that data. The implementation of SQL in SAS 9.2 PROC SQL does not fully comply with the current ANSI standard for SQL. On the other hand, PROC SQL includes some features not required by the standard. Reference: For details about PROC SQL and the ANSI standard, see Base SAS 9.2 Procedures Guide: Procedures: The SQL Procedure: PROC SQL and the ANSI Standard. Fundamental Differences between SQL and the DATA Step The largest part of this book is devoted to explaining and illustrating the features of PROC SQL and identifying and qualifying parallels with non-SQL SAS counterparts to those features. Those explanations and examples deal with particular language elements. Before delving into that sort of detail, we should look at some general characteristics that distinguish PROC SQL from other parts of SAS. These distinctions range from the rather mundane to the almost profound. Comma versus White Space Separation In most parts of SAS, a series of like elements (such as variable names) is coded using white space (blanks, tabs, or new lines) for separation. In SQL, elements in such a series are separated by commas (with optional white space permitted in addition to, but not instead of, each comma).