OPTIONS ON THE PROC SQL STATEMENT

Restricting Row Processing with the INOBS= and OUTOBS= Options

There are several useful options that can be used in the PROC SQL statement to help control the appearance of the report. Note that once coded, these options will apply to all SELECT statements within the PROC SQL step unless a RESET statement is used:

PROC SQL INOBS=5 OUTOBS=4 DOUBLE;
SELECT STORE_NAME, (GROSS_SALES * .05) AS TAX
FROM Online_Sales;
QUIT;

INOBS=n
Restricts the number of rows (observations) that PROC SQL retrieves from any single source. This is very useful for debugging queries on large table.
For example, if you specify INOBS=10, then PROC SQL uses only 10 rows from any table or view that is specified in a FROM clause. If you specify INOBS=10 and join two tables without using a WHERE clause, then the resulting table (Cartesian product) contains a maximum of 100 rows. The INOBS= option is similar to the SAS system option OBS=.

OUTOBS=n
Restricts the number of rows (observations) in the output. For example, if you specify OUTOBS=10 and insert values into a table by using a query, then PROC SQL inserts a maximum of 10 rows into the resulting table. Please refer output #1.

For example, we have one dataset called ONLINE_SALES:

 output #1

THE CALCULATED OPTION

The CALCULATED component refers to a previously calculated variable so recalculation is not necessary. The CALCULATED component must refer to a variable created within the same SELECT statement. Please refer output #2.

PROC SQL ;
SELECT STORE_NAME, (GROSS_SALES * .05) AS TAX,
CALCULATED TAX * .01 AS REBATE
FROM Online_Sales;
QUIT;

Output #2