BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
krzy32
Fluorite | Level 6

SAS 9.4

I am learning Advanced SAS currently. The idxwhere=no options can be used to specify not to use an index. In what possible scenario would you tell SAS not to use indexes ? SAS predicts whether to use an index or not. If the program fulfils criteria for not using indexes, it's obvious even SAS knows those scenarios. Why would you decrease efficiency by telling SAS not to use an index?

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

I don't know if this is a good example but I shows one scenario where turning off the index changes what you get back.  Notice that using the index the values are returned in INDEX order while not using it they are returned in OBS order.  

 

41         options msglevel=i;
42         data byage;
43            set class;
44            where age in(12 15);
INFO: Index Age selected for WHERE clause optimization.
45            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
      WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.02 seconds
      

46         proc print;
47            run;

NOTE: There were 9 observations read from the data set WORK.BYAGE.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      

48         data byage;
49            set class(idxwhere=no);
50            where age in(12 15);
INFO: Data set option (IDXWHERE=NO) forced a sequential pass of the data rather than use of an index for where-clause processing.
51            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
      WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

Capture.PNG

View solution in original post

8 REPLIES 8
krzy32
Fluorite | Level 6

Yes but I cannot imagine any scenario in which I would need to explicitly tell SAS not to use indexes even though I risk decreasing efficiency but get same results as when index was used. Can you help me or have you come across any such scenario?

data_null__
Jade | Level 19

I don't know if this is a good example but I shows one scenario where turning off the index changes what you get back.  Notice that using the index the values are returned in INDEX order while not using it they are returned in OBS order.  

 

41         options msglevel=i;
42         data byage;
43            set class;
44            where age in(12 15);
INFO: Index Age selected for WHERE clause optimization.
45            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
      WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.02 seconds
      

46         proc print;
47            run;

NOTE: There were 9 observations read from the data set WORK.BYAGE.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      

48         data byage;
49            set class(idxwhere=no);
50            where age in(12 15);
INFO: Data set option (IDXWHERE=NO) forced a sequential pass of the data rather than use of an index for where-clause processing.
51            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
      WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

Capture.PNG

krzy32
Fluorite | Level 6

Thank you for your answer. Can you please post the log with the fullstimer option?

Ksharp
Super User

When you get most part of a table (like 70% 80%),not using index is faster than using index.

krzy32
Fluorite | Level 6

Agreed but SAS has an inbuilt algorithm that estimates output dataset. In the scenario you specified, had we not mentioned any index option, SAS would not have used indexing as about 70%-80% of dataset is being used.

LinusH
Tourmaline | Level 20
Yes there is an algorithm. But that can't foresee everything. So I guess that there is no straight answer. I'm thinking that in a real life example find the use of index inefficient and thenot turn that off explicitly for that query.
One situation I can imagine is the the index is heavily fragmented so that even if just 10% rows is returned, let's say that 50% of the data pages is read.
Data never sleeps
krzy32
Fluorite | Level 6

Yes, even i'm trying to imagine a scenario. Maybe what you say could happen.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 2320 views
  • 3 likes
  • 5 in conversation