DATA Step, Macro, Functions and more

Merging datasets into one - not joined

Reply
Contributor
Posts: 22

Merging datasets into one - not joined

Hi,  I have 3 datasets that I will like to merge, but I dont want them joined, just the columns side by side and I need for them to keep the same sorting order they have in the original datasets.

 

 Dataset #1

ID_1 COUNT_1
W 3999
B 344
A 100
N 67
H 7

 

Dataset #2

ID_4 COUNT_4
YES 1000
NO 365

 

Dataset #3

ID_10 COUNT_10
5/5/2014 200
3/5/2010 10
6/9/2016 1

 

 

I want this:

ID_1 COUNT_1 ID_4 COUNT_4 ID_10 COUNT_10
W 3999 YES 1000 5/5/2014 200
B 344 NO 365 3/5/2010 10
A 100     6/9/2016 1
N 67        
H 7        

 

 

Can anyone help me to create a code that will do this?

 

Thank you.

Trusted Advisor
Posts: 1,631

Re: Merging datasets into one - not joined

You want to use the MERGE command. It's called a one-to-one merge;

http://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=basess&docsetTarget=n189t6srul...

PROC Star
Posts: 264

Re: Merging datasets into one - not joined

As @PaigeMiller suggested, you need to merge those datasets. I have also merged datasets by using a join. There are divided into 2 steps fro simplicity. first one is creating rownumbers and second one is joining/merging on basis of rownumber. below is the code.

data dataset1;
set dataset1;
rnum = _n_;
run;
data dataset2;
set dataset2;
rnum = _n_;
run;
data dataset3;
set dataset3;
rnum = _n_;
run;

proc sql;
select ID_1,  COUNT_1, ID_4, Count_4, ID_10, count_10
from dataset1 a
full join dataset2 b
on a.rnum =b.rnum
full join dataset3 c
on a.rnum=c.rnum ;
quit;
Super User
Super User
Posts: 6,502

Re: Merging datasets into one - not joined

You can use the MERGE statement without a BY statement, but you will need to clear the values from the "short" tables to prevent SAs from retaining the values.

data want ;
  merge a b c ;
  * NO BY statement;
  output;
  call missing(of _all_);
run;

Or you could use separate SET statements but then you would need to protect against reading past the end of any of the inputs and you still need to prevent SAS from retaining the last record.  And you also will need to stop the data step manually.

data want ;
  if not eof1 then set a end=eof1;
  if not eof2 then set b end=eof2;
  if not eof3 then set c end=eof3;
  output;
  if eof1 and eof2 and eof3 then stop;
  call missing(of _all_);
run;
Ask a Question
Discussion stats
  • 3 replies
  • 124 views
  • 2 likes
  • 4 in conversation