Understanding SAS Indexes for the BASE (V9) Engine

[#1]

An index is an optional file that you can create for a SAS data set using the BASE (V9) engine in order to provide direct access to specific observations. The index stores values in ascending value order for a specific variable or variables and includes information as to the location of those values within observations in the data file. In other words, an index enables you to locate an observation by value.

For a complete description of SAS indexes - including benefits, types, guidance on deciding whether to create an index, and information about how to create and use indexes, see this article in the SAS 9.4 Language Reference: Concepts, Fifth Edition:

https://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#n06cy7dznbx6gen1q9m...

[Ron_MacroMaven]

see also books by our favorite Marathon Runner and MainFrame Optimization Expert

SAS Press - Michael A. Raithel Author Page

[ChrisNZ]

Interesting. Thank you. A few questions:

1-This seems wrong:

If you issue the following PRINT procedure, which refers to the SQL view, along with a WHERE statement that specifies the key variable State, SAS cannot optimize the WHERE statement with the index. SQL views cannot join a WHERE expression that was defined in the view to a WHERE expression that was specified in another procedure, DATA step, or SCL

data class(index=(age));   set sashelp.class;do i=1 to 1e3;   output ;end;

proc sql;

   create view c as    select * from class  where sex='F';

quit;

proc print data=c;

   where age <12;

run;

generates:

INFO: Index Age selected for WHERE clause optimization.

NOTE: There were 1000 observations read from the data set WORK.CLASS.

      WHERE (Sex='F') and (Age<12);

2- This is wrong/ badly worded:

pattern-matching operators LIKE and NOT LIKE  => Valid for Compound Optimization = no

data class(index=(s=(name age)));

   set sashelp.class;do i=1 to 1e3;   output ;end;

proc sql;

   create view c as    select * from class    where sex='F';

quit;

proc print data=c;

   where name like 'A%';

run;

generates:

INFO: Index s selected for WHERE clause optimization.

NOTE: There were 1000 observations read from the data set WORK.CLASS.

      WHERE (Sex='F') and Name like 'A%';

3- Same for

is null => Valid for Compound Optimization = no

proc print data=c;

   where name is null ;

run;

generates:

INFO: Index s selected for WHERE clause optimization.

NOTE: No observations were selected from data set WORK.C.

NOTE: There were 0 observations read from the data set WORK.CLASS.

      WHERE (Sex='F') and (Name is null);

4- Same for trim() not being optimised

proc print data=c;

   where trim(name) = 'Alice';

run;

generates:

INFO: Index s selected for WHERE clause optimization.

NOTE: There were 1000 observations read from the data set WORK.CLASS.

      WHERE (Sex='F') and (TRIM(Name)='Alice');

5- Also this article should make it clear it addresses V9 indexes.

SPDE indexes are a different beast altogether.

Still a good read.

[DWarner]

Chris,

I apologize for the delay in getting answers to your questions. Hopefully, I can clear up a few things here as well as do some rework in the "Understanding SAS Indexes" documentation. 

• Regarding the SQL view: Only a view created by SQL can be used to optimize an SQL WHERE clause. The example you provided does not seem to contradict this.    

• We will address the wording issues in the chart that lists the WHERE conditions that can be optimized. We need to clarify a misunderstanding of compound optimization, which involves using more than one of the columns indexed in a composite index. All of the examples use only the first column in a composite index, which is not compound optimization.   

• I will also change the heading of this article to include the BASE (V9) engine, as well as the documentation. 

Thank you for your input and questions.  

[ChrisNZ]

@DWarner

Isn't just the opposite said in text: "SQL views cannot join a WHERE expression that was defined in the view to a WHERE expression that was specified in another procedure"

[DWarner]

I had to run this question through development, and you are absolutely correct. The documentation is wrong. Thank you for pointing out the error, and I will have the documentation corrected