BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aaaaa34
Calcite | Level 5

Hi,

 

How can I merge these two datasets?

idname
1stallone
2bond
3perry
4gomez

 

namedate 
stallone2009
bond2013
1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Super User

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.

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1036 views
  • 0 likes
  • 3 in conversation