topic Re: Need to calculate rolling standard deviation in SAS Programming

Need to calculate rolling standard deviation

bd_user_10 — Fri, 13 Nov 2020 01:18:41 GMT

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.

Re: Need to calculate rolling standard deviation

Astounding — Fri, 13 Nov 2020 03:05:47 GMT

Many good answers already exist. Try googling:

sas.com:rolling standard deviation

Re: Need to calculate rolling standard deviation

bd_user_10 — Fri, 13 Nov 2020 08:19:22 GMT

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?

Re: Need to calculate rolling standard deviation

PeterClemmensen — Fri, 13 Nov 2020 08:27:42 GMT

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;

Re: Need to calculate rolling standard deviation

hhinohar — Wed, 18 Nov 2020 09:36:38 GMT

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;

Re: Need to calculate rolling standard deviation

bd_user_10 — Tue, 12 Jan 2021 16:03:39 GMT

Thank you very much for the codes.