Identifying Defaulters

NTR — Thu, 08 Nov 2018 09:24:07 GMT

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

Re: Identifying Defaulters

RW9 — Thu, 08 Nov 2018 09:34:21 GMT

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;

Re: Identifying Defaulters

ballardw — Thu, 08 Nov 2018 15:31:00 GMT

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

Re: Identifying Defaulters

NTR — Mon, 12 Nov 2018 10:02:24 GMT

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

topic Re: Identifying Defaulters in SAS Programming

Identifying Defaulters

Re: Identifying Defaulters

Re: Identifying Defaulters

Re: Identifying Defaulters