BookmarkSubscribeRSS Feed
wizkid2050
Fluorite | Level 6

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.

1 REPLY 1
Patrick
Opal | Level 21

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1149 views
  • 0 likes
  • 2 in conversation