Guys, I truly appreciate both of your help in getting me the code for this. Actually, we just need to check if the Account hits 61 months in IO consecutively and once it does that, we should not be bothered about what happens after that. After 61 consecutive months in IO, same account can have PIF for 5 months, PI for 3 months and then again IO, any pattern really. But I am not bothered by any of this, if the account has already hit 61 consecutive months in IO. Moment this happens, game over and we should flag this to 1. The modified code was working and even your code was also working but in the output, it was showing me all the rows, not just one row per account. I might have done some mistake there. I then tried to code this on my own from the first principles. What we need is a counter that does the following. 1. Counter set to 1 for the first record for each account. 2. If difference between current month key and previous month key is greater than 1, then counter is again set to 1, OR 3. If current month's payment type is NOT IN ("IO","IOA") the counter is reset to 1, OR 4. If current month's payment type is IN ("IO","IOA") and previous month's payment type is not in ("IO","IOA") then counter is set to 1. This is because if it goes to IO the first time, I need that to be reflected as counter = 1, not counter = 2. 5. If any of the above 4 conditions are not satisfied, then we increment the counter by 1 Immediately output Indicator =1 as soon as counter = 61 and that's it, happy days!! Below is the code that I used. The number of accounts where Indicator = 1 was matching with the code you guys shared with me, hence I got confidence the code that I wrote below is also doing the same thing. data want; set have; by account_id month_key; lag_month = lag(month_key); lag_payment_type = lag(payment_type); 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') OR (payment_type IN('IO', 'IOA') and lag_payment_type NOT IN ('IO','IOA')) ) then n = 0; n+1; IO_GT_60_Months_Ind = (n GT 60); format lag_month date9.; run;
... View more