Hi Everyone,
I need to calculate the rolling standard deviation of daily stock returns over one year prior to the trading day. I have been using the following codes but not sure whether it's working correctly.
proc sql;
create table want as
select *,(select std(stk_return) from daily_stock_return where date between a.date-252 and a.date-1 and isin=a.isin) as rolling_std
from daily_stock_return as a;
quit;
I have assumed 252 working days in a year although it could be different in different years! May be there is a better way to deal with this. Any help would be appreciated. Please find the attached sample data. Thanks.
Below is a sample program tested using provided dataset.
I added year variable to original dataset.
data work.daily_stock_return;
set daily_stock_return;
*added year variable;
year=year(date);
run;
data want;
do _N_=1 by 1 until(last.year);
set work.daily_stock_return;
by isin year notsorted;
array days[999] _temporary_;
days[_N_]=stk_return;
rolling_std=std(of days[*]);
if last.year then do i=1 to dim(days);
days[i]=.;
end;
output;
end;
drop i year;
run;
Many good answers already exist. Try googling:
sas.com:rolling standard deviation
Thanks for the advice!
However, these codes creating the rolling standard deviation from day 2. How do I restrict it to have all 252 days observations to calculate the rolling standard deviation?
See if you can use this as a template
proc sql;
create table want as
select *,
(select std(close) from sashelp.stocks
where stock=a.stock
and (intnx('month', a.Date, -12, 's') le Date le a.Date))
as std format=8.2
from sashelp.stocks as a;
quit;
Below is a sample program tested using provided dataset.
I added year variable to original dataset.
data work.daily_stock_return;
set daily_stock_return;
*added year variable;
year=year(date);
run;
data want;
do _N_=1 by 1 until(last.year);
set work.daily_stock_return;
by isin year notsorted;
array days[999] _temporary_;
days[_N_]=stk_return;
rolling_std=std(of days[*]);
if last.year then do i=1 to dim(days);
days[i]=.;
end;
output;
end;
drop i year;
run;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.