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.
Some sample data along with your expected output will for sure be helpful to facilitate the answer.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.