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

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