Lookup rows in a different table based on column values

[art297]

I just noticed your post about the variable name mismatch.  The same logic I originally suggested could easily be adapted for non-matching names.  e.g.:

data table1;

  input ValueABC ValueQQQ ValueAAA ValueCCD;

  cards;

1 0 0 0

0 1 1 0

0 0 1 1

1 0 1 0

1 1 1 1

;

run;

data table2;

  input Value Return;

  cards;

1 5

2 18

3 2

4 1

;

run;

%let n=4;

data want (drop=Value Return);

  array recodes(&n.)_temporary_;

  if _n_ eq 1 then do;

    do _n_=1 to &n.;

      set table2;

   recodes(value)=Return;

  end;

  end;

  set table1;   

  array values(*) ValueABC--ValueCCD;

  do _n_=1 to dim(values);

    total=sum(total,values(_n_)*recodes(_n_));

  end;

run;

[kaushal2040]

Hi Art,

I have seen using if _n_=1 then do in hash objects.  However, what is the purporse of it in regular SAS programms other than hash programming.      From what I understand, it means do something for the first iteration of data step.  Does this means only do for the first observation of the data and what about other data step iterations?  Thank you for your clarification.

[HSARAI]

kaushal2040, I can answer that since I encountered it myself. The purpose for using the if _n_=1 flag is so that the hash object is only defined once. If the flag is not used, then the hash object is re-declared for every row of the dataset, which is unnecessary.

[Reeza]

If _n_=1 does mean do something at the begininning of the data step. It is used for doing something only once, ie loading a temporary array, initializing variables, creating a hash table.

Art's solution is similar to @Hsarai explanation, except rather than a Hash table, the lookup table is loaded into memory, but into a Temporary Array.

Since both are loaded into memory I'd expect the performance to be similar.

[Haikuo · Accepted solution]

Hash seems pretty handy for this kind of lookup:

data source;

   input value1 value2 value3 value4;

   cards;

1    0 0    0

0    1 1    0

0    0 1    1

1    0 1    0

1    1 1    1

;;;;

   run;

data lookup;

   input value return;

   cards;

1    5

2    18

3    2

4    1

;;;;

data want;

   if _n_=1 then do;

      declare hash h(dataset:'lookup');

      h.definekey('value');

      h.definedata('return');

      h.definedone();

      call missing (value,return);

    end;

    set source;

    array _v value1-value4;

      do over _v;

        if _v=1 then do;

          rc=h.find(key:_i_);

          total=sum(total,return);

         end;

      end;

        drop value return rc;

run;

Haikuo

[HSARAI]

There were many different options proposed but I feel that the hash solution is the most flexible, though perhaps not the most efficient. Thank you everyone for your help!