## 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.

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

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

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

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

Discussion stats
• 3 replies
• 661 views
• 0 likes
• 4 in conversation