topic Re: Merging 3 datasets in ODS and Base Reporting

Merging 3 datasets

sarahsasuser — Wed, 02 Jul 2014 17:52:18 GMT

Hi All,

I have 3 datasets I want to merge where there is not a unique ID to all three. For example,

DatasetA - VAR1 VAR2 VAR3

DatasetB - VAR1 VAR4

DatasetC - VAR2 VAR5

This code does not work:

Data SetD;

merge DatasetA DatasetB (in= VAR1) DatasetC (in=VAR2);

by VAR1 VAR2;

run;

What would work?

Re: Merging 3 datasets

Ksharp — Thu, 03 Jul 2014 13:55:39 GMT

then use two steps ?

Data SetD;

merge DatasetA DatasetB;

by VAR1;

run;

Data SetDD;

merge SetD DatasetC ;

by VAR2;

run;

Re: Merging 3 datasets

Keith — Thu, 03 Jul 2014 14:04:56 GMT

Obviously SetD may need sorting before the 2nd merge

Re: Merging 3 datasets

Ksharp — Thu, 03 Jul 2014 14:18:21 GMT

sure. not see OP's data , just write some dummy code.

Re: Merging 3 datasets

RW9 — Thu, 03 Jul 2014 14:18:27 GMT

Use proc sql:

proc sql;

select COALESCE(A.VAR1,B.VAR1) as VAR1,

COALESCE(A.VAR2,C.VAR2) as VAR2,

A.VAR3,

B.VAR4,

C.VAR5

from DSA A

full join DSB B

on A.VAR1=B.VAR1

full join DSC C

on A.VAR2=C.VAR2;

quit;

Re: Merging 3 datasets

sarahsasuser — Thu, 03 Jul 2014 14:30:37 GMT

Perfect! thanks! I added a create table as statement to save it to an output file.

Re: Merging 3 datasets

venkatnaveen — Wed, 09 Jul 2014 12:57:52 GMT

111 data accountholders;

112 infile cards;

113 input accountnumber area $ type $;

114 cards;

NOTE: The data set WORK.ACCOUNTHOLDERS has 3 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.20 seconds

cpu time 0.15 seconds

118 ;

119 data creditholders;

120 infile cards;

121 input type $ days;

122 cards;

NOTE: The data set WORK.CREDITHOLDERS has 3 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

126 ;

127 data loanholders;

128 infile cards;

129 input area $ loanamount;

130 cards;

NOTE: The data set WORK.LOANHOLDERS has 3 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

134 ;

135 proc sql;

136 select coalesce(accountholders.type,creditholders.type) as type,

137 coalesce(accountholders.area,creditholders.area) as area,

138 accountholders.accounttype,creditholders.days,loanholders.loanamount

139 from accountholders

140 full join creditholders

141 on accountholders.type=creditholders.type

142 full join loanholders

143 on accountholders.area=loanholders.area;

ERROR: Column area could not be found in the table/view identified with the correlation name

CREDITHOLDERS.

ERROR: Column accounttype could not be found in the table/view identified with the correlation name

ACCOUNTHOLDERS.

144 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.63 seconds

cpu time 0.11 second

i am getting following error for this sample