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