topic Re: Calculate standard deviation on rolling basis in SAS Programming

Calculate standard deviation on rolling basis

ADou — Thu, 27 Dec 2018 22:37:11 GMT

Hi Experts,

I'd like to calculate the 60-month standard deviation(sigma) and I have the monthly returns for each company. Now I want to compute the standard deviations for the lat 60-month observations on the rolling bases. For example, I want the standard deviation of stock returns from 1987-02 to 1992-01 to be the sigma of 1992-01. Do you know any good solutions?

Thanks a lot!

ADou

Re: Calculate standard deviation on rolling basis

PGStats — Thu, 27 Dec 2018 22:47:07 GMT

proc expand (part of SAS/ETS) would be a very good solution. It has plenty of moving window operations, including the STD.

Re: Calculate standard deviation on rolling basis

ADou — Thu, 27 Dec 2018 23:02:17 GMT

Thanks a lot! But I'm not very familiar with proc expand, could you please give me some example codes? And I've attached a printscreen of mt data.

Thank you!

Re: Calculate standard deviation on rolling basis

learsaas — Fri, 28 Dec 2018 02:44:59 GMT

proc sort data=a;
	by PERMNO Date;
run;
proc expand data=a out=result(drop=time);
	by PERMNO;
	convert Returns = Returns_movstd / transformout=(movstd 60*30);
run;

Re: Calculate standard deviation on rolling basis

PGStats — Fri, 28 Dec 2018 05:28:08 GMT

If you don't specify a time variable (with the ID statement) proc expand assumes 1 row = 1 time unit. So you can simply say:

proc sort data=a;
	by PERMNO Date;
run;

proc expand data=a out=result;
	by PERMNO;
	convert Returns = Returns_std / transformout=(movstd 60);
run;

Re: Calculate standard deviation on rolling basis

ADou — Sat, 29 Dec 2018 22:10:29 GMT

Thanks a lot!

ADou

Re: Calculate standard deviation on rolling basis

ADou — Sat, 29 Dec 2018 22:11:08 GMT

Thank you!

ADou

Re: Calculate standard deviation on rolling basis

Ksharp — Sun, 30 Dec 2018 11:19:04 GMT

CODE NOT TESTED .

proc sql;
create table have as
 select *,
(select std(return) from have where permno=a.permno and
 date between intnx('month',a.date,-60) and a.date) 
 as rolling_std
  from have as a;
quit;

Re: Calculate standard deviation on rolling basis

ADou — Tue, 01 Jan 2019 20:21:25 GMT

Thanks for your example and it works well. But I still have a question. Since I have to delete the companies that have returns less than 12 months, so I need the number of standard deviation of each company in one rolling(60 months). It is something like nrolling_std. How should I modify the codes?

Re: Calculate standard deviation on rolling basis

ADou — Tue, 01 Jan 2019 20:25:30 GMT

Thanks KSharp! It takes a long time to run the sql. And I still have a question. Since I have to delete the companies that have returns less than 12 months, so I need the number of standard deviation of each company in one rolling(60 months). It is something like nrolling_std. How should I modify the codes?

Re: Calculate standard deviation on rolling basis

Ksharp — Wed, 02 Jan 2019 12:21:12 GMT

OK.just add one more clause.

proc sql;
create table have as
 select *,
(select std(return) from have where permno=a.permno and
 date between intnx('month',a.date,-60) and a.date) 
 as rolling_std,
(select count(*) from have where permno=a.permno and
 date between intnx('month',a.date,-60) and a.date) 
 as n_rolling_std
  from have as a;
quit;