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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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