topic Re: Index Utilization in SAS Programming

Index Utilization

aha123 — Thu, 25 Nov 2010 22:21:29 GMT

I have a large dataset and set up indexes on a couple of columns. Then I wrote a PROC SQL to take advantage of the indexes to get the data from this dataset, but it still takes a long time to return the results. I am wondering if SAS actually uses the indexes to speed up the data retrieval. Does SAS offer any tool to analyze a query? Oracle offers the EXPLAIN command for users to analyze a query to see if it uses the index as intended. So I wonder SAS has a similar tool.

Re: Index Utilization

Peter_C — Thu, 25 Nov 2010 23:04:32 GMT

try the _method option like
proc sql _method ;
it will indicate which methods are used
there are papers on the feature

Re: Index Utilization

Ksharp — Fri, 26 Nov 2010 02:10:30 GMT

Hi.
Do you use proc sql to create index? and using proc contents to view the index is really created?

Re: Index Utilization

sbb — Fri, 26 Nov 2010 03:52:23 GMT

Suggestion to the OP: share your code, as part of the SAS-generated log (not just your code statements). This would include both index-creating code and any subsequent retrieval code - using COPY / PASTE in your forum post-reply.

Scott Barry
SBBWorks, Inc.

Re: Index Utilization

aha123 — Fri, 26 Nov 2010 04:33:18 GMT

@Peter Thanks! proc sql _method is exactly what I am looking for.

sbb sounds like a mod but useless.