Why this left join code is not working?

[Mirisage]

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

[Reeza]

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.

[SASKiwi · Accepted solution]

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.

[Tom]

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;