SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

Using the PROC SQL Automatic Macro Variables . . . . . . . . . . . . . . . . . . . . . . . . 162

Formatting PROC SQL Output By Using the REPORT Procedure . . . . . . . . . . . 165

Accessing a DBMS with SAS/ACCESS Software . . . . . . . . . . . . . . . . . . . . . . . . . . 167

Overview of Accessing a DBMS with SAS/ACCESS Software . . . . . . . . . . . . . . 167

Connecting to a DBMS By Using the LIBNAME Statement . . . . . . . . . . . . . . . . 168

Connecting to a DBMS By Using the SQL Procedure Pass-Through Facility . . . 171

Updating PROC SQL and SAS/ACCESS Views . . . . . . . . . . . . . . . . . . . . . . . . . . 173

Using the Output Delivery System with PROC SQL . . . . . . . . . . . . . . . . . . . . . . . 174

Introduction

This section shows you how to do the following:

• use PROC SQL options to create and debug queries

• improve query performance

• access DICTIONARY tables and how they are useful in gathering information about

the elements of SAS

• use PROC SQL with the SAS macro facility

• use PROC SQL with the REPORT procedure

• access DBMSs by using SAS/ACCESS software

• format PROC SQL output by using the SAS Output Delivery System (ODS)

Using PROC SQL Options to Create and Debug

Queries

Overview of Using PROC SQL Options to Create and Debug Queries

PROC SQL supports options that can give you greater control over PROC SQL while

you are developing a query:

• The INOBS=, OUTOBS=, and LOOPS= options reduce query execution time by

limiting the number of rows and the number of iterations that PROC SQL processes.

• The EXEC and VALIDATE statements enable you to quickly check the syntax of a

query.

• The FEEDBACK option expands a SELECT * statement into a list of columns that

the statement represents.

• The PROC SQL STIMER option records and displays query execution time.

You can set an option initially in the PROC SQL statement, and then use the RESET

statement to change the same option's setting without ending the current PROC SQL

step.

138 Chapter 5 • Programming with the SQL Procedure