SAS 9.3 SQL Procedure User's Guide

1 SAS SQL ProcedureUser s GuideSAS DocumentationThe correct bibliographic citation for this manual is as follows: SAS Institute Inc 2011. SAS SQL Procedure user s Guide . Cary, NC: SAS Institute SQL Procedure user s GuideCopyright 2011, SAS Institute Inc., Cary, NC, USAISBN 978-1-60764-892-5 All rights reserved. Produced in the United States of a hardcopy 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 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 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.

2 Please purchase only authorized electronic editions and do not participate in or encourage electronic piracy of copyrighted materials. Your support of others' rights is Government License Rights; Restricted Rights: Use, duplication, or disclosure of this software and related documentation by the government is subject to the Agreement with SAS Institute and the restrictions set forth in FAR 19 Commercial Computer Software-Restricted Rights (June 1987).SAS Institute Inc., SAS Campus Drive, Cary, North Carolina 3, May 2017 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 or call and all other SAS Institute Inc.

3 Product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. indicates USA brand and product names are registered trademarks or trademarks of their respective This Book .. ixWhat s New in the SAS SQL Procedure .. xiiiRecommended Reading .. xviiPART 1 Using the SQL Procedure1 Chapter 1 Introduction to the SQL Procedure .. 3 What Is SQL? .. 3 What Is the SQL Procedure ? .. 3 Terminology .. 4 Comparing PROC SQL with the SAS DATA Step .. 5 Notes about the Example Tables .. 7 Chapter 2 Retrieving Data from a Single Table .. 19 Overview of the SELECT Statement .. 20 Selecting Columns in a Table .. 22 Creating New Columns .. 27 Sorting Data .. 37 Retrieving Rows That Satisfy a Condition .. 44 Summarizing Data .. 56 Grouping Data .. 64 Filtering Grouped Data .. 69 Validating a Query.

4 71 Chapter 3 Retrieving Data from Multiple Tables .. 73 Introduction .. 73 Selecting Data from More than One Table by Using Joins .. 74 Using Subqueries to Select Data .. 95 When to Use Joins and Subqueries .. 101 Combining Queries with Set Operators .. 102 Chapter 4 Creating and Updating Tables and Views .. 109 Introduction .. 110 Creating Tables .. 110 Inserting Rows into Tables .. 114 Updating Data Values in a Table .. 118 Deleting Rows .. 120 Altering Columns .. 121 Creating an Index .. 124 Deleting a Table .. 126 Using SQL Procedure Tables in SAS Software .. 126 Creating and Using Integrity Constraints in a Table .. 126 Creating and Using PROC SQL Views .. 129 Chapter 5 Programming with the SQL Procedure .. 135 Introduction .. 136 Using PROC SQL Options to Create and Debug Queries .. 136 Improving Query Performance.

5 140 Accessing SAS System Information by Using DICTIONARY Tables .. 144 Using SAS Data Set Options with PROC SQL .. 151 Using PROC SQL with the SAS Macro Facility .. 152 Formatting PROC SQL Output by Using the REPORT Procedure .. 160 Accessing a DBMS with SAS/ACCESS Software .. 162 Using the Output Delivery System with PROC SQL .. 169 Chapter 6 Practical Problem-Solving with PROC SQL .. 171 Overview .. 172 Computing a Weighted Average .. 172 Comparing Tables .. 174 Overlaying Missing Data Values .. 176 Computing Percentages within Subtotals .. 179 Counting Duplicate Rows in a Table .. 181 Expanding Hierarchical Data in a Table .. 183 Summarizing Data in Multiple Columns .. 186 Creating a Summary Report .. 188 Creating a Customized Sort Order .. 191 Conditionally Updating a Table .. 194 Updating a Table with Values from Another Table.

6 197 Creating and Using Macro Variables .. 199 Using PROC SQL Tables in Other SAS procedures .. 203 PART 2 SQL Procedure Reference207 Chapter 7 SQL Procedure .. 209 Overview .. 210 Syntax: SQL Procedure .. 212 Examples: SQL Procedure .. 246 Chapter 8 SQL SELECT Statement Clauses .. 293 Dictionary .. 293 Chapter 9 SQL Procedure Components .. 307 Overview .. 307 Dictionary .. 308 PART 3 Appendixes361 Appendix 1 SQL Macro Variables and System Options .. 363 Dictionary .. 363 Appendix 2 PROC SQL and the ANSI Standard .. 375 Appendix 3 Source for SQL Examples .. 381 Overview .. 381 EMPLOYEES .. 381 HOUSES .. 382 MATCH_11 .. 393 STORES .. 394 SURVEY .. 394 Glossary .. 395 Index .. 399 ContentsviiviiiContentsAbout This BookSyntax Conventions for the SAS LanguageOverview of Syntax Conventions for the SAS LanguageSAS uses standard conventions in the documentation of syntax for SAS language elements.

7 These conventions enable you to easily identify the components of SAS syntax. The conventions can be divided into these parts: syntax components style conventions special characters references to SAS libraries and external filesSyntax ComponentsThe components of the syntax for most language elements include a keyword and arguments. For some language elements, only a keyword is necessary. For other language elements, the keyword is followed by an equal sign (=).keywordspecifies the name of the SAS language element that you use when you write your program. Keyword is a literal that is usually the first word in the syntax. In a CALL routine, the first two words are the following examples of SAS syntax, the keywords are the first words in the syntax:CHAR (string, position)CALL RANBIN (seed, n, p, x);ALTER (alter-password)BEST <data-set-name>In the following example, the first two words of the CALL routine are the keywords:CALL RANBIN(seed, n, p, x)The syntax of some SAS statements consists of a single keyword without arguments:DO.

8 SAS code ..ixEND;Some system options require that one of two keyword values be specified:DUPLEX | NODUPLEX argumentspecifies a numeric or character constant, variable, or expression. Arguments follow the keyword or an equal sign after the keyword. The arguments are used by SAS to process the language element. Arguments can be required or optional. In the syntax, optional arguments are enclosed between angle the following example, string and position follow the keyword CHAR. These arguments are required arguments for the CHAR function:CHAR (string, position)Each argument has a value. In the following example of SAS code, the argument string has a value of 'summer', and the argument position has a value of 4:x=char('summer', 4);In the following example, string and substring are required arguments, while modifiers and startpos are (string, substring <,modifiers> <,startpos>Note:In most cases, example code in SAS documentation is written in lowercase with a monospace font.)

9 You can use uppercase, lowercase, or mixed case in the code that you ConventionsThe style conventions that are used in documenting SAS syntax include uppercase bold, uppercase, and italic:UPPERCASE BOLD identifies SAS keywords such as the names of functions or statements. In the following example, the keyword ERROR is written in uppercase bold:ERROR<message>;UPPERCASE identifies arguments that are the following example of the CMPMODEL= system option, the literals include BOTH, CATALOG, and XML:CMPMODEL = BOTH | CATALOG | XMLitalicsidentifies arguments or values that you supply. Items in italics represent user -supplied values that are either one of the following: nonliteral arguments In the following example of the LINK statement, the argument label is a user -supplied value and is therefore written in italics:LINK label; nonliteral values that are assigned to an argument In the following example of the FORMAT statement, the argument DEFAULT is assigned the variable default-format:FORMAT = variable-1 <.

10 , variable-n format > <DEFAULT = default-format>;xAbout This BookItems in italics can also be the generic name for a list of arguments from which you can choose (for example, attribute-list). If more than one of an item in italics can be used, the items are expressed as item-1, .., CharactersThe syntax of SAS language elements can contain the following special characters:=an equal sign identifies a value for a literal in some language elements such as system the following example of the MAPS system option, the equal sign sets the value of MAPS:MAPS = location-of-maps< >angle brackets identify optional arguments. Any argument that is not enclosed in angle brackets is the following example of the CAT function, at least one item is required:CAT (item-1 <, .., item-n>)|a vertical bar indicates that you can choose one value from a group of values.