topic Merging datasets in SAS Data Management

Merging datasets

aaaaa34 — Tue, 12 Nov 2019 18:31:37 GMT

Hi,

How can I merge these two datasets?

id	name
1	stallone
2	bond
3	perry
4	gomez

name	date
stallone	2009
bond	2013

Re: Merging datasets

PeterClemmensen — Tue, 12 Nov 2019 18:34:53 GMT

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    .

Re: Merging datasets

PeterClemmensen — Tue, 12 Nov 2019 18:41:50 GMT

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;

Re: Merging datasets

Kurt_Bremser — Wed, 13 Nov 2019 09:17:59 GMT

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.