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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.