• Print |
• E-mail  |
• Bookmark

New methods are available for improving relational database optimization. These methods were implemented in SAS^® 9.1.3 Service Pack 4, and they are controlled by using automatic macro variables upon the installation of hot fixes E9BB70 and E9BB72.

You can use the following macro variables that can be used with the hot fix.

%let SYS_SQLREDUCEPUT=DBMS;      [DBMS, BASE, ALL, NONE]
%let SYS_SQLREDUCEPUTOBS=0;      [minobs...maxobs]
%let SYS_SQLREDUCEPUTVALUES=100; [0...3000]

In a SAS BI envirionment, these macro variables can be set in the appstart_autoexec.sas file located in the ..\Lev1\SASMain directory on the SAS Application Server machine.

In SAS^® 9.2, you control these new database optimization methods with new SAS^® system options or PROC SQL options.

The system options are as follows:

SQLREDUCEPUT

Specifies whether PROC SQL is to optimize references to PUT() functions within submitted queries. When referencing tables in a database, the elimination of references to PUT() functions can enable more of the query to be passed down to the database.

SQLREDUCEPUTOBS

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. This system option is used for PROC SQL when the SQLREDUCEPUT= system option is NOT set to NONE.

SQLREDUCEPUTVALUES

Specifies the maximum number of SAS format values that can exist in a PUT() function expression in order for PROC SQL to consider optimizing the PUT() function in a query. This system option is used for PROC SQL when the SQLREDUCEPUT= system option is NOT set to NONE

The equivalent PROC SQL options are as follows:

• REDUCEPUT=
• REDUCEPUTOBS=
• REDUCEPUTVALUES=

Select the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

%macro sqlreduce(red_put, red_obs, red_vals) ; 
                                               
%if &sysver=9.1 %then %do;                     
  %global SYS_SQLREDUCEPUT                     
          SYS_SQLREDUCEPUTOBS                  
          SYS_SQLREDUCEPUTVALUES;              
  %let SYS_SQLREDUCEPUT = &red_put;            
  %let SYS_SQLREDUCEPUTOBS = &red_obs;         
  %let SYS_SQLREDUCEPUTVALUES = &red_vals ;    
  %end;                                        
%else %if &sysver=9.2 %then %do;               
  options SQLREDUCEPUT = &red_put              
          SQLREDUCEPUTOBS = &red_obs           
          SQLREDUCEPUTVALUES = &red_vals;      
  %end;                                        
%mend sqlreduce;                               
                                               
%sqlreduce(all, 0, 0 );                        

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at: