SAS System Options

SQLREDUCEPUTOBS= System Option

For the SQL procedure when the SQLREDUCEPUT= system 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.

Valid in:	configuration file, SAS invocation, OPTIONS statement, SAS System Options window
Category:	Files: SAS Files
	System administration: SQL
	System administration: Performance
Interaction:	If the SQLREDUCEPUT= system option is set to NONE, conditions for both the SQLREDUCEPUTOBS= and SQLREDUCEPUTVALUES= system options must be met in order for the SQL procedure to consider optimizing the PUT function.
PROC OPTIONS GROUP=	SASFILES
	SQL
	PERFORMANCE

Syntax
Syntax Description
Details
See Also

Syntax

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

Syntax Description

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. number-of-observations 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 the SQL procedure to optimize the PUT function.
Range:	0 - 2⁶³-1, or approximately 9.2 quintillion

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 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.

Details

For databases that allow implicit pass-through when the row count for a table is not known, the SQL procedure allows the optimization in order for the query to be executed by the database. When the SQLREDUCEPUT= system option is set to NONE, the SQL procedure considers the value of both the SQLREDUCEPUTVALUES= and SQLREDUCEPUTOBS= system options and determines whether to optimize the PUT function.

For databases that do not allow implicit pass-through, the SQL procedure does not perform the optimization, and more of the query is performed by SAS.