64819 - A DATA step that executes a PROC SQL view with a WHERE clause does not select the index

Problem Note 64819: A DATA step that executes a PROC SQL view with a WHERE clause does not select the index

An index is not selected to optimize a WHERE clause under the following circumstances:

A SORT procedure creates an output data set and an index is created.
An SQL procedure view is created from this data set.
The view is specified in a SET statement in a DATA step.
A WHERE clause that contains the indexed variable is specified.

The index is not selected to optimize the WHERE clause, which can lead to a performance issue. The Full Code tab provides an example of code that can create such an issue.

A workaround is to use a data set in the SET statement instead of a PROC SQL view. If a DATA step view is used instead, the WHERE clause must be part of the view definition.

Click 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
Product Family	Product	System	Reported	Fixed*
SAS System	Base SAS	z/OS	9.4 TS1M4
		z/OS 64-bit	9.4 TS1M4
		Microsoft® Windows® for x64	9.4 TS1M4
		Microsoft Windows 8 Enterprise 32-bit	9.4 TS1M4
		Microsoft Windows 8 Enterprise x64	9.4 TS1M4
		Microsoft Windows 8 Pro 32-bit	9.4 TS1M4
		Microsoft Windows 8 Pro x64	9.4 TS1M4
		Microsoft Windows 8.1 Enterprise 32-bit	9.4 TS1M4
		Microsoft Windows 8.1 Enterprise x64	9.4 TS1M4
		Microsoft Windows 8.1 Pro 32-bit	9.4 TS1M4
		Microsoft Windows 8.1 Pro x64	9.4 TS1M4
		Microsoft Windows 10	9.4 TS1M4
		Microsoft Windows Server 2008	9.4 TS1M4
		Microsoft Windows Server 2008 R2	9.4 TS1M4
		Microsoft Windows Server 2008 for x64	9.4 TS1M4
		Microsoft Windows Server 2012 Datacenter	9.4 TS1M4
		Microsoft Windows Server 2012 R2 Datacenter	9.4 TS1M4
		Microsoft Windows Server 2012 R2 Std	9.4 TS1M4
		Microsoft Windows Server 2012 Std	9.4 TS1M4
		Windows 7 Enterprise 32 bit	9.4 TS1M4
		Windows 7 Enterprise x64	9.4 TS1M4
		Windows 7 Home Premium 32 bit	9.4 TS1M4
		Windows 7 Home Premium x64	9.4 TS1M4
		Windows 7 Professional 32 bit	9.4 TS1M4
		Windows 7 Professional x64	9.4 TS1M4
		Windows 7 Ultimate 32 bit	9.4 TS1M4
		Windows 7 Ultimate x64	9.4 TS1M4
		64-bit Enabled AIX	9.4 TS1M4
		64-bit Enabled Solaris	9.4 TS1M4
		HP-UX IPF	9.4 TS1M4
		Linux for x64	9.4 TS1M4
		Solaris for x64	9.4 TS1M4

* 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 data set is sorted and an index is built on the output data set. A PROC SQL view is created and called in a DATA step in the SET statement. The index is not called to optimize the WHERE clause. If the index had been used, there would have been a message in the log as a result of specifying the MSGLEVEL system option set to i (info).

options msglevel=i;

data test;
   do x=-5 to 5 by .25;
      do y=-5 to 5 by .25;
         z=tanh(sqrt(x*x+y*y));
         z2=sin(sqrt(x*x+y*y));
         randomnumber=int((ranuni(12345)*10000));
         randomnumber_char = compress(input(randomnumber,$15.));
         output;
      end;
   end;
run;

proc sort data=test out=testindex (index=(randomnumber)) force;
by randomnumber;
run;

proc sql;
   create view sqlviewtest
   as select *
   from testindex;
quit;



/*TESTING INDEX USAGE WITH THE SQL VIEW CREATED USING THE INDEX ADDED IN PROC SORT*/
data singleview3;
   set sqlviewtest (where=(randomnumber=6688));
run;

Type:	Problem Note
Priority:	high

Date Modified:	2019-10-03 11:38:25
Date Created:	2019-09-24 16:05:51

Support

Problem Note 64819: A DATA step that executes a PROC SQL view with a WHERE clause does not select the index

Operating System and Release Information