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

Dear all,

how do I know whether an index is really used by a data step? I user SAS 9.2 and the data file is a sas file with a simple index on one field. The data step has a where statement.

Best regards,

Eva

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

No message isn't what is implied by the documentation:

Displaying Index Usage Information in the SAS Log

To display information in the SAS log regarding index usage, change the value of the MSGLEVEL= system option from its default value of N to I. When you issue options msglevel=i; , the following occurs:

  • If an index is used, a message displays specifying the name of the index.
  • If an index is not used but one exists that could optimize at least one condition in the WHERE expression, messages provide suggestions as to what you can do to influence SAS to use the index; for example, a message could suggest sorting the data file into index order or specifying more buffers.
  • A message displays the IDXWHERE= or IDXNAME= data set option value if the setting can affect index processing.

If you are just trying to test whether using an index will be beneficial, you can always force it by using the idxwhere and/or idsname options.

View solution in original post

7 REPLIES 7
Eva
Quartz | Level 8 Eva
Quartz | Level 8

Dear Keith,

I've tried it and see nothing in the sas log which haven't seen before. Does that mean that the index is not used?

Eva

Keith
Obsidian | Level 7

Hi Eva,

That is my understanding.


Regards,

Keith

art297
Opal | Level 21

Out of curiosity (and I don't really know if it makes a difference) are you using a where option in a set statement, or actually including a where statement?

Eva
Quartz | Level 8 Eva
Quartz | Level 8

I have a where statement in the data step like in this example:

data work.myfile;

     set report.xyfile;

     where a = 100;

run;

art297
Opal | Level 21

No message isn't what is implied by the documentation:

Displaying Index Usage Information in the SAS Log

To display information in the SAS log regarding index usage, change the value of the MSGLEVEL= system option from its default value of N to I. When you issue options msglevel=i; , the following occurs:

  • If an index is used, a message displays specifying the name of the index.
  • If an index is not used but one exists that could optimize at least one condition in the WHERE expression, messages provide suggestions as to what you can do to influence SAS to use the index; for example, a message could suggest sorting the data file into index order or specifying more buffers.
  • A message displays the IDXWHERE= or IDXNAME= data set option value if the setting can affect index processing.

If you are just trying to test whether using an index will be beneficial, you can always force it by using the idxwhere and/or idsname options.

art297
Opal | Level 21

Use the MSGLEVEL=I sas system option.  For further reading, take a look at: http://www2.sas.com/proceedings/sugi29/123-29.pdf

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
  • 7 replies
  • 3659 views
  • 1 like
  • 3 in conversation