Calculate standard deviation on rolling basis

[ADou]

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

[PGStats]

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

[ADou]

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!

[learsaas]

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;

[ADou]

Thanks a lot!

ADou

[ADou]

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?

[PGStats]

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;

[ADou]

Thank you! 

ADou

[Ksharp]

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;

[ADou]

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?

[Ksharp]

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;