topic Re: Merging datasets into one - not joined in SAS Programming

Merging datasets into one - not joined

ismahero2 — Thu, 08 Jun 2017 17:00:52 GMT

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.

Re: Merging datasets into one - not joined

PaigeMiller — Thu, 08 Jun 2017 17:04:39 GMT

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=n189t6srulapcpn1xirhprgts4z1.htm&locale=en

Re: Merging datasets into one - not joined

kiranv_ — Thu, 08 Jun 2017 17:35:04 GMT

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;

Re: Merging datasets into one - not joined

Tom — Thu, 08 Jun 2017 17:53:16 GMT

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;