Perform Lookup in Sas

wizkid2050 — Sun, 16 Oct 2016 04:59:27 GMT

data t1;
input name $ age2;
infile datalines missover;
datalines;
a1 1
a2 2
a3 3
a4 4
a5 4
a6 3
;run;

data t2;
input name $ age1;
infile datalines missover;
datalines;
a1
a1
a1
a1
a1
a2
a2
a2
a2
a2
a3
a3
a3
a3
a3
a3
a3
;
run;

I have above two data sets
I want the output
a1 1
a1 1
a1 1
a1 1
a1 1
a2 2
a2 2
a2 2
a2 2
a2 2
a3 3
a3 3
a3 3
a3 3
a3 3
a3 3
a3 3

Hope the problem is clear.

Re: Perform Lookup in Sas

Patrick — Sun, 16 Oct 2016 06:06:42 GMT

Below the 3 most common ways of getting there.

data t1;
input name $ age2;
infile datalines missover;
datalines;
a1 1
a2 2
a3 3
a4 4
a5 4
a6 3
;run;
data t2;
input name $ age1;
infile datalines missover;
datalines;
a1 
a1
a1
a1
a1
a2
a2
a2
a2
a2
a3
a3
a3
a3
a3
a3
a3
;
run;

proc sql;
  create table want1 as
    select a.name, b.age2 as age
    from t2 as a left join t1 as b
      on t1.name=t2.name
  ;
quit;

data want2;
  merge t2 (in=int2) t1;
  by name;
  if int2;
  age=age2;
  drop age1 age2;
run;

data want3;
  set t2(rename=(age1=age));
  if _n_=1 then
    do;
      dcl hash h1(dataset:'t1(keep=name age2 rename=(age2=age))');
      h1.defineKey('name');
      h1.defineData('age');
      h1.defineDone();
    end;

  h1.find();
run;

A 4th option would be to create a format out of table t1.

Be aware that if the relationship between the two tables is many:many then the results between above three approaches will differ.

topic Perform Lookup in Sas in SAS Programming

Perform Lookup in Sas

Re: Perform Lookup in Sas