how to get the vintage numbers for buckets.

[BaalaRaaji]

Hi Team,

I am working on a adhoc which needs to get the count of the months that they have been in arreas for 7(Bucket 7).

Below is the sample data and the output i need.

appreciate the help and thoughts as usual.

accno	month_end_dt	arreas
101	31-Aug-15	7
101	30-Jul-16	7
101	31-Dec-17	0
101	30-Mar-18	2
101	31-Oct-18	7
201	31-Aug-15	7
201	30-Jul-16	7
201	31-Dec-17	7
201	30-Mar-18	7
201	31-Oct-18	7
301	31-Dec-15	7
301	31-Jan-16	7
301	28-Feb-16	0
301	31-Mar-16	1
301	31-Dec-16	3
301	31-Jan-17	5
301	28-Feb-17	6
301	31-Mar-17	0
301	31-Jan-18	3
301	31-Mar-18	3
301	30-Apr-18	7
301	30-Jun-18	7
301	31-Aug-18	7
301	31-Oct-18	7

output i need the count as below..

accno	month_end_dt	arreas	count
101	31-Oct-18	7	1
201	31-Oct-18	7	5
301	31-Oct-18	7	4

it shouls count only if it has repeated 7 contiously for previous months may be from 2015 data.

example if it has 777701234567777...then should count the number where it starts from 7...it should be 4 (not 8).

Appreciate the logic.let me know if its not clear.thanks in advance.

Regards,

RS

[Kurt_Bremser]

Shouldn't the count for 101 also be 2, as the first two observation have arreas = 7?

[BaalaRaaji]

No kurtBremser .. it should be 1 as am counting from last date (monthly snap shot for 31Oct2018)...77027 series...for 101.. counting only if it's 7 and has same 7 in previous month ..

[Kurt_Bremser]

Then this should do it:

data have;
infile cards dlm='09'x;
input accno :$3. month_end_dt :date9. arreas;
format month_end_dt yymmddd10.;
cards;
101	31-Aug-15	7
101	30-Jul-16	7
101	31-Dec-17	0
101	30-Mar-18	2
101	31-Oct-18	7
201	31-Aug-15	7
201	30-Jul-16	7
201	31-Dec-17	7
201	30-Mar-18	7
201	31-Oct-18	7
301	31-Dec-15	7
301	31-Jan-16	7
301	28-Feb-16	0
301	31-Mar-16	1
301	31-Dec-16	3
301	31-Jan-17	5
301	28-Feb-17	6
301	31-Mar-17	0
301	31-Jan-18	3
301	31-Mar-18	3
301	30-Apr-18	7
301	30-Jun-18	7
301	31-Aug-18	7
301	31-Oct-18	7
;
run;

proc sort data=have;
by accno descending month_end_dt;
run;

data want (keep=accno month_end_dt count);
set have (rename=(month_end_dt=_month_end_dt));
by accno;
retain count flag month_end_dt;
format month_end_dt yymmddd10.;
if first.accno
then do;
  count = 0;
  flag = 1;
  month_end_dt = _month_end_dt;
end;
if arreas = 7 then count + 1;
if (arreas ne 7 or last.accno) and flag
then do;
  output;
  flag = 0;
end;
run;

proc print data=want noobs;
var accno month_end_dt count;
run;

Result:

             month_
accno        end_dt    count

 101     2018-10-31      1  
 201     2018-10-31      5  
 301     2018-10-31      4