O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required