So you want the rolling 30 day minimum of the actual DOV value? Normally you would do this sort of rolling calculation of some other variable.
The best solution is to use SAS/ETS procedures. So check if the those licensed.
Otherwise if the data is smallish then doing a self join in the PROC SQL is the clearest. Just left join the table with itself and use your DOV window in the ON condition.
This will work for your test data (renamed Result to EXPECT so we can make sure it worked).
data have;
input ID $ DoV expect;
cards;
a 1 1
a 20 1
a 25 1
a 40 20
a 52 25
b 3 3
b 80 80
;
proc sql;
create table want as
select a.*,min(b.dov) as Result
from have a
left join have b
on a.id=b.id and b.dov between a.dov-30 and a.dov
group by a.id,a.dov,a.expect
;
quit;
If the data is too large then you can use an array of 30 elements to keep track of the values for the last 30 days. You can use MOD() function to simulate a "wrap around" array. Make sure the empty out the array elements for the skipped days.
data want;
do until (last.id);
set have;
by id dov ;
array rolling[0:29] rolling0-rolling29 ;
if not first.id then do index=lag_dov+1 to dov-1 ;
rolling[mod(index,30)]=.;
end;
rolling[mod(dov,30)]=dov;
Result=min(of rolling[*]);
output;
lag_dov=dov;
end;
drop index lag_dov rolling0-rolling29;
run;
... View more
