Merging datasets

[aaaaa34]

Hi,

How can I merge these two datasets?

id	name
1	stallone
2	bond
3	perry
4	gomez

name	date
stallone	2009
bond	2013

[PeterClemmensen]

One way

data one;
input id name $;
datalines;
1 stallone
2 bond
3 perry
4 gomez
;

data two;
input name $ date;
datalines;
stallone 2009
bond 2013
;

proc sql;
   create table want as
   select one.*, two.date
   from one left join two
   on one.name=two.name
   order by id;
quit;

Result:

id  name     date 
1   stallone 2009 
2   bond     2013 
3   perry    . 
4   gomez    . 

[PeterClemmensen · Accepted solution]

Another way

data want;
   if _N_=1 then do;
      dcl hash h(dataset:'two');
      h.definekey('id');
      h.definedata('date');
      h.definedone();
   end;

   set one;
   date=.;

   rc=h.find();
run;

[Kurt_Bremser]

Finally, the "classic" data step method:

proc sort data=one;
by name;
run:

proc sort data=two;
by name;
run;

dara want;
merge
  one (in=in_one)
  two
;
by name;
if in_one;
run;

proc sort data=want;
by id;
run;

• the hash method is fastest, but may run out of memory with large data
• the data step method is best when dealing with large data that can't fit in memory, and scales until you run out of disk space in WORK/UTIL
• the SQL is easy to understand for people not eloquent in SAS, scales like above, but can have performance problems with big data.