BookmarkSubscribeRSS Feed
Peter_C
Rhodochrosite | Level 12
Ksharp
it is not because the index is composite.
A simple index is also rejected - [pre]517 data class;
518 set sashelp.class ;
519 do until( rap > 99999 ) ;
520 rap = 100000 * ranuni(1) ;
521 nam1= put( rap,z1.)!!name ;
522 output ;
523 end ;
524 run ;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 1260317 observations and 7 variables.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may
be shifted by the "BEST" format.
NOTE: DATA statement used (Total process time):
real time 0.65 seconds
cpu time 0.65 seconds


525 proc sort out= classI( index=(nam1 )) ;
526 by weight ;
527 run ;

NOTE: There were 1260317 observations read from the data set WORK.CLASS.
NOTE: SAS threaded sort was used.
NOTE: The data set WORK.CLASSI has 1260317 observations and 7 variables.
INFO: Multiple concurrent threads will be used to create the index.
NOTE: Simple index nam1 has been defined.
NOTE: PROCEDURE SORT used (Total process time):
real time 1.92 seconds
cpu time 2.48 seconds


528 proc sql _method stimer ;
NOTE: SQL Statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

529 create table Kpeter as select * from classI(idxwhere=yes) where nam1 in('1Alice'
529! '1Barbara' '1Judy') ;

NOTE: SQL execution methods chosen are:

sqxcrta
sqxsrc( WORK.CLASSI )
INFO: Data set option (IDXWHERE=YES)forced an index to be used rather than a sequential
pass for where-clause processing.
INFO: Index nam1 selected for WHERE clause optimization.
NOTE: Table WORK.KPETER created, with 5 rows and 7 columns.

NOTE: SQL Statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

530 create table Kpeter as select * from classI where nam1 in ('1Alice' '1Barbara'
530! '1Judy') ;

NOTE: SQL execution methods chosen are:

sqxcrta
sqxsrc( WORK.CLASSI )
INFO: Index nam1 not used. Sorting into index order may help.
NOTE: Table WORK.KPETER created, with 5 rows and 7 columns.

NOTE: SQL Statement used (Total process time):
real time 0.16 seconds
cpu time 0.14 seconds[/pre]
So, I think it must be something to do with file- or index-size for data not sorted in index order.

peterC
Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 4410 views
  • 0 likes
  • 4 in conversation