SQLREDUCEPUTVALUES= System Option

For the SQL procedure, when the SQLREDUCEPUT= system option is set to DBMS, BASE, or ALL, specifies the maximum number of SAS format values that can exist in a PUT function expression for PROC SQL to optimize the PUT function in a query.

Valid in: configuration file, SAS invocation, OPTIONS statement, SAS System Options window
Categories: Files: SAS Files
System administration: SQL
System administration: Performance
PROC OPTIONS GROUP= SASFILES
SQL
PERFORMANCE
Interaction: If the SQLREDUCEPUT= system option is set to DBMS, BASE, or ALL, conditions for both the SQLREDUCEPUTVALUES= and SQLREDUCEPUTOBS= system options must be met for PROC SQL to optimize the PUT function.
Note: This option can be restricted by a site administrator. For more information, see “Restricted Options” in Chapter 1 of SAS System Options: Reference.

Syntax

SQLREDUCEPUTVALUES= n

Syntax Description

n

specifies the maximum number of SAS format values that can exist in a PUT function expression for PROC SQL to optimize the PUT function in a query.

Default 100
Range 100 – 3,000
Requirement n must be an integer
Interaction If the number of SAS format values in a PUT function expression is greater than this value, PROC SQL does not optimize the PUT function.

Details

Some formats, especially user-defined formats, can contain many format values. Depending on the number of matches for a PUT function expression, the resulting expression can list many format values. If the number of format values becomes too large, query performance can degrade. When the SQLREDUCEPUT= system option is set to DBMS, BASE, or ALL, PROC SQL considers the values of both the SQLREDUCEPUTVALUES= and SQLREDUCEPUTOBS= system options, and determines whether to optimize the PUT function.
Tip
The value for SQLREDUCEPUTVALUES= is used for each individual optimization. For example, if you have a PUT function in a WHERE clause, and another PUT function in a GROUP BY clause, the value of SQLREDUCEPUTVALUES= is applied separately for each clause.