Help using Base SAS procedures

How to create a rolling counter?

Occasional Contributor
Posts: 7

How to create a rolling counter?

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.

Respected Advisor
Posts: 3,167

Re: How to create a rolling counter?

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

Super Contributor
Posts: 334

Re: How to create a rolling counter?

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 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;


          else do;

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

                     counter + 1;         

                      date_tmp = date;


                     else do;

                         counter = 1;

                          date_tmp = date;





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.


Super Contributor
Posts: 578

Re: How to create a rolling counter?

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




     have t

     left outer join have t1

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

     left outer join have t2

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



     and t1.delq=0

     and is null

     and is null



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


Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation