In this code there are 2 pointers on the data: the current and the 30 day-ahead. This way you can maintain a moving sum, and output when you read from the current pointer.
Probably you want to delete obs where moving_cnt is too small (<=20). No problem if a day is missing.
proc sort data=have;
by Firm Date;
run;
%let timeWindow=30;/*day*/
%let timePeriod=day;
data have_exp / view=have_exp;
set have;
date=intnx("&timePeriod.",Date,-&timeWindow.,'s');
run;
data want;
set have(in=inT) have_exp ;/*interleaving original and expiry datasets*/
by Firm Date;
if first.Firm then do;/*initializing counters and sums at beginning of a group*/
moving_cnt=0;
moving_sum=.;
moving_Ret=1;retain moving_Ret;
end;
if inT then do;/*if transaction, then first output than calculate*/
output;
moving_cnt+(-1);
moving_sum+(-Ret);
moving_Ret=moving_Ret/(1+Ret);
end;
else do;/*if expiry, then calculate*/
moving_cnt+1;
moving_sum+Ret;
moving_Ret=moving_Ret*(1+Ret);
end;
run;
Hello all, Thank you very much for all of your help. I was able to get this to work, finally!
and , thank you very much, and I was able to use a combination of what you gave to me to make this work.
By the way, I am using SAS 9.2.
@muthia, I am going to email you privately about understanding arrays, as that is something that I really need to understand better!
Thanks so much!
John
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.