LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2009, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 1 May 2009 16:56:43 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: basic questions about index a large SAS file
In-Reply-To:  <037AB3FF38D44C4BAFB5DFF3D06B57BA020AEA87@EX-CMS01.westat.com>
Content-Type: text/plain; charset="us-ascii"

As Joe politely suggests, SAS SQL makes limited use of standing indexes. May I suggest a possible workaround?

My SGF paper on Predictive Modelling includes a Data step hash-object program that generates a summary of a SAS dataset. Paul Dorfman of SAS-L (Hall of) Fame wrote a program that I lightly adapted into a summary procedure with few parameters. It will generate a summary of a SAS dataset variable or two, properly rounded, in a flash. In a very large dataset, many of the rounded values will repeat many times. The means of rounded values will almost always differ trivially from the means of the original values. So PROC MEANS with the WEIGHT statement will compute means or other summary statistics very quickly and efficiently.

Should one need means subsets in narrow ranges of variable values, an index may work even faster. In that case, a SAS SQL view as PROC MEANS input will attempt to optimize selection of relevant obs before feeding those obs to PROC MEANS.

Others may suggest alternatives that I haven't considered. What SAS lacks in prepackaged query and procedure optimization, it makes up for it with a wealth of workarounds. S

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Michael Raithel Sent: Friday, May 01, 2009 3:38 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: basic questions about index a large SAS file

Dear SAS-L-ers,

Omnipotent SAS-L Rookie of the Year Joe Matise posted the following regarding my reply to Christine:

> > Michael, I think there might be a level of misunderstanding here... I > think Christine is asking whether PROC MEANS > (/SUMMARY) uses the index when you use the CLASS statement, not a > WHERE statement. (I don't know the answer to that question myself > unfortunately...) Unless I missed the answer to that in your answer > somewhere? > Joe,

Thanks! As I mentioned in our brief, non-L side-bar conversation:

A second reading of the original post in light of your comments makes me think that you are right. The answer would be a categorical NO if that is what she is asking. SAS only drops the hammer on an index in four circumstances:

1. WHERE expression 2. BY statement 3. The KEY option on a MODIFY statement 4. The KEY option on a SET statement

It doesn't fire off an index just because an index key variable is a co-defendant in a CLASS statement.

Joe, best of luck in all of your SAS endeavors!

I hope that this suggestion proves helpful now, and in the future!

Of course, all of these opinions and insights are my own, and do not reflect those of my organization or my associates. All SAS code and/or methodologies specified in this posting are for illustrative purposes only and no warranty is stated or implied as to their accuracy or applicability. People deciding to use information in this posting do so at their own risk.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Michael A. Raithel "The man who wrote the book on performance" E-mail: MichaelRaithel@westat.com

Author: Tuning SAS Applications in the MVS Environment

Author: Tuning SAS Applications in the OS/390 and z/OS Environments, Second Edition

http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172

Author: The Complete Guide to SAS Indexes

http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Live so that you wouldn't be ashamed to sell the family parrot to the town gossip. - Will Rogers +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Back to: Top of message | Previous page | Main SAS-L page