Hi SAS Forum,
I tried to perform this left joining but my code failed.
/* have_1 is the “master table” whose records are preserved in the final table*/
/*called “Merged” regardless of “have_2” table is having matching records or otherwise*/
*/
data have_1;
informat current_date date9.;
input Bank_number Account_number $ 5-7 Current_date Arrears_Band $22-28;
format current_date date9.;
cards;
10 111 30SEP2010 NPNA
10 111 31OCT2010 Current
10 111 30NOV2010 NPNA
10 111 30JUN2011 NPNA
10 111 01JAN2012 NPNA
70 111 31DEC2011 NPNA
70 111 31JAN2012 NPNA
100 111 30APR2011 90 +
100 111 31MAY2011 90 +
100 111 31JUL2011 NPNA
100 111 31AUG2011 NPNA
100 111 04JAN2012 NPNA
40 555 08FEB2010 30 - 60
40 555 31MAR2010 Current
40 555 30APR2010 60 - 90
40 555 31MAY2010 Current
40 666 03JAN2012 1 - 30
;
run;
data have_2;
informat current_date date9.;
input Bank_number Account_number $ 5-7 Current_date Arrears_Band $22-28;
format current_date date9.;
cards;
10 111 01FEB2012 writoff
70 111 03FEB2012 writoff
70 999 03FEB2012 writoff
;
run;
/*Using above 2 tables, I needed to create a table like this*/
Bank_number Account_number Current_date Arrears_Band
10 111 30SEP2010 NPNA
10 111 31OCT2010 Current
10 111 30NOV2010 NPNA
10 111 30JUN2011 NPNA
10 111 01JAN2012 NPNA
10 111 01FEB2012 writoff /*This record is coming from have_2 table*/
70 111 31DEC2011 NPNA
70 111 31JAN2012 NPNA
70 111 03FEB2012 writoff /*This record is coming from have_2 table*/
100 111 30APR2011 90 +
100 111 31MAY2011 90 +
100 111 31JUL2011 NPNA
100 111 31AUG2011 NPNA
100 111 04JAN2012 NPNA
40 555 08FEB2010 30 - 60
40 555 31MAR2010 Current
40 555 30APR2010 60 - 90
40 555 31MAY2010 Current
40 666 03JAN2012 1 - 30
*/
/*The is the code I attempted*/
/*Left joining */
proc sql;
create table Merged as select *
from have_1 as a left join have_2 as b
on a.bank_number=b.Bank_number and a.account_number=b.account_number ;
quit;
Question:
1. My code doesn't do the intended joining (or any joining for that matter)
2). /*And my code creates this warning too*/
/*WARNING: Variable current_date already exists on file WORK.MERGED.
WARNING: Variable Bank_number already exists on file WORK.MERGED.
WARNING: Variable Account_number already exists on file WORK.MERGED.
WARNING: Variable Arrears_Band already exists on file WORK.MERGED.*/
Could anyone help me?
Thanks
Mirisage
Your code involves a match merge but your desired output shows inserted new rows (as Reeza says).
Using the UNION operator with ORDER BY should enable you to get what you want:
proc sql;
create table Merged2 as
select a.*
from have_1 as a
union
select b. *
from have_2 as b,
have_1 as a2
where b.bank_number = a2.bank_number
and b.account_number = a2.account_number
order by bank_number, account_number;
quit;
BTW it's not advisable to store bank/account numbers as numerics in SAS because of precision issues - best all stored as character fields.
If you join two tables that have columns with the same name, how does SAS know which variable to take.
Basically it doesn't and you need to tell it.
But regardless you looking to insert records not join columns, so you need an insert or append instead.
Your code involves a match merge but your desired output shows inserted new rows (as Reeza says).
Using the UNION operator with ORDER BY should enable you to get what you want:
proc sql;
create table Merged2 as
select a.*
from have_1 as a
union
select b. *
from have_2 as b,
have_1 as a2
where b.bank_number = a2.bank_number
and b.account_number = a2.account_number
order by bank_number, account_number;
quit;
BTW it's not advisable to store bank/account numbers as numerics in SAS because of precision issues - best all stored as character fields.
Why are you using SQL? Why not just set the tables together.
data merged2;
set have_1 have_2 ;
by bank_number account_number ;
run;
If you want to eliminate the record from have_2 where the account is not in have_1 (the last line in your example) then you can do a little more work.
data merged2;
do until (last.account_number);
set have_1 (in=in1) have_2 ;
by bank_number account_number ;
if in1 then any_in1=1;
end;
do until (last.account_number);
set have_1 have_2(in=in2) ;
by bank_number account_number ;
if not (in2 and not any_in1) then output;
end;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.