Consecutive Months Data Counting in SAS

Question

I have a typical banking data and need some help.

There are 3 columns: Account ID, Month Key(yyyymm format) and Payment Type. Payment Type can take values IO,IOA,PIF,PI,P,NFD,Null.

I have around 250,000 accounts and objective is to find such accounts that have Payment_Type in ("IO","IOA") for consecutive 60+ months. Discontinuous 60 months in IO is not my objective.

data have;
   length Account_ID $2.
          Month_Key 8.
          Payment_Type $3.
   ;
   
   format Month_Key date9.;

   input Account_ID$ Month_Key:yymmn. Payment_Type$;
   datalines;
   A1 201001 
   A1 201002 IO
   A1 201003 PIF
   A1 201004 PI
   A1 201005 P
   A1 201006  
   A1 201007 IOA
   A1 201008 IO
   A1 201009 IOA
   A1 201010 IOA
   A1 201011 IO
   A1 201012 IO
   A1 201101 IO
   A1 201102 IO
   A1 201103 IO
   A1 201104 IO
   A1 201105 IO
   A1 201106 IO
   A1 201107 IO
   A1 201108 IO
   A1 201109 IO
   A1 201110 IO
   A1 201111 IO
   A1 201112 IO
   A1 201201 IO
   A1 201202 IO
   A1 201203 IO
   A1 201204 IO
   A1 201205 IO
   A1 201206 IO
   A1 201207 IO
   A1 201208 IO
   A1 201209 IO
   A1 201210 IO
   A1 201211 IO
   A1 201212 IO
   A1 201301 IO
   A1 201302 IO
   A1 201303 IO
   A1 201304 IO
   A1 201305 IO
   A1 201306 IO
   A1 201307 IO
   A1 201308 IO
   A1 201309 IO
   A1 201310 IO
   A1 201311 IO
   A1 201312 IO
   A1 201401 IO
   A1 201402 IO
   A1 201403 IO
   A1 201404 IO
   A1 201405 IO
   A1 201406 IO
   A1 201407 IO
   A1 201408 IO
   A1 201409 IO
   A1 201410 IO
   A1 201411 IO
   A1 201412 IO
   A1 201501 IO
   A1 201502 IO
   A1 201503 IO
   A1 201504 IO
   A1 201505 IO
   A1 201506 IO
   A1 201507 IO
   A1 201508 IO
   A1 201509 PIF
   A1 201510 PIF
   A1 201511 PIF
   A1 201512 PIF
   A1 201601 PIF
   A1 201602 PIF
   A1 201603 PIF
;
run;

This account is in IO for a period of 62 consecutive months starting from 201007 ending at 201508.

My final output should have Account ID, and an indicator stating whether account is in IO > 60+ Months. Better to create an indicator with value 1 such as below if 60+ months in IO, else 0.

Account_ID IO_GT_60_Mths_Ind

A1         1

Can someone please help me. Appreciate!!

user_5342700 · Answer

Welcome to Stack Overflow! You can accomplish this using a data step, by-group processing, and the sum statement. The below code will increment n by 1. We will reset the counter if:

We reach a new account
The payment type is not IO or IOA
The number of months between the current month and previous month is > 1

Code:

proc sort data=have;
    by account_id month_key;
run;

data want;
    set have;
    by account_id month_key;
    
    lag_month = lag(month_key);
    
    if(first.account_id) then call missing(lag_month);

    if(   first.account_id 
       OR intck('month', lag_month, month_key) > 1
       OR payment_type NOT IN('IO', 'IOA') 
      ) 
    then n = 0;

    n+1;

    IO_GT_60_Months_Ind = (n GE 60);

    format lag_month date9.;
run;

Your question is clear, but normally we would ask for sample code and what you have tried. Please be sure to format your data in datalines or a downloadable csv, and post your attempt the next time.

user_4044936 · Answer

My 2 cents

data want(keep=Account_ID IO_GT_60_Mths_Ind);
    IO_GT_60_Mths_Ind = 0;
    do until (last.Account_ID);
        set have;
        by Account_ID notsorted;
        c = ifn(Payment_Type in ("IO", "IOA"), c+1, 0);
        if c = 60 then IO_GT_60_Mths_Ind = 1;
    end;
    output;
run;

Consecutive Months Data Counting in SAS

2 Answers