Merging 3 datasets

[sarahsasuser]

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?

[Ksharp]

then use two steps ?

Data SetD;

     merge DatasetA  DatasetB;

     by VAR1;

run;

Data SetDD;

     merge SetD DatasetC ;

     by VAR2;

run;

[Keith]

Obviously SetD may need sorting before the 2nd merge

[Ksharp]

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

[RW9 · Accepted solution]

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;

[sarahsasuser]

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

[venkatnaveen]

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