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