How to merge two tables while giving variable values-II

[Mirisage]

Hi SAS Community,

I have two tables (Have_1 and Have_2).

data Have_1;

informat Current_date date9.;

Input Current_date   Account_number $12-14 Arrears_Band $ 17-23  balance bank_number;

Format Current_date date9.;

cards;

28FEB2010  111  NPNA    10 100

31MAR2010  111  Current 20 100

31MAY2010  111  30 - 60 30 100

28FEB2010  444  Current 40 900

31MAR2010  444  30 - 60 50 900

30APR2010  444  30 - 60 60 900

31MAY2010  444  Current 70 900

30APR2010  555  Current 80 700

31MAY2010  555  NPNA    90 700

31AUG2011  555  90 +    12 700

30SEP2011  555  NPNA    0  700

31MAY2010  666  Current 15 400

31AUG2011  666  90 +    20 400

30SEP2011  666  Current 30 400

;

run;

data Have_2;

informat Post_date date9.;

Input Post_date Account_number $12-14 Net_Write_off_total_USD bank_number;

Format Post_date date9.;

cards;

31AUG2010  111  50 100

31AUG2010  111  50 200

30SEP2011  555  77 700

30SEP2011  666  77 300

;

run;

Question:

I need to join these 2 tables so that I could get the answer below. It is mandatory that I need to keep all the records in have_1 table regardless of whether the have_2 table is having corresponding matching records or not. But reverse is not mandatory, i.e. have_2 table may have excess (non matching) records, and they should not show up in final table that I want.     .    

Answer:

/*    Final data set should be like this

Notice the 2 records from have_2 table are embedded within have_1 table in date sequence. And a value called “writoff” was assigned.

Current_date  Account_number   Arrears_Band      balance     bank_number

28FEB2010  111  NPNA    10 100

31MAR2010  111  Current 20 100

31MAY2010  111  30 - 60 30 100

31AUG2010  111  writoff 50 100

28FEB2010  444  Current 40 900     

31MAR2010  444  30 - 60 50 900

30APR2010  444  30 - 60 60 900

31MAY2010  444  Current 70 900

30APR2010  555  Current 80 700

31MAY2010  555  NPNA    90 700

31AUG2011  555  90 +    12 700

30SEP2011  555  NPNA    0  700

30SEP2011  555  writoff 77 700

31MAY2010  666  Current 15 400

31AUG2011  666  90 +    20 400

30SEP2011  666  Current 30 400  

The code below (Ksharp is acknowledged) generates the wanted table to some extent but I have newly included bank_number variable to reflect business reality. Now the results are misleading us.

data want;

set have_1 have_2(in=inb  rename=(Post_date=Current_date Net_Write_off_total_USD=balance)) ;

by Account_number;

if inb then Arrears_Band='writoff ';

run;

proc sort data=want;by Account_number Current_date;run;

This is the output generated by above code.

I have striken through 2 records, and they should not be in the output. Reason is their bank_number s are not matching although account_number s are matching.

Current_date Account_number   Arrears_Band      balance     bank_number   these are heading of the output table

28FEB2010	111	NPNA	10	100
31MAR2010	111	Current	20	100
31MAY2010	111	30 - 60	30	100
31AUG2010	111	writoff	50	100
31AUG2010	111	writoff	50	200
28FEB2010	444	Current	40	900
31MAR2010	444	30 - 60	50	900
30APR2010	444	30 - 60	60	900
31MAY2010	444	Current	70	900
30APR2010	555	Current	80	700
31MAY2010	555	NPNA	90	700
31AUG2011	555	90 +	12	700
30SEP2011	555	NPNA	0	700
30SEP2011	555	writoff	77	700
31MAY2010	666	Current	15	400
31AUG2011	666	90 +	20	400
30SEP2011	666	Current	30	400
30SEP2011	666	writoff	77	300

I would appreciate any help to generate the correct table.

Thank you

Mirisage

[Steelers_In_DC]

This gets the solution you are looking for, edited for length and rename:

data want;
length arrears_band $10.;
merge have_1 (in=a)
           have_2 (in=b rename=(post_date=Current_date Net_Write_off_total_USD=balance));
by account_number bank_number;
if missing(arrears_band) then Arrears_Band = 'Write Off';
run;