Chapter 5
Programming with the SQL
Procedure
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Using PROC SQL Options to Create and Debug Queries . . . . . . . . . . . . . . . . . . . 138
Overview of Using PROC SQL Options to Create and Debug Queries . . . . . . . . . 138
Restricting Row Processing with the INOBS= and OUTOBS= Options . . . . . . . . 139
Limiting Iterations with the LOOPS= Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Checking Syntax with the NOEXEC Option and the VALIDATE Statement . . . . 139
Expanding SELECT * with the FEEDBACK Option . . . . . . . . . . . . . . . . . . . . . . 140
Timing PROC SQL with the STIMER Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Resetting PROC SQL Options with the RESET Statement . . . . . . . . . . . . . . . . . . 141
Improving Query Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Overview of Improving Query Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Using Indexes to Improve Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Using the Keyword ALL in Set Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Omitting the ORDER BY Clause When Creating Tables and Views . . . . . . . . . . . 143
Using In-Line Views versus Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Comparing Subqueries with Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Using WHERE Expressions with Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Optimizing the PUT Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Replacing References to the DATE, TIME, DATETIME, and
TODAY Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Disabling the Remerging of Data When Using Summary Functions . . . . . . . . . . . 146
Using Column Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Overview of Column Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Column Alias Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
Using the CALCULATED Keyword with Column Aliases . . . . . . . . . . . . . . . . . . 148
Accessing SAS System Information By Using DICTIONARY Tables . . . . . . . . . . 149
What Are DICTIONARY Tables? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Retrieving Information about DICTIONARY Tables and Sashelp Views . . . . . . . 151
Using DICTIONARY.Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Using DICTIONARY.Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
DICTIONARY Tables and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Using SAS Data Set Options with PROC SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Using PROC SQL with the SAS Macro Facility . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Overview of Using PROC SQL with the SAS Macro Facility . . . . . . . . . . . . . . . . 157
Creating Macro Variables in PROC SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Concatenating Values in Macro Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Defining Macros to Create Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
137

Get SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.