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.
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.
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.
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.