## Merging 3 datasets

Solved
Frequent Contributor
Posts: 110

# Merging 3 datasets

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?

Accepted Solutions
Solution
‎07-03-2014 10:18 AM
Super User
Posts: 9,599

## Re: Merging 3 datasets

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;

All Replies
Super User
Posts: 10,761

## Re: Merging 3 datasets

then use two steps ?

Data SetD;

merge DatasetA  DatasetB;

by VAR1;

run;

Data SetDD;

merge SetD DatasetC ;

by VAR2;

run;

Regular Contributor
Posts: 151

## Re: Merging 3 datasets

Obviously SetD may need sorting before the 2nd merge

Super User
Posts: 10,761

## Re: Merging 3 datasets

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

Solution
‎07-03-2014 10:18 AM
Super User
Posts: 9,599

## Re: Merging 3 datasets

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;

Frequent Contributor
Posts: 110

## Re: Merging 3 datasets

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

Frequent Contributor
Posts: 89

## Re: Merging 3 datasets

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

🔒 This topic is solved and locked.