How to merge two tables while giving variable values-II

Mirisage — Sat, 15 Sep 2012 21:57:01 GMT

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

Re: How to merge two tables while giving variable values-II

Steelers_In_DC — Thu, 10 Sep 2015 15:30:12 GMT

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;

topic How to merge two tables while giving variable values-II in SAS Programming

How to merge two tables while giving variable values-II

Re: How to merge two tables while giving variable values-II