BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sarahsasuser
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

6 REPLIES 6
Ksharp
Super User

then use two steps ?

Data SetD;

     merge DatasetA  DatasetB;

     by VAR1;

run;

Data SetDD;

     merge SetD DatasetC ;

     by VAR2;

run;

Keith
Obsidian | Level 7

Obviously SetD may need sorting before the 2nd merge

Ksharp
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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

venkatnaveen
Obsidian | Level 7

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

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
  • 6 replies
  • 14723 views
  • 0 likes
  • 5 in conversation