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 ;
Format Current_date date9.;
cards;
28FEB2010 111 NPNA 10
31MAR2010 111 Current 20
31MAY2010 111 30 - 60 30
28FEB2010 444 Current 40
31MAR2010 444 30 - 60 50
30APR2010 444 30 - 60 60
31MAY2010 444 Current 70
30APR2010 555 Current 80
31MAY2010 555 NPNA 90
31AUG2011 555 90 + 12
30SEP2011 555 NPNA 0
31MAY2010 666 Current 15
31AUG2011 666 90 + 20
30SEP2011 666 Current 30
;
run;
data Have_2;
informat Post_date date9.;
Input Post_date Account_number 12-14 Net_Write_off_total_USD;
Format Post_date date9.;
cards;
31AUG2010 111 50
30SEP2011 555 77
;
run;
Question:
I need to join these 2 tables so that I could get the answer below.
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.
28FEB2010 111 NPNA 10
31MAR2010 111 Current 20
31MAY2010 111 30 - 60 30
31AUG2010 111 writoff 50
28FEB2010 444 Current 40
31MAR2010 444 30 - 60 50
30APR2010 444 30 - 60 60
31MAY2010 444 Current 70
30APR2010 555 Current 80
31MAY2010 555 NPNA 90
31AUG2011 555 90 + 12
30SEP2011 555 NPNA 0
30SEP2011 555 writoff 77
31MAY2010 666 Current 15
31AUG2011 666 90 + 20
30SEP2011 666 Current 30
*/
My approach:
/*Transform unique ID from numeric to character*/
data have_2;
set have_2;
char_account_number = put(account_number, $7.) ;
drop account_number ;
rename char_account_number=account_number ;
run;
/*Carrying out a full outer join*/
proc sql;
select
a.* ,
b.*
from have_1 a full join
have_2 b
on a.Account_number=b.Account_number;
run;
Obviously this approach doesn’t give me what I want.
I really appreciate if someone could help me?
Thanks
Mirisage
You only want union these two datasets vertically by Account group?
data Have_1; informat Current_date date9.; Input Current_date Account_number $12-14 Arrears_Band $ 17-23 balance ; Format Current_date date9.; cards; 28FEB2010 111 NPNA 10 31MAR2010 111 Current 20 31MAY2010 111 30 - 60 30 28FEB2010 444 Current 40 31MAR2010 444 30 - 60 50 30APR2010 444 30 - 60 60 31MAY2010 444 Current 70 30APR2010 555 Current 80 31MAY2010 555 NPNA 90 31AUG2011 555 90 + 12 30SEP2011 555 NPNA 0 31MAY2010 666 Current 15 31AUG2011 666 90 + 20 30SEP2011 666 Current 30 ; run; data Have_2; informat Post_date date9.; Input Post_date Account_number $12-14 Net_Write_off_total_USD; Format Post_date date9.; cards; 31AUG2010 111 50 30SEP2011 555 77 ; run; 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;
Ksharp
You only want union these two datasets vertically by Account group?
data Have_1; informat Current_date date9.; Input Current_date Account_number $12-14 Arrears_Band $ 17-23 balance ; Format Current_date date9.; cards; 28FEB2010 111 NPNA 10 31MAR2010 111 Current 20 31MAY2010 111 30 - 60 30 28FEB2010 444 Current 40 31MAR2010 444 30 - 60 50 30APR2010 444 30 - 60 60 31MAY2010 444 Current 70 30APR2010 555 Current 80 31MAY2010 555 NPNA 90 31AUG2011 555 90 + 12 30SEP2011 555 NPNA 0 31MAY2010 666 Current 15 31AUG2011 666 90 + 20 30SEP2011 666 Current 30 ; run; data Have_2; informat Post_date date9.; Input Post_date Account_number $12-14 Net_Write_off_total_USD; Format Post_date date9.; cards; 31AUG2010 111 50 30SEP2011 555 77 ; run; 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;
Ksharp
Same suggestion as KSharp but, if your data is already in account_number date order, you can avoid the extra sort by including date as a 2nd by variable. i.e.:
data want;
set have_1 have_2(in=inb rename=(Post_date=Current_date Net_Write_off_total_USD=balance)) ;
by Account_number Current_date;
if inb then Arrears_Band='writoff ';
run;
Hi Ksharp and Art,
Splendid! Work well. A lot of things to learn.
Thanks again!
Mirisage
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: