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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.