How to create a rolling counter?

[otis]

I need help to create a rolling counter. For example, I have 12 months of data (each month is one record), and want to find accounts that have no delinquency three months in a row. I know 'array' or 'do loop' can make it happen, but could not figure it out myself. Appreciate any help.

[Haikuo]

Some sample data along with your expected output will for sure be helpful to facilitate the answer.

[esjackso]

As said without more information specific code will not be possible. Unless I am missing the issue here I dont think an array or do loop would be what I used. Do processing would be used however.

Logic steps I would take:

1.I would sort by acct date

2.at the first of every acct I would set the counter to 1 and store the date value to date_tmp (retain this variable)

3.else I would check intck('month',date,date_tmp)=1 then do counter + 1;

          else counter = 1;

proc sort data=yourdata; by acct date; run;

data newdata;

     set yourdata;

     by acct date;

     retain date_tmp counter;

     if first.acct then do;

          counter = 1;

          date_tmp = date;

     end;

          else do;

               if intck('month',date,date_tmp)=1 then do;

                     counter + 1;         

                      date_tmp = date;

               end;

                     else do;

                         counter = 1;

                          date_tmp = date;

                      end;

          end;

run;

The syntax would depend a lot on the structure of your data, but perhaps the logic above can help you along. The code above isnt tested and may not be complete but perhaps provides a starting point.

EJ

[DBailey]

If you have data like this:

ID      Dt     Delq

1     31Jan2013     0

1     28Feb2013     0

1     31mar2013     0

1     30Apr2013     1

2     31Jan2013     0

2     28Feb2013     0

2     31Mar2013     0

2     30Apr2013     0

Then you could do this:

proc sql;

create table no_delq_in_3_months as

select

     t.*

from

     have t

     left outer join have t1

          on t.id=t1.id and intnx('month',t.dt,-1,'end')=t1.dt and t1.delq>0

     left outer join have t2

          on t.id=t2.id and intnx('month',t.dt,-2,'end')=t2.dt and t2.delq>0

where

     t1.dt>='31Mar2013'd

     and t1.delq=0

     and t2.id is null

     and t3.id is null

;

quit;

Depending on size, the datasets might need sorting or indexing.

db