SAS System Options

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
Category:	Files: SAS Files
	System administration: SQL
	System administration: Performance
PROC OPTIONS GROUP=	SASFILES
	SQL
	PERFORMANCE

Syntax
Syntax Description
Details
See Also

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

By using the SQLREDUCEPUT= system option, you can specify that SAS reduces the PUT function as much as possible before the query is processed. 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, the SQL procedure 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 and optimized queries:

select x from &lib..c where (put(bday, date9.) = put(today(), date9.));

would be reduced to

select x from &lib..c where (put(bday, date9.) = "01Jun2008");

If a query does not contain the PUT function, optimization is not performed.

Note: The value that is specified in the SQLREDUCEPUT system option is in effect for all SQL procedure statements, unless the REDUCEPUT option in the PROC SQL statement 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. [cautionend]