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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.