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?
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;
then use two steps ?
Data SetD;
merge DatasetA DatasetB;
by VAR1;
run;
Data SetDD;
merge SetD DatasetC ;
by VAR2;
run;
sure. not see OP's data , just write some dummy code.
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;
Perfect! thanks! I added a create table as statement to save it to an output file.
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
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.
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.