Understanding SAS Indexes for the BASE (V9) Engine
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. 
