The SQL Procedure

PROC SQL Statement

PROC SQL <option(s)>;

Task		Option
Control output
	Specify the buffer page size for the output	BUFFERSIZE=
	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
	Specify whether PROC SQL replaces references to the DATE, TIME, DATETIME, and TODAY functions in a query with their equivalent constant values before the query executes	CONSTDATETIME\|NOCONSTDATETIME
	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
	Specify whether PROC SQL clears an error code for the SQLEXITCODE macro variable for each statement	EXITCODE
	Restrict the number of input rows	INOBS=
	Specify whether implicit pass-through is enabled or disabled	IPASSTHRU\|NOIPASSTHRU
	Restrict the number of loops	LOOPS=
	Restrict the number of output rows	OUTOBS=
	Specify whether PROC SQL prompts you when a limit is reached with the INOBS=, OUTOBS=, or LOOPS= options	PROMPT\|NOPROMPT
	Specify the engine type that a query uses for which optimization is performed by replacing a PUT function in a query with a logically equivalent expression	REDUCEPUT=
	When the REDUCEPUT= option is set to NONE, specifies the minimum number of observations that must be in a table in order for PROC SQL to consider optimizing the PUT function in a query	REDUCEPUTOBS=
	When the REDUCEPUT= option is set to NONE, specifies the maximum number of SAS format values that can exist in a PUT function expression in order for PROC SQL to consider optimizing the PUT function in a query	REDUCEPUTVALUES=
	Specify whether PROC SQL processes queries that use remerging of data	REMERGE\|NOREMERGE
	Specify whether PROC SQL writes timing information for each statement to the SAS log	STIMER\|NOSTIMER
	Override the SAS system option THREADS\|NOTHREADS	THREADS\|NOTHREADS
	Specify how PROC SQL handles updates when there is an interruption	UNDO_POLICY=

Options

BUFFERSIZE=n|nK|nM|nG

specifies the permanent buffer page size for the output in multiples of 1 (bytes), 1024 (kilobytes), 1,048,576 (megabytes), or 1,073,741,824 (gigabytes). For example, a value of 65536 specifies a page size of 65536 bytes and a value of 64k specifies a page size of 65536 bytes.

BUFFERSIZE can also be specified in a RESET statement for use in particular queries.

Default:

0, which causes SAS to use the minimum optimal page size for the operating environment.

CONSTDATETIME|NOCONSTDATETIME

specifies whether the SQL procedure replaces references to the DATE, TIME, DATETIME, and TODAY functions in a query with their equivalent constant values before the query executes. Computing these values once ensures consistency of results when the functions are used multiple times in a query or when the query executes the functions close to a date or time boundary.

When the NOCONSTDATETIME option is set, PROC SQL evaluates these functions in a query each time it processes an observation.

Default:	CONSTDATETIME
Interaction:	If both the CONSTDATETIME option and the REDUCEPUT= option are specified, PROC SQL replaces the DATE, TIME, DATETIME, and TODAY functions with their respective values in order to determine the PUT function value before the query executes.
Tip:	Alternatively, you can set the SQLCONSTDATETIME system option. The value that is specified in the SQLCONSTDATETIME system option is in effect for all SQL procedure statements, unless the PROC SQL CONSTDATETIME option is set. The value of the CONSTDATETIME option takes precedence over the SQLCONSTDATETIME system option. The RESET statement can also be used to set or reset the CONSTDATETIME option. However, changing the value of the CONSTDATETIME option does not change the value of the SQLCONSTDATETIME system option. For more information, see the SQLCONSTDATETIME system option in the SAS Language Reference: Dictionary.

DOUBLE|NODOUBLE

double-spaces the report.

Default:	NODOUBLE
Featured in:	Combining Two Tables

DQUOTE=ANSI|SAS

specifies whether PROC SQL treats values within double quotation marks (" ") as variables or strings. With DQUOTE=ANSI, PROC SQL treats a quoted value as a variable. This feature enables you to use the following as table names, column names, or aliases:

reserved words such as AS, JOIN, GROUP, and so on
DBMS names and other names that are not normally permissible in SAS.

The quoted value can contain any character.

With DQUOTE=SAS, values within double quotation marks 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

EXITCODE

specifies whether PROC SQL clears an error code for any SQL statement. Error codes are assigned to the SQLEXITCODE macro variable.

Default:	0
Tip:	The exit code can be reset to the default value between PROC SQL statements with the RESET Statement.
See Also:	Using the PROC SQL Automatic Macro Variables in SAS 9.2 SQL Procedure User's Guide.

FEEDBACK|NOFEEDBACK

specifies whether PROC SQL displays, in the SAS log, PROC SQL statements after view references are expanded or certain other transformations of the statement are made.

This option has the following effects:

Any asterisk (for example, SELECT * ) is expanded into the list of qualified columns that it represents.
Any PROC SQL view is expanded into the underlying query.
Macro variables are resolved.
Parentheses are shown around all expressions to further indicate their order of evaluation.
Comments are removed.

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. Specifying FLOW without arguments is equivalent to specifying 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.

IPASSTHRU|NOIPASSTHRU

specifies whether implicit pass through is enabled or disabled.

Implicit pass through is enabled when PROC SQL is invoked. You can disable it for a query or series of queries. The primary reasons you might want to disable implicit pass through are as follows:

DBMSs use SQL2 semantics for NULL values, which behave somewhat differently than SAS missing values.
PROC SQL might do a better job of query optimization.

Default:	IPASSTHRU
See Also:	The documentation on the Pass-Through Facility for your DBMS in SAS/ACCESS for Relational Databases: Reference.

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 the PROC SQL Automatic Macro Variables in the SAS 9.2 SQL Procedure User's Guide

NOCONSTDATETIME

See CONSTDATETIME|NOCONSTDATETIME.

NODOUBLE

See DOUBLE|NODOUBLE.

NOERRORSTOP

See ERRORSTOP|NOERRORSTOP.

NOEXEC

See EXEC|NOEXEC.

NOFEEDBACK

See FEEDBACK|NOFEEDBACK.

NOFLOW

See FLOW|NOFLOW.

NOIPASSTHRU

See IPASSTHRU|NOIPASSTHRU.

NONUMBER

See NUMBER|NONUMBER.

NOPRINT

See PRINT|NOPRINT.

NOPROMPT

See PROMPT|NOPROMPT.

NOREMERGE

See REMERGE|NOREMERGE.

NOSORTMSG

See SORTMSG|NOSORTMSG.

NOSTIMER

See STIMER|NOSTIMER.

NOTHREADS

See THREADS|NOTHREADS.

NUMBER|NONUMBER

specifies whether the SELECT statement includes a column called ROW, which is the row (or observation) number of the data as the rows 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, then 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
Interaction:	NOPRINT affects the value of the SQLOBS automatic macro variable. See Using the PROC SQL Automatic Macro Variables in the SAS 9.2 SQL Procedure User's Guide for details.
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=, then PROC SQL prompts you to stop or continue. The prompting repeats if the same limit is reached again.

Default:

NOPROMPT

REDUCEPUT=ALL|NONE|DBMS|BASE

specifies the engine type that a query uses for which optimization is performed by replacing a PUT function in a query with a logically equivalent expression. The engine type can be one of the following values.

ALL

specifies that optimization is performed on all PUT functions regardless of the engine that is used by the query to access the data.

NONE

specifies that no optimization is to be performed.

DBMS

specifies that optimization is performed on all PUT functions whose query is performed by a SAS/ACCESS engine.

Requirement:

The first argument to the PUT function must be a variable that is obtained by a table that is accessed using a SAS/ACCESS engine.

BASE

specifies that optimization is performed on all PUT functions whose query is performed by a SAS/ACCESS engine or a Base SAS engine.

Default:	DBMS
Interaction:	If both the REDUCEPUT= option and the CONSTDATETIME option are specified, PROC SQL replaces the DATE, TIME, DATETIME, and TODAY functions with their respective values in order to determine the PUT function value before the query executes.
Interaction:	If the query also contains a WHERE or HAVING clause, the evaluation of the WHERE or HAVING clause is simplified.
Tip:	Alternatively, you can set the SQLREDUCEPUT system option. The value that is specified in the SQLREDUCEPUT system option is in effect for all SQL procedure statements, unless the PROC SQL REDUCEPUT= option is set. The value of the REDUCEPUT= option takes precedence over the SQLREDUCEPUT system option. The RESET statement can also be used to set or reset the REDUCEPUT= option. However, changing the value of the REDUCEPUT= option does not change the value of the SQLREDUCEPUT system option. For more information, see the SQLREDUCEPUT system option in the SAS Language Reference: Dictionary.

REDUCEPUTOBS=n | nK |nM |nG | nT | hexX | MIN | MAX

when the REDUCEPUT= option is set to NONE, specifies the minimum number of observations that must be in a table in order for PROC SQL to consider optimizing the PUT function in a query. The number of observations can be one of the following values:

n | nK | nM | nG | nT

specifies the number of observations that must be in a table before the SQL procedure considers to optimize the PUT function. n is an integer that can be allocated in multiples of 1 (bytes); 1,024 (kilobytes); 1,048,576 (megabytes); 1,073,741,824 (gigabytes); or 1,099,511,627,776 (terabytes). For example, a value of 8 specifies eight buffers, and a value of 3k specifies 3,072 buffers.

Default:	0, which indicates that there is no minimum number of observations in a table required for PROC SQL to optimize the PUT function.
Range:	0 - 2⁶³-1

hexX

specifies the number of observations that must be in a table before the SQL procedure considers to optimize the PUT function as a hexadecimal value. You must specify the value beginning with a number (0-9), followed by an X. For example, the value 2dx specifies 45 buffers.

MIN

sets the number of observations that must be in a table before the SQL procedure considers to optimize the PUT function to 0. A value of 0 indicates that there is no minimum number of observations required. This value is the default.

MAX

sets the maximum number of observations that must be in a table before the SQL procedure considers to optimize the PUT function to 2⁶³-1, or approximately 9.2 quintillion.

Default:

Tip:

Alternatively, you can set the SQLREDUCEPUTOBS system option. The value that is specified in the SQLREDUCEPUTOBS system option is in effect for all SQL procedure statements, unless the PROC SQL REDUCEPUTOBS= option is set. The value of the REDUCEPUTOBS= option takes precedence over the SQLREDUCEPUTOBS system option. The RESET statement can also be used to set or reset the REDUCEPUTOBS= option. However, changing the value of the REDUCEPUTOBS= option does not change the value of the SQLREDUCEPUTOBS system option. For more information, see the SQLREDUCEPUTOBS system option in the SAS Language Reference: Dictionary.

REDUCEPUTVALUES=n | nK |nM |nG | nT | hexX | MIN | MAX

when the REDUCEPUT= option is set to NONE, specifies the maximum number of SAS format values that can exist in a PUT function expression in order for PROC SQL to consider optimizing the PUT function in a query.

n | nK | nM | nG | nT

specifies the number of SAS format values that can exist in a PUT function expression, where n is an integer that can be allocated in multiples of 1 (bytes); 1,024 (kilobytes); 1,048,576 (megabytes); 1,073,741,824 (gigabytes); or 1,099,511,627,776 (terabytes). For example, a value of 8 specifies eight buffers, and a value of 3k specifies 3,072 buffers.

Default:	0, which indicates that there is no minimum number of SAS format values that can exist in a PUT function expression.
Range:	0 - 5,000
Interaction:	If the number of format values in a PUT function expression is greater than this value, the SQL procedure does not optimize the PUT function.

hexX

specifies the number of SAS format values that can exist in a PUT function expression as a hexadecimal value. You must specify the value beginning with a number (0-9), followed by an X. For example, the value 2dx specifies 45 buffers.

MIN

sets the number of SAS format values that can exist in a PUT function expression to 0. A value of 0 indicates that there is no minimum number of SAS format values required. This value is the default.

MAX

sets the maximum number of SAS format values that can exist in a PUT function expression to 5,000.

Default:

Tip:

Alternatively, you can set the SQLREDUCEPUTVALUES system option. The value that is specified in the SQLREDUCEPUTVALUES system option is in effect for all SQL procedure statements, unless the PROC SQL REDUCEPUTVALUES= option is set. The value of the REDUCEPUTVALUES= option takes precedence over the SQLREDUCEPUTVALUES system option. The RESET statement can also be used to set or reset the REDUCEPUTVALUES= option. However, changing the value of the REDUCEPUTVALUES= option does not change the value of the SQLREDUCEPUTVALUES system option. For more information, see the SQLREDUCEPUTVALUES system option in the SAS Language Reference: Dictionary.

REMERGE|NOREMERGE

Specifies whether PROC SQL can process queries that use remerging of data. The remerge feature of PROC SQL makes two passes through a table, using data in the second pass that was created in the first pass, in order to complete a query. When the NOREMERGE system option is set, PROC SQL cannot process remerging of data. If remerging is attempted when the NOREMERGE option is set, an error is written to the SAS log.

Default:	REMERGE
Tip:	Alternatively, you can set the SQLREMERGE system option. The value that is specified in the SQLREMERGE system option is in effect for all SQL procedure statements, unless the PROC SQL REMERGE option is set. The value of the REMERGE option takes precedence over the SQLREMERGE system option. The RESET statement can also be used to set or reset the REMERGE option. However, changing the value of the REMERGE option does not change the value of the SQLREMERGE system option. For more information, see the SQLREMERGE system option in the SAS Language Reference: Dictionary.
See also:	Remerging Data

SORTMSG|NOSORTMSG

Certain operations, such as ORDER BY, can 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.

Default:	value of SAS system option THREADS\|NOTHREADS.
Restriction:	Your site administrator can create a restricted options table. A restricted options table specifies SAS system option values that are established at startup and cannot be overridden. If the THREADS \| NOTHREADS system option is listed in the restricted options table, any attempt to set it is ignored and a warning message is written to the SAS log.
Interaction:	When THREADS\|NOTHREADS has been specified in a PROC SQL statement or a RESET statement, there is no way to reset the option to its default (that is, the value of the SAS system option THREADS\|NOTHREADS) for that invocation of PROC SQL.

Default:	REQUIRED
Tip:	If you are updating a data set using the SPD Engine, you can significantly improve processing performance by setting UNDO_POLICY=NONE. However, ensure that NONE is an appropriate setting for your application.
Tip:	Alternatively, you can set the SQLUNDOPOLICY system option. The value that is specified in the SQLUNDOPOLICY= system option is in effect for all SQL procedure statements, unless the PROC SQL UNDO_POLICY= option is set. The value of the UNDO_POLICY= option takes precedence over the SQLUNDOPOLICY= system option. The RESET statement can also be used to set or reset the UNDO_POLICY= option. However, changing the value of the UNDO_POLICY= option does not change the value of the SQLUNDOPOLICY= system option. After the procedure completes, the undo policy reverts to the value of the SQLUNDOPOLICY= system option. For more information, see the SQLUNDOPOLICY system option in the SAS Language Reference: Dictionary.