PROC SQL Options
A Brief Overview
The SQL procedure offers
a variety of options that control processing. Some options control
execution. For example, you can limit the number of rows that are
read or written during a query. Other options control output. For
example, you can control the number of rows to be displayed in your
output.
PROC SQL Statement Syntax
|
Syntax, PROC SQL statement:
PROC SQL <option(s)>;
option(s)
names the option(s)
to be used.
|
After you specify an option, it remains
in effect until you change it or you reset it.
The following tables
list the options that are covered in this section. A description
and an example of each option appear in the following sections.
|
Desired Result
|
Option
|
|---|---|
|
Restrict the number
of input rows.
|
INOBS=
|
|
Restrict the number
of output rows.
|
OUTOBS=
|
|
Specify whether PROC
SQL prints the query’s result.
|
PRINT | NOPRINT
|
|
Include a column of
row numbers.
|
NUMBER | NONUMBER
|
|
Desired Result
|
Option
|
|---|---|
|
Specify the names of
columns to be kept.
|
KEEP=
|
|
Specify the names of
columns to be dropped.
|
DROP=
|
|
Specify the last observations
that SAS processes in a data set.
|
OBS=
|
|
Change the name of a
variable.
|
RENAME=
|
Using Invocation Options
Restricting Row Processing
When you are developing queries against large tables,
you can shorten the time that it takes for the queries to run by reducing
the number of rows that PROC SQL processes. Subsetting the tables
with WHERE clauses is one way to do this. Using the INOBS= and OUTOBS=
options in PROC SQL is another way.
You already know that
you can use the OUTOBS= option to restrict the number of rows that
PROC SQL displays or writes to a table. However, the OUTOBS= option
does not restrict the rows that are read. The INOBS= option restricts
the number of rows that PROC SQL takes as input from any single source.
The INOBS= option is similar to the SAS data set option OBS= and is
useful for debugging queries on large tables.
In the following PROC
SQL set operation, INOBS=5 is specified. As indicated in the log,
only five rows from the source table Certadv.Mechanicslevel1 are read.
The resulting table contains five rows.
proc sql inobs=5;
select *
from certadv.mechanicslevel1;
quit;
Log 1.4 SAS Log
WARNING: Only 5 records were read from CERTADV.MECHANICSLEVEL1 due to INOBS= option.
Output 1.24 PROC SQL Query Result: INOBS= Option
Example: Limiting the Number of Rows Displayed
Suppose you want to
quickly review the types of values that are stored in a table, without
printing out all the rows. The following PROC SQL query selects data
from the table Certadv.Payrollmaster, which contains more than 100
rows. The query prints only the first ten rows that are ordered by
Salary in descending order.
proc sql outobs=10;
select *
from certadv.payrollmaster
order by Salary desc;
quit;
When you limit the number
of rows, a warning is written to the SAS log.
Log 1.5 SAS Log
WARNING: Statement terminated early due to OUTOBS=10 option.
Output 1.25 PROC SQL Query Result Using the OUTOBS Option
Tip
You can also use the INOBS=
option to restrict the number of rows that PROC SQL takes as input
from any single source.Example: Including a Column of Row Numbers
The NUMBER | NONUMBER option specifies whether the output
from a query should include a column named ROW, which displays row
numbers. NONUMBER is the default. The option is similar to the NOOBS
option in the PRINT procedure.
The following PROC SQL
step specifies the NUMBER option. Output from the step includes a
column named Row, which contains row numbers.
proc sql inobs=10 number;
select flightnumber, destination
from certadv.internationalflights;
quit;
Output 1.26 PROC SQL Query Result: NUMBER Option
Last updated: October 16, 2019
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.