Newsgroups: bit.listserv.sas-l
Date:         Fri, 11 Sep 1992 11:48:51 EDT
Reply-To:     Andy Norton <76350.1604@COMPUSERVE.COM>
From:         Andy Norton <76350.1604@COMPUSERVE.COM>
Subject:      SET KEY=, crucial undocumented details
Comments: To: SAS-L <SAS-L@AWIIMC12.IMC.UNIVIE.AC.AT>

----------------------------------------------------------------------
CONTENT:   Information
SUMMARY:   If you want to use SET KEY=, you need to know this.
           SET KEY= with multiple master observations.
           SET KEY= with no lookup observation.
REL/PLTF:  6.07.01/CMS, 6.04/PC-DOS
E-ADDR:    76350.1604@compuserve.com
NAME:      Andy Norton
ADDRESS:   Trilogy Consulting, 5228 Lovers Lane, Kalamazoo MI 49002
PHONE:     (616) 344-2191
----------------------------------------------------------------------

This has nothing to do with precision, I promise <grin>.

The new SET KEY= feature of 6.07 is inadequately documented.  In fact,
the example shown in Technical Report P-222 (Enhancements to
Base 6.07), page 43 is artificially simplified in two ways.  Practical
applications require the undocumented UNIQUE feature.

data COMBINE;
  set INVTORY (keep=PARTNO INSTOCK PRICE);
  set PARTCODE (keep=PARTNO DESC) key=PARTNO;
run;
-------------------------
1.  If no match is found for a key, _IORC_ is set to -1, which is the
value of %SYSRC(_SWEOF) =  -1.  (The mnemonic _SWEOF stands for
Warning: End of File).  No variables are set to missing.  In
the example, the previous value of DESC will remain.  A realistic
application would probably include a statement such as
   if _IORC_ eq %SYSRC(_SWEOF) then
      DESC = ' ';
after the SET PARTCODE statement.

-------------------------
2.  When SET KEY= is called consecutively with the same key values, it
retrieves additional observations.   If there are no more observations
to be had, the SET fails (see point #1).  Suppose we have only one
observation in PARTCODE for each PARTNO, but two consecutive
observations in INVTORY have the same PARTNO value.  Then on the second
observation, _IORC_ will return -1.  If there was a third observation,
the SET statement resets to the beginning.

The example in the book ignores the _IORC_ code, so duplicate keys in
the master file appear to work correctly.  But this is sloppy
programming.  Slight modifications make the weaknesses readily
apparent:

   /* Doesn't work */
  data COMBINE;
      set INVTORY (keep=PARTNO INSTOCK PRICE DESC);
      if DESC = ' ' then
         set PARTCODE (keep=PARTNO DESC) key=PARTNO;
  run;

In this example, DESC is reset to missing prior to each SET KEY=.  If
two consecutive inventory observations have the same PARTNO, the second
SET KEY= will fail and the output will have a missing DESC.

What we _want_ to do is to obtain the first matching observation from
PARTCODE every time we run the SET KEY= statement, regardless of
previous executions.  I called SAS Institute Technical Support and was
told about the undocumented UNIQUE option (usage note U-4897,
unshipped).

If there are any observations matching the KEY, the UNIQUE option will
always give you the first one:
     set PARTCODE (keep=PARTNO DESC) key=PARTNO /unique;
I think this option should ALWAYS be used unless you deliberately want
to retrieve multiple matches.

When a SET KEY=/UNIQUE statement returns an _IORC_ value of -1, you
know that no observations with that key exist.

---------------------------------
Additional notes
1)   Multiple SET statements work independently:
      data _null_;
          PARTNO = 7;
          set PARTCODE (keep=PARTNO DESC)
              key=PARTNO;  /* first occurrence */
          set PARTCODE (keep=PARTNO DESC)
              key=PARTNO; /* also first occurence */
          stop;
      run;

2)  There is no executable way to force rewinding of the cursor to the
first observation.  The UNIQUE option must be specified at
compilation.

I think there are some sequential searching applications which would~
start the search with the first observation, proceed sequentially, and
perhaps stop early.  To do this we need a variable that can be toggled
during execution, something like:
     REWIND = 1;
     do until (FOUND);
        set LOOKUP key=I /rewind=REWIND;
        REWIND = 0;
        ...
        end;

------------------------
Personally, I'd rather be using SQL :-).  The SAS documentation example
could be written as
    proc sql;
       select INVTORY.PARTNO, DESC, INSTOCK, PRICE
       from    INVTORY left join PARTCODE
                  on INVTORY.PARTNO = PARTCODE.PARTNO;
    quit;

Like the SET KEY= example, this code does not require sorting or
indexing of INVTORY.  Unlike the SET KEY= example (as printed in the
documentation), the SQL code works when no matching PARTNO is found in
PARTCODE, or when consecutive INVTORY observations have the same
PARTNO.

                                                Andy