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