merging datasets

Reply
Contributor
Posts: 27

merging datasets

Hi There,

 

Could anyone please help me in the merging of two datasets.

 

E.g. - Let's say I have two datasets 'Tabel A' and 'Table B' which are as follows - 

 

'Table A'                                                                      

 

IDVisit1Visit1 _DateVisit1_Time
101V11/28/201510:02
101V26/2/201520:00
101V49/20/201512:15
102V12/2/201512:20
102V36/20/201510:20
102V49/29/201512:55

 

'Table B'

 

IDVisit2Visit2_DateVisit2_Time
101V11/28/201512:20
101V37/12/201515:40
102V12/2/201512:00
102V26/21/201518:20
102V49/29/201519:20

 

By merging these above two datasets, I want to create a third dataset 'Tabel C' which is as follow.

 

'Table C'

 

IDVisit1Visit2Visit1 _DateVisit1_TimeVisit2_DateVisit2_Time
101V1V11/28/201510:021/28/201512:20
101V2.6/2/201520:00..
101.V3..7/12/201515:40
101V4.9/20/201512:15..
102V1V12/2/201512:202/2/201512:00
102.V2..6/21/201518:20
102V3.6/20/201510:20..
102V4V49/29/201512:559/29/201519:20

 

Can anyone help me out here. 

 

Many Thanks in advance.

 

KR,

VIkrant

Esteemed Advisor
Posts: 5,198

Re: merging datasets

It seems that you simple want to perform an full outer join.

Data step merge by would probably work good as well, but then you need to rename the date variable so they have matching names. (and then they only will be stored once, unless you duplicate them in each input dataset).

Data never sleeps
Grand Advisor
Posts: 17,321

Re: merging datasets

proc sql;
create table want as
select a.*, b.*
from table1 as a
outer join table2 as b
on a.id=b.id and a.visit1=b.visit2
order by a.id, a.visit1, b.visit2;
quit;

You may want to explicitly list the variables because you probably want a specified order but this should help get you started.
Valued Guide
Posts: 854

Re: merging datasets

Most of this is cosmetic, if the output can be changed this can be done easier, but here it is:

 



data one;
infile cards dsd;
informat id $3. visit1 $2. visit1_date mmddyy10. visit1_time time5.;
format id $3. visit1 $2. visit1_date mmddyy10. visit1_time time5.;
input ID Visit1$ Visit1_Date Visit1_Time;
cards;
101,V1,1/28/2015,10:02
101,V2,6/2/2015,20:00
101,V4,9/20/2015,12:15
102,V1,2/2/2015,12:20
102,V3,6/20/2015,10:20
102,V4,9/29/2015,12:55
;
run;

data two;
infile cards dsd;
informat id $3. visit2 $2. visit2_date mmddyy10. visit2_time time5.;
format id $3. visit2 $2. visit2_date mmddyy10. visit2_time time5.;
input ID Visit2$ Visit2_Date Visit2_Time;
cards;
101,V1,1/28/2015,12:20
101,V3,7/12/2015,15:40
102,V1,2/2/2015,12:00
102,V2,6/21/2015,18:20
102,V4,9/29/2015,19:20
;

proc sql;
create table prep as
select
a.id as aid,
b.id as bid,
visit1,visit2,visit1_date,visit1_time,visit2_date,visit2_time
from one a full join
     two b on
a.id=b.id and
a.visit1=b.visit2;

data want(drop=aid bid);
format id $5.;
set prep;
id= strip(coalesce(aid,bid));
run;

Ask a Question
Discussion stats
  • 3 replies
  • 305 views
  • 0 likes
  • 4 in conversation