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.

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

Product Family	Product	System	SAS Release
			Reported	Fixed*
SAS System	Base SAS	z/OS	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft® Windows® for 64-Bit Itanium-based Systems	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows XP 64-bit Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft® Windows® for x64	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows NT Workstation	9.1 TS1M3 SP4	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows XP Professional	9.1 TS1M3 SP4	9.2 TS1M0
		Windows Vista	9.1 TS1M3 SP4	9.2 TS1M0
		64-bit Enabled AIX	9.1 TS1M3 SP4	9.2 TS1M0
		64-bit Enabled HP-UX	9.1 TS1M3 SP4	9.2 TS1M0
		64-bit Enabled Solaris	9.1 TS1M3 SP4	9.2 TS1M0
		HP-UX IPF	9.1 TS1M3 SP4	9.2 TS1M0
		Linux	9.1 TS1M3 SP4	9.2 TS1M0
		Linux on Itanium	9.1 TS1M3 SP4	9.2 TS1M0
		OpenVMS Alpha	9.1 TS1M3 SP4	9.2 TS1M0
		Tru64 UNIX	9.1 TS1M3 SP4	9.2 TS1M0

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

The following Macro can be used to assign the values for the various SQLREDUCE macro variables or SAS System options based on the version of SAS being used. If SAS 9.1.3 is being used, the values are assigned to macro variables. If SAS 9.2 is being used, the values are assigned via an OPTIONS statement.

---

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

http://www.sas.com/techsup/download/hotfix/e9_sbcs_prod_list.html#030390

---

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

http://www.sas.com/techsup/download/hotfix/e9_dbcs_prod_list.html#030390

---

New methods have ben created to help improve relational database optimization, independent of the database being used.

Type:	Problem Note
Priority:

Date Modified:	2011-01-26 16:24:32
Date Created:	2007-10-19 17:06:19