DATA Step, Macro, Functions and more

check whether an index is used in a data step

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 156
Accepted Solution

check whether an index is used in a data step

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


Accepted Solutions
Solution
‎03-19-2012 09:54 AM
PROC Star
Posts: 7,474

check whether an index is used in a data step

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


All Replies
Regular Contributor
Posts: 151

check whether an index is used in a data step

Regular Contributor
Regular Contributor
Posts: 156

check whether an index is used in a data step

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

Regular Contributor
Posts: 151

check whether an index is used in a data step

Hi Eva,

That is my understanding.


Regards,

Keith

PROC Star
Posts: 7,474

check whether an index is used in a data step

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?

Regular Contributor
Regular Contributor
Posts: 156

check whether an index is used in a data step

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

data work.myfile;

     set report.xyfile;

     where a = 100;

run;

Solution
‎03-19-2012 09:54 AM
PROC Star
Posts: 7,474

check whether an index is used in a data step

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.

PROC Star
Posts: 7,474

check whether an index is used in a data step

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1207 views
  • 1 like
  • 3 in conversation