topic Re: How to create a rolling counter? in SAS Procedures

How to create a rolling counter?

otis — Mon, 26 Aug 2013 23:17:51 GMT

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.

Re: How to create a rolling counter?

Haikuo — Mon, 26 Aug 2013 23:26:19 GMT

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

Re: How to create a rolling counter?

esjackso — Tue, 27 Aug 2013 12:45:15 GMT

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;

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.

Re: How to create a rolling counter?

DBailey — Tue, 27 Aug 2013 15:28:52 GMT

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.