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 (June 2003, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 18 Jun 2003 09:14:40 -0500
Reply-To:     peetie_wheatstraw@lycos.com
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Peetie Wheatstraw <peetie_wheatstraw@LYCOS.COM>
Organization: Lycos Mail  (http://www.mail.lycos.com:80)
Subject:      Re: keyed access, data step, mult. indices
Content-Type: text/plain; charset=us-ascii

--------- Original Message ---------

DATE: Mon, 16 Jun 2003 18:05:34 From: "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM> Subject: Re: keyed access, data step, mult. indices To: SAS-L@LISTSERV.UGA.EDU Cc:

--- snip --------------------------------------------------

>> This is the composite index of which I spoke. Thanks. >> >> This is probably "A Wisp In The Wind", but does anyone >> remember ISAM (Indexed-Sequential Access Method)? Seems >> one could index: >> >> ID1 ID2 >> ___ ___ >> 122 998 >> 123 222 >> 123 333 >> 123 444 >> 123 555 >> 124 111 >> >> on ID1 and ID2. Then if you asked for ID1=123 you'd get the >> 123 222 rec. But if you needed/asked for the ID1=123 ID2=444 rec >> you'd get precisely that. This is all from memories of a >> long-past life. >> >> Is it safe to assume that this kind of indexed access is >> impractical with SAS? How do SAS indexing internals >> work?? :-) >> >> The OnlineDoc seems to a do reasonable job of explaining >> outcomes of indexed access using the where stmt. Above and >> beyond that, I find its coverage of indexed access inadequate. >>

--- snip --------------------------------------------------

> >If I recall my ISAM days, the record might have a key of length N of which >you could specify a key value of length < N, and it would utilize the index >for returning records matching the partial key.

For a single character key? I think maybe it (ISAM) did. Here's a quick and dirty test with SAS:

173 data x; 174 input Fund Stock $ Weight; 175 put _all_; 176 datalines;

Fund=1 Stock=A Weight=0.3 _ERROR_=0 _N_=1 Fund=1 Stock=B Weight=0.7 _ERROR_=0 _N_=2 Fund=2 Stock=C Weight=0.4 _ERROR_=0 _N_=3 NOTE: The data set WORK.X has 3 observations and 3 variables. NOTE: DATA statement used: real time 0.03 seconds cpu time 0.02 seconds

180 ; 181 run; 182 183 data y(index = (stock date)); 184 input Stock $ Date :mmddyy8. Return; 185 format date date9.; 186 put _all_; 187 datalines;

Stock=ABCD Date=01JAN1998 Return=0.01 _ERROR_=0 _N_=1 Stock=ABCD Date=02JAN1998 Return=-0.02 _ERROR_=0 _N_=2 Stock=BCDE Date=01JAN1998 Return=0.02 _ERROR_=0 _N_=3 Stock=BCDE Date=02JAN1998 Return=0.03 _ERROR_=0 _N_=4 Stock=CDEF Date=01JAN1998 Return=-0.01 _ERROR_=0 _N_=5 NOTE: The data set WORK.Y has 5 observations and 3 variables. NOTE: Simple index date has been defined. NOTE: Simple index stock has been defined. NOTE: DATA statement used: real time 0.04 seconds cpu time 0.02 seconds

193 ; 194 run; 195 196 data z; 197 set x; 198 set y key = stock; 199 put _all_; 200 if _iorc_ = 0 then output; 201 run;

Fund=1 Stock=A Weight=0.3 Date=. Return=. _ERROR_=1 _IORC_=1230015 _N_=1 Fund=1 Stock=A Weight=0.3 Date=. Return=. _ERROR_=1 _IORC_=1230015 _N_=1 Fund=1 Stock=B Weight=0.7 Date=. Return=. _ERROR_=1 _IORC_=1230015 _N_=2 Fund=1 Stock=B Weight=0.7 Date=. Return=. _ERROR_=1 _IORC_=1230015 _N_=2 Fund=2 Stock=C Weight=0.4 Date=. Return=. _ERROR_=1 _IORC_=1230015 _N_=3 Fund=2 Stock=C Weight=0.4 Date=. Return=. _ERROR_=1 _IORC_=1230015 _N_=3 NOTE: There were 3 observations read from the data set WORK.X. NOTE: There were 0 observations read from the data set WORK.Y. NOTE: The data set WORK.Z has 0 observations and 5 variables.

Of course, it is _not_ doing ISAM. What _is_ it doing?

>In my head this sound very much like having an composite key index and a >processor that utilizes the index when only a partial of the composite is >provided. > >I think this example indicates that the where clause uses the index in >partial. > > >data foo; > do level1 = 1 to 1e6; > do level2 = 1 to 5; > x = ranuni (0); > y = ranuni (0); > output; > end; > end; >run; > >data foo2 (index=(focus=(level1 level2))); > set foo; >run; > >%let key1 = level1=4e5; >%let key2 = level1=5e5 and level2=3; > >options msglevel=i; > >data a1; > set foo; > where &key1; >run; > >data a2; > set foo; > where &key2; >run; > > >data b1; > set foo2; > where &key1; >run; > >data b2; > set foo2; > where &key2; >run;

You mean it uses the first of the 2 indexed fields in the composite key (per my query) if so specified. Yes, it does. This is a good illustration. I found an example in the Onlinedoc that I had previously missed.

Thanks, Peetie

------------------------------------------------------------ | Peetie Wheatstraw peetie_wheatstraw@lycos.com | ------------------------------------------------------------

____________________________________________________________ Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005


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