Chapter Contents
Previous
Next
The SQL Procedure

PROC SQL Statement

PROC SQL <option(s)>;

To do this Use this option
Control output

Double-space the report DOUBLE|NODOUBLE

Write a statement to the SAS log that expands the query FEEDBACK|NOFEEDBACK

Flow characters within a column FLOW|NOFLOW

Include a column of row numbers NUMBER|NONUMBER

Specify whether PROC SQL prints the query's result PRINT|NOPRINT

Specify whether PROC SQL should display sorting information SORTMSG|NOSORTMSG

Specify a collating sequence SORTSEQ=
Control execution

Allow PROC SQL to use names other than SAS names DQUOTE=

Specify whether PROC SQL should stop executing after an error ERRORSTOP|NOERRORSTOP

Specify whether PROC SQL should execute statements EXEC|NOEXEC

Restrict the number of input rows INOBS=

Restrict the number of output rows OUTOBS=

Restrict the number of loops LOOPS=

Specify whether PROC SQL prompts you when a limit is reached with the INOBS=, OUTOBS=, or LOOPS= options PROMPT|NOPROMPT

Specify whether PROC SQL writes timing information to the SAS log STIMER|NOSTIMER

Specify how PROC SQL handles updates when there is an interruption UNDO_POLICY=

Options

DOUBLE|NODOUBLE
double-spaces the report.
Default: NODOUBLE
Featured in: Combining Two Tables

DQUOTE=ANSI|SAS
specifies whether PROC SQL treats values within double-quotes as variables or strings. With DQUOTE=ANSI, PROC SQL treats a quoted value as a variable. This enables you to use the following as table names, column names, or aliases:

The quoted value can contain any character.

With DQUOTE=SAS, values within quotes are treated as strings.
Default: SAS

ERRORSTOP|NOERRORSTOP
specifies whether PROC SQL stops executing if it encounters an error. In a batch or noninteractive session, ERRORSTOP instructs PROC SQL to stop executing the statements but to continue checking the syntax after it has encountered an error.

NOERRORSTOP instructs PROC SQL to execute the statements and to continue checking the syntax after an error occurs.
Default: NOERRORSTOP in an interactive SAS session; ERRORSTOP in a batch or noninteractive session
Interaction: This option is useful only when the EXEC option is in effect.
Tip: ERRORSTOP has an effect only when SAS is running in the batch or noninteractive execution mode.
Tip: NOERRORSTOP is useful if you want a batch job to continue executing SQL procedure statements after an error is encountered.

EXEC | NOEXEC
specifies whether a statement should be executed after its syntax is checked for accuracy.
Default: EXEC
Tip: NOEXEC is useful if you want to check the syntax of your SQL statements without executing the statements.
See also: ERRORSTOP option

FEEDBACK|NOFEEDBACK
specifies whether PROC SQL displays a statement after it expands view references or makes certain transformations on the statement.

This option expands any use of an asterisk (for example, SELECT *) into the list of qualified columns that it represents. Any PROC SQL view is expanded into the underlying query, and parentheses are shown around all expressions to further indicate their order of evaluation.
Default: NOFEEDBACK

FLOW<=n <m>>|NOFLOW
specifies that character columns longer than n are flowed to multiple lines. PROC SQL sets the column width at n and specifies that character columns longer than n are flowed to multiple lines. When you specify FLOW=n m, PROC SQL floats the width of the columns between these limits to achieve a balanced layout. FLOW is equivalent to FLOW=12 200.
Default: NOFLOW

INOBS=n
restricts the number of rows (observations) that PROC SQL retrieves from any single source.
Tip: This option is useful for debugging queries on large tables.

LOOPS=n
restricts PROC SQL to n iterations through its inner loop. You use the number of iterations reported in the SQLOOPS macro variable (after each SQL statement is executed) to discover the number of loops. Set a limit to prevent queries from consuming excessive computer resources. For example, joining three large tables without meeting the join-matching conditions could create a huge internal table that would be inefficient to execute.
See also: Using Macro Variables Set by PROC SQL

NODOUBLE
See DOUBLE|NODOUBLE .

NOERRORSTOP
See ERRORSTOP|NOERRORSTOP .

NOEXEC
See EXEC|NOEXEC .

NOFEEDBACK
See FEEDBACK|NOFEEDBACK .

NOFLOW
See FLOW|NOFLOW .

NONUMBER
See NUMBER|NONUMBER .

NOPRINT
See PRINT|NOPRINT .

NOPROMPT
See PROMPT|NOPROMPT .

NOSORTMSG
See SORTMSG|NOSORTMSG .

NOSTIMER
See STIMER|NOSTIMER .

NUMBER|NONUMBER
specifies whether the SELECT statement includes a column called ROW, which is the row (or observation) number of the data as they are retrieved.
Default: NONUMBER
Featured in: Joining Two Tables

OUTOBS=n
restricts the number of rows (observations) in the output. For example, if you specify OUTOBS=10 and insert values into a table using a query-expression, the SQL procedure inserts a maximum of 10 rows. Likewise, OUTOBS=10 limits the output to 10 rows.

PRINT|NOPRINT
specifies whether the output from a SELECT statement is printed.
Default: PRINT
Tip: NOPRINT is useful when you are selecting values from a table into macro variables and do not want anything to be displayed.

PROMPT|NOPROMPT
modifies the effect of the INOBS=, OUTOBS=, and LOOPS= options. If you specify the PROMPT option and reach the limit specified by INOBS=, OUTOBS=, or LOOPS=, PROC SQL prompts you to stop or continue. The prompting repeats if the same limit is reached again.
Default: NOPROMPT

SORTMSG|NOSORTMSG
Certain operations, such as ORDER BY, may sort tables internally using PROC SORT. Specifying SORTMSG requests information from PROC SORT about the sort and displays the information in the log.
Default: NOSORTMSG

SORTSEQ=sort-table
specifies the collating sequence to use when a query contains an ORDER BY clause. Use this option only if you want a collating sequence other than your system's or installation's default collating sequence.
See also: SORTSEQ= option in SAS Language Reference: Dictionary.

STIMER|NOSTIMER
specifies whether PROC SQL writes timing information to the SAS log for each statement, rather than as a cumulative value for the entire procedure. For this option to work, you must also specify the SAS system option STIMER. Some operating environments require that you specify this system option when you invoke SAS. If you use the system option alone, you receive timing information for the entire SQL procedure, not on a statement-by-statement basis.
Default: NOSTIMER

UNDO_POLICY=NONE|OPTIONAL|REQUIRED
specifies how PROC SQL handles updated data if errors occur while you are updating data. You can use UNDO_POLICY= to control whether your changes will be permanent:

NONE
keeps any updates or inserts.

OPTIONAL
reverses any updates or inserts that it can reverse reliably.

REQUIRED
undoes all inserts or updates that have been done to the point of the error. In some cases, the UNDO operation cannot be done reliably. For example, when a program uses a SAS/ACCESS view, it may not be able to reverse the effects of the INSERT and UPDATE statements without reversing the effects of other changes at the same time. In that case, PROC SQL issues an error message and does not execute the statement. Also, when a SAS data set is accessed through a SAS/SHARE server and is opened with the data set option CNTLLEV=RECORD, you cannot reliably reverse your changes.

This option may enable other users to update newly inserted rows. If an error occurs during the insert, PROC SQL can delete a record that another user updated. In that case, the statement is not executed, and an error message is issued.

Default: REQUIRED

Note:   Options can be added, removed, or changed between PROC SQL statements with the RESET statement.  [cautionend]


Chapter Contents
Previous
Next
Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.