BookmarkSubscribeRSS Feed
ismahero2
Obsidian | Level 7

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.

3 REPLIES 3
kiranv_
Rhodochrosite | Level 12

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

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 791 views
  • 2 likes
  • 4 in conversation