good day all;
I would really appreciate it if someone can assist me. I have a payment file where one contract has multiple entries. A payment can be made and reverse. I'm struggling to put a code together in order for me to identify defaulters.
Sample Data:
account number Amount Date Type
92865 $-200.00 24APR2018:00:00:00.000 REC
92865 $200.00 24APR2018:00:00:00.000 REV_REC
92865 $-200.00 19MAY2018:00:00:00.000 REC
92865 $-177.20 10JUN2018:00:00:00.000 REC
92865 $-400.00 08JUL2018:00:00:00.000 REC
92865 $-200.00 05AUG2018:00:00:00.000 REC
92865 $200.00 19SEP2018:00:00:00.000 REV_REC
92865 $-200.00 19SEP2018:00:00:00.000 REC
92865 $-200.00 18OCT2018:00:00:00.000 REC
100668 $-150.98 25APR2018:00:00:00.000 REC
100668 $-150.18 24MAY2018:00:00:00.000 REC
100668 $-150.98 29JUN2018:00:00:00.000 REC
100668 $-150.18 25JUL2006:00:00:00.000 REC
100668 $-150.18 06AUG2005:00:00:00.000 REC
100668 $-150.98 31SEP2009:00:00:00.000 REC
100668 $-150.18 04OCT2018:00:00:00.000 REC
100668 $150.18 04OCTG2018:00:00:00.000 REV_REC
Anticipated outcome is:
Account number Month Default Flag
92865 Apr 1
92865 May 0
92865 Jun 0
92865 Jul 0
92865 Aug 0
92865 Sep 1
92865 Oct 0
100668 Apr 0
100668 May 0
100668 Jun 0
100668 Jul 0
100668 Aug 0
100668 Sep 0
100668 Oct 1
Code your rec/rec_rev as 0 and 1 respectively, also create a month variable to group by, then proc means sum by month. Am not typing test data out for you - post test data in the form of a datastep in future. A simple example might look like:
data inter; set have; month=month(date); val=ifn(type="REC",0,1); run; proc means data=inter; by account_number month; var val; output out=want sum=sum; run;
Or you could do it in sql as:
proc sql; create table want as select account_number, month(date) as month, sum(case when type="REC" then 0 else 1 end) as val from have group by account_number, month(date); quit;
It also helps to describe the rule(s) used to determine what a "defaulter" may be.
Defaulter is an account where there was a receipt (REC) and reversal(REV REC) on the same day.
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.