SQLREDUCEPUT= System Option

For the SQL procedure, 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.

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
Note: This option can be restricted by a site administrator. For more information, see the section on restricted options in the SAS System Options: Reference.

Syntax

SQLREDUCEPUT= ALL | NONE | DBMS | BASE

Syntax Description

ALL

specifies that optimization is performed on all PUT functions regardless of any 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. This is the default.

Requirement The first argument to the PUT function must be a variable 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.

Details

If you specify the SQLREDUCEPUT= system option, SAS optimizes the PUT function as much as possible before the query is executed. If the query also contains a WHERE clause, the evaluation of the WHERE clause is simplified. The following SELECT statements are examples of queries that would be reduced if this option was set to any value other than none:
select x, y from &lib..b where (PUT(x, abc.) in ('yes', 'no'));
select x from &lib..a where (PUT(x, udfmt.) = trim(left('small')));
If both the SQLREDUCEPUT= system option and the SQLCONSTDATETIME system option are specified, PROC SQL replaces the DATE, TIME, DATETIME, and TODAY functions with their respective values to determine the PUT function value before the query executes.
The following two SELECT clauses show the original query and the optimized query. This is the original query.
select x from &lib..c where (put(bday, date9.) = put(today(), date9.));
Here, the SELECT clause is optimized.
select x from &lib..c where (x = '17Mar2011'D);
If a query does not contain the PUT function, it is not optimized.
Note: 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. However, changing the value of the REDUCEPUT= option does not change the value of the SQLREDUCEPUT= system option.

See Also

“Improving Query Performance” in the SAS SQL Procedure User’s Guide
Procedure Statement Options:
PROC SQL Statement REDUCEPUT= option in the SAS SQL Procedure User’s Guide
System Options:
SQLCONSTDATETIME and SQLREDUCEPUTOBS in the SAS SQL Procedure User’s Guide