SAS 9.2 SQL Procedure: User's Guide

1 SAS SQL ProcedureUser s 12/17/09 3:43:14 PMThe correct bibliographic citation for this manual is as follows: SAS Institute Inc.,SAS sql procedure user s Guide . Cary, NC: SAS Institute Inc., sql procedure user s GuideCopyright 2009, SAS Institute Inc., Cary, NC, 978 1 599944 853 9 All rights reserved. Produced in the United States of a hard-copy book:No part of this publication may be reproduced, stored in aretrieval system, or transmitted, in any form or by any means, electronic, mechanical,photocopying, or otherwise, without the prior written permission of the publisher, SASI nstitute a Web download or e-book:Your use of this publication shall be governed by theterms established by the vendor at the time you acquire this Government Restricted Rights , duplication, or disclosure of thissoftware and related documentation by the government is subject to the Agreementwith SAS Institute and the restrictions set forth in FAR 19 Commercial ComputerSoftware-Restricted Rights (June 1987).

2 SAS Institute Inc., SAS Campus Drive, Cary, North Carolina electronic book, February 20091st printing, February 2009 SAS Publishing provides a complete selection of books and electronic products to helpcustomers use SAS software to its fullest potential. For more information about oure-books, e-learning products, CDs, and hard-copy books, visit the SAS Publishing Web call and all other SAS Institute Inc. product or service names are registered trademarksor trademarks of SAS Institute Inc. in the USA and other countries. indicates brand and product names are registered trademarks or trademarks of theirrespective 1 Introduction to the sql procedure 1 What Is SQL?

3 1 What Is the sql procedure ?1 Terminology2 Comparing PROC SQL with the SAS data Step3 Notes about the Example Tables5 chapter 2 Retrieving data from a Single Table 11 Overview of the SELECT Statement12 Selecting Columns in a Table14 Creating New Columns18 Sorting Data25 Retrieving Rows That Satisfy a Condition31 Summarizing Data40 Grouping Data47 Filtering Grouped Data51 Validating a Query53 chapter 3 Retrieving data from Multiple Tables 55 Introduction56 Selecting data from More Than One Table by Using Joins56 Using Subqueries to Select Data74 When to Use Joins and Subqueries80 Combining Queries with Set Operators81 chapter 4 Creating and Updating Tables and Views 89

4 Introduction90 Creating Tables90 Inserting Rows into Tables93 Updating data Values in a Table96 Deleting Rows98 Altering Columns99 Creating an Index102 Deleting a Table103 Using sql procedure Tables in SAS Software103 Creating and Using Integrity Constraints in a Table103 Creating and Using PROC SQL Views106 chapter 5 Programming with the sql procedure 111 Introduction112 Using PROC SQL Options to Create and Debug Queries112 Improving Query Performance116ivAccessing SAS System Information by Using DICTIONARY Tables120 Using SAS data Set Options with PROC SQL127 Using PROC SQL with the SAS Macro Facility128 formatting PROC SQL Output by Using the REPORT Procedure136 Accessing a DBMS with SAS/ACCESS Software137 Using the Output Delivery System with PROC SQL142 chapter 6 Practical Problem-Solving with PROC SQL 145 Overview146 Computing a Weighted Average146 Comparing Tables148 Overlaying Missing data Values150 Computing Percentages within Subtotals152 Counting Duplicate Rows in a Table153 Expanding Hierarchical data in a Table155 Summarizing data in Multiple Columns157 Creating a Summary Report158 Creating a Customized Sort Order161 Conditionally Updating a Table163 Updating a Table with Values from Another Table165 Creating and Using Macro Variables167 Using PROC SQL Tables in Other SAS Procedures170

5 Appendix 1 Recommended Reading 175 Recommended Reading175 Glossary 177 Index 1811 CHAPTER1 Introduction to the SQLP rocedureWhat Is SQL?1 What Is the sql procedure ?1 Terminology2 Tables2 Queries2 Views3 Null Values3 Comparing PROC SQL with the SAS data Step3 Notes about the Example Tables5 What Is SQL?Structured Query Language (SQL) is a standardized, widely used language thatretrieves and updates data in relational tables and a mathematical concept that is similar to the mathematical concept of aset. Relations are represented physically as two-dimensional tables that are arrangedin rows and columns. Relational theory was developed by E. F. Codd, an IBMresearcher, and first implemented at IBM in a prototype called System R.

6 Thisprototype evolved into commercial IBM products based on SQL. The Structured QueryLanguage is now in the public domain and is part of many vendors Is the sql procedure ?The sql procedure is the Base SAS implementation of Structured Query SQL is part of Base SAS software, and you can use it with any SAS data set(table). Often, PROC SQL can be an alternative to other SAS procedures or the data step. You can use SAS language elements such as global statements, data set options,functions, informats, and formats with PROC SQL just as you can with other SASprocedures. PROC SQL can generate reports generate summary statistics retrieve data from tables or views combine data from tables or views create tables, views, and indexes update the data values in PROC SQL tables update and retrieve data from database management system (DBMS) tables2 Terminology chapter 1 modify a PROC SQL table by adding, modifying, or dropping SQL can be used in an interactive SAS session or within batch programs, andit can include global statements, such as TITLE and PROC SQLtableis the same as a SAS data file.

7 It is a SAS file of type SQL tables consist of rows and columns. The rows correspond to observations inSAS data files, and the columns correspond to variables. The following table listsequivalent terms that are used in SQL, SAS, and traditional data TermSAS TermData Processing TermtableSAS data filefilerowobservationrecordcolumnvariab lefieldYou can create and modify tables by using the SAS data step, or by using the PROCSQL statements that are described in chapter 4, Creating and Updating Tables andViews, on page 89. Other SAS procedures and the data step can read and updatetables that are created with PROC data files can have a one-level name or a two-level name.

8 Typically, the namesof temporary SAS data files have only one level, and the data files are stored in theWORK library. PROC SQL assumes that SAS data files that are specified with aone-level name are to be read from or written to the WORK library, unless you specify aUSER library. You can assign a user library with a LIBNAME statement or with theSAS system option user =. For more information about how to work with SAS datafiles and libraries, see Temporary and Permanent SAS data Sets in theBase SASP rocedures tablesare tables that were created with other software vendors databasemanagement systems. PROC SQL can connect to, update, and modify DBMS tables,with some restrictions.

9 For more information, see Accessing a DBMS with SAS/ACCESS Software on page retrieve data from a table, view, or DBMS. A query returns aquery result,which consists of rows and columns from a table. With PROC SQL, you use a SELECT statement and its subordinate clauses to form a query. chapter 2, Retrieving Datafrom a Single Table, on page 11 describes how to build a to the sql procedure Comparing PROC SQL with the SAS data Step 3 ViewsPROC SQL views do not actually contain data as tables do. Rather, a PROC SQLview contains a stored SELECT statement or query. The query executes when you usethe view in a SAS procedure or data step. When a view executes, it displays data thatis derived from existing tables, from other views, or from SAS/ACCESS views.

10 OtherSAS procedures and the data step can use a PROC SQL view as they would any SASdata file. For more information about views, see chapter 4, Creating and UpdatingTables and Views, on page :When you process PROC SQL views between a client and a server, getting thecorrect results depends on the compatibility between the client and server more information, see Accessing a SAS View in theSAS/CONNECT user sGuide. Null ValuesAccording to the ANSI Standard for SQL, a missing value is called anull the same as a blank or zero value. However, to be compatible with the rest of SAS,PROC SQL treats missing values the same as blanks or zero values, and considers allthree to be null values.