topic check whether an index is used in a data step in SAS Programming

check whether an index is used in a data step

Eva — Mon, 19 Mar 2012 12:20:07 GMT

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

check whether an index is used in a data step

Keith — Mon, 19 Mar 2012 12:22:29 GMT

option msglevel=i;

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000279149.htm

check whether an index is used in a data step

art297 — Mon, 19 Mar 2012 12:26:13 GMT

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

check whether an index is used in a data step

Eva — Mon, 19 Mar 2012 12:28:46 GMT

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

check whether an index is used in a data step

Keith — Mon, 19 Mar 2012 13:16:02 GMT

Hi Eva,

That is my understanding.

Regards,

Keith

check whether an index is used in a data step

art297 — Mon, 19 Mar 2012 13:48:13 GMT

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?

check whether an index is used in a data step

art297 — Mon, 19 Mar 2012 13:54:54 GMT

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.

check whether an index is used in a data step

Eva — Thu, 29 Mar 2012 09:53:09 GMT

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

data work.myfile;

set report.xyfile;

where a = 100;

run;