topic Re: Why this left join code is not working? in SAS Procedures

Why this left join code is not working?

Mirisage — Tue, 19 Feb 2013 22:42:39 GMT

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

Re: Why this left join code is not working?

Reeza — Tue, 19 Feb 2013 23:08:59 GMT

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.

Re: Why this left join code is not working?

SASKiwi — Tue, 19 Feb 2013 23:30:48 GMT

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.

Re: Why this left join code is not working?

Tom — Tue, 19 Feb 2013 23:46:26 GMT

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;