Hey.Peter.
I found it very interesting.Obviously value of your nam1 prevent to use index.
When I open your class table,I found a * before name,that is not legal.So I change your code a little.So the index is worked ,whether it is simple or composite index, and Linus might be correct.
So this remind us to use index after checking data which used to make index.
[pre]
data class;
set sashelp.class ;
rapp=0;
do until( rapp > 9999 ) ;
rapp+1;
rap = floor(10* ranuni(1) );
namee= cats(put(rap,$1.),name) ;
output ;
end ;
run ;
proc datasets library=work;
modify class;
index create namee ;
quit;
options msglevel=i;
proc sql _method stimer ;
create table Kpeter as select * from class(idxwhere=yes) where namee in ('1Alice' '1Barbara' '1Judy') ;
create table Kpeter as select * from class where namee in ('1Alice' '1Barbara' '1Judy') ;
quit ;
403 modify class;
404 index create namee ;
NOTE: Simple index namee has been defined.
405 quit;
NOTE: MODIFY was successful for WORK.CLASS.DATA.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.69 seconds
cpu time 0.63 seconds
406
407 options msglevel=i;
408 proc sql _method stimer ;
NOTE: SQL Statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
409 create table Kpeter as select * from class(idxwhere=yes) where namee in ('1Alice' '1Barbara'
409! '1Judy') ;
NOTE: SQL execution methods chosen are:
sqxcrta
sqxsrc( WORK.CLASS )
INFO: Data set option (IDXWHERE=YES)forced an index to be used rather than a sequential pass for
where-clause processing.
INFO: Index namee selected for WHERE clause optimization.
NOTE: Table WORK.KPETER created, with 2969 rows and 8 columns.
NOTE: SQL Statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
410 create table Kpeter as select * from class where namee in ('1Alice' '1Barbara' '1Judy') ;
NOTE: SQL execution methods chosen are:
sqxcrta
sqxsrc( WORK.CLASS )
INFO: Index namee selected for WHERE clause optimization.
NOTE: Table WORK.KPETER created, with 2969 rows and 8 columns.
NOTE: SQL Statement used (Total process time):
real time 0.01 seconds
cpu time 0.02 seconds
411 quit ;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
[/pre]
Ksharp