DATA Step, Macro, Functions and more

Perform Lookup in Sas

Reply
Contributor
Posts: 23

Perform Lookup in Sas

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.

Respected Advisor
Posts: 4,173

Re: Perform Lookup in Sas

[ Edited ]
Posted in reply to wizkid2050

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.

Ask a Question
Discussion stats
  • 1 reply
  • 216 views
  • 0 likes
  • 2 in conversation