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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.