SAS Lookup on Per Variable Basis

Question

I have two tables in SAS, Table A and Table B. Suppose I want to write a little SAS code to obtain the table "Desired Output." How would I do this?

Table A:

Observation  Var1   Var2
1            0      0
2            1      2
3            2      1
4            0      0

Table B:

Var     Level   Lookup
Var1    0       0.1
Var1    1       0.3
Var1    2       0.5
Var2    0       0.7
Var2    1       0.8
Var2    2       0.9

Desired output:

Observation Var1    Var2    Var1_new    Var2_new
1           0       0       0.1         0.7
2           1       2       0.3         0.9
3           2       1       0.5         0.8
4           0       2       0.1         0.9

From my understanding, this may involve SQL in SAS, but I'm not sure. I have no idea how to do this. Pseudo-code might look like this, but I don't know how to actually make it work:

data DATA_OUT.DESIRED_OUTPUT;
set DATA_IN.TABLE_A;
set PP.TABLE_B key=(Var Level);

Var1_new = TABLE_B["Var1" Var1][Lookup];
Var2_new = TABLE_B["Var2" Var2][Lookup];
run;

How would you achieve the desired output in SAS?

user_1623007 · Answer

There's about a dozen ways to do this, but the best way for what you have there is probably to make a format from the second dataset.

Formats are just relationships between one value and another value, which is exactly what you have here! You use the CNTLIN option on PROC FORMAT to create the relationship from a dataset (your dataset B) and then apply it using PUT. (Then use INPUT to change back to a number - formats only create character values. You can't use INFORMAT here because those only take character values as input. Number to number always takes an extra step.)

You could also use a hash table lookup, or just a pair of data step merges, or keyed set statements... a lot of options, as well as SQL joins. But format here will be the fastest and the easiest to code IMO.

data a;
input Observation  Var1   Var2;
datalines;
1            0      0
2            1      2
3            2      1
4            0      0
;;;;
run;

data b;
input Var $  Level   Lookup;
datalines;
Var1    0       0.1
Var1    1       0.3
Var1    2       0.5
Var2    0       0.7
Var2    1       0.8
Var2    2       0.9
;;;;
run;

*Here we make a new dataset that has the required names for a format cntlin dataset;
data for_fmt;
  set b;
  rename var=fmtname 
         level=start
         lookup=label
  ;
  var = cats(var,'F');  *format names cannot end with numbers, so add an F at the end;
run;
proc format cntlin=for_fmt;  *read in the format;
quit;

*now use the formats;
data want;
  set a;
  var1_new = input(put(var1,var1f.),best12.);
  var2_new = input(put(var2,var2f.),best12.);
run;

user_4965549 · Answer

Here is a method using a hash object to store your table B.

data A ;
 input var1 var2;
cards;
0 0
1 2
2 1
0 0
;    
data B;
  input Var :$32. Level Lookup;
cards;
Var1 0 0.1
Var1 1 0.3
Var1 2 0.5
Var2 0 0.7
Var2 1 0.8
Var2 2 0.9
;

data want;
  if _n_=1 then do;
    if 0 then set b;
    dcl hash h(dataset: 'b');
    h.definekey('var','level');
    h.definedata('lookup');
    h.definedone();
  end;
  set a;
  h.find(key:'Var1',key:var1);
  lookup1=lookup;
  h.find(key:'Var2',key:var2);
  lookup2=lookup;
  drop var level lookup;
run;

SAS Lookup on Per Variable Basis

2 Answers