Identifying Defaulters

[NTR]

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

[RW9]

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;

[ballardw]

It also helps to describe the rule(s) used to determine what a "defaulter" may be.

[NTR]

Defaulter is an account where there was a receipt (REC) and reversal(REV REC) on the same day.