Standard deviation on rolling basis

[Ksharp]

OK. If there was no ID column ,and want t-1 to t-12 rolling std.

data want;
 if _n_=1 then do;
   if 0 then set have(rename=(ret=_ret));
   declare hash h(dataset:'have(rename=(ret=_ret))',multidata:'y');
   h.definekey('Names_Date');
   h.definedata('_ret');
   h.definedone();
 end;

array x{1000} _temporary_;
set have;

 n=0;call missing(of x{*});
 do i=intnx('month',monyy,-12) to intnx('month',monyy,-1,'e');
   rc=h.find(key:i);
   do while(rc=0);
     n+1;x{n}=_ret;
     rc=h.find_next(key:i);
   end;
 end;
 std=std(of x{*});

drop i _ret n rc;
run;

[AbuChowdhury]

This code shows the following error message:

ERROR: Array subscript out of range at line 125 column 10.

This was at line 125:

n+1;x{n}=_ret;

[Ksharp]

Sorry. This code is not for you . someone sent me a private message with this topic link.

He or She want get the similar result with only one PERMNO . So I post it here. If that was not you ,ignore it .

proc import datafile='c:\temp\daily_ret.txt' out=have dbms=tab replace;
guessingrows=32767;
run;
data have ;
 set have;
 ret=input(Returns,?? best32.);
 drop Returns;
 monyy=intnx('month',Names_Date,0);
 format monyy yymmn6.;
run;
proc sort data=have;by PERMNO monyy Names_Date;run;






data have;
 set have;
 if permno=10008;
run;


data want;
 if _n_=1 then do;
   if 0 then set have(rename=(ret=_ret));
   declare hash h(dataset:'have(rename=(ret=_ret))',multidata:'y');
   h.definekey('Names_Date');
   h.definedata('_ret');
   h.definedone();
 end;

array x{1000} _temporary_;
set have;

 n=0;call missing(of x{*});
 do i=intnx('month',monyy,-12) to intnx('month',monyy,-1,'e');
   rc=h.find(key:i);
   do while(rc=0);
     n+1;x{n}=_ret;
     rc=h.find_next(key:i);
   end;
 end;
 std=std(of x{*});

drop i _ret n rc;
run;

[Nieves]

Hi Ksharp, 

Thank you very much for your answer. I am calculating the similar sigma ratio here, except that I require that if there are fewer than five nonzero observations over the 3 months used in the rolling window computation, SIGMA will be counted as missing and replace with the cross-sectional mean of SIGMA. May I ask how to add a line in your following code to implement ? 

Thanks 

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

[Ksharp]

How do you calculate this cross-sectional mean of SIGMA ?

And I suggest you start a new session ,this post is almost one year old .

The following could give you a start.

proc sql;
create table want as
 select *,
case when count(return) <5 then .
else
(select std(return) from have where id=a.id and date between intnx('month',a.date,-3) and a.date )
end as std
   from have as a;
quit;

data final_want;
set want;
"replace missing with cross-sectional mean of SIGMA"
............
run;

[Ramin1]

I am going to calculate sigma following the paper Campbell et al. (2008), where he defines sigma as
Sigma_t-1,t-2,t-3=SQRT(252*(1/N-1) sum(r^2_i,k))
Where r^2 is the firm-level daily return and N is the number of trading days in three months period. K is the index of trading days in months t-1, t-2, t-3. They require a firm have at least 5 nonzero daily observations. In this case, they report sigma as missing and replace it with the annual cross-sectional mean.

I have the attached code, it seems it is modified from your code in this question (I do not have much idea of such hash function), can you look into this and tell me whether the 3 months rolling volatility/standard deviation calculated perfectly or not and how can I annualize it? I am attaching the code for you. If you think there is any other efficient way or the code need modification please post it. Thanks in advance.
The link of the paper which I am following
https://scholar.harvard.edu/files/campbell/files/campbellhilscherszilagyi_jf2008.pdf (page 2936)

data crsp;
set work.daily_return;
format date mmddyy10.;
month=month(date);
keep permno date month ret;
run;

data have ;
set crsp;
ret=input(ret,?? best32.);
drop Returns;
monyy=intnx('month',Date,0);
format monyy yymmn6.;
run;

proc sort data=have;
by PERMNO monyy Date;
run;

data want;
if _n_=1 then do;
if 0 then set have(rename=(ret=_ret));
declare hash h(multidata:'y');
h.definekey('Date');
h.definedata('_ret');
h.definedone();
end;

array x{100} _temporary_;
do until(last.PERMNO);
set have;
by PERMNO;
_ret=ret;h.add();
end;

do until(last.PERMNO);
set have;
by PERMNO;
n=0;call missing(of x{*});
m.clear(); /**<---***/
do i=intnx('month',monyy,-3) to intnx('month',monyy,-1,'e');
rc=h.find(key:i);
do while(rc=0);
if not missing(_RET) then do;n+1;x{n}=_ret; _monyy=intnx('month',i,0); m.replace(); end; /**<---***/
rc=h.find_next(key:i);
end;
end;
std=std(of x{*});
if n<5 then std=.; /**<-----****/
if m.num_items<3 then std=.; /**<----EDIT HERE-****/
output;
end;

h.clear();
drop i _ret n rc;
run;

data trim;
set want;
date2=intnx('month',date,1)-1;
date1=intnx('month',date2,0);
date_of=intnx('month',date1,-3);
year=year(date);
format date1 date2 date_of date9.;
drop date1 monyy _monyy ret;
run;

proc sort data=trim;
by permno month year;
run;

data monthly;
set trim;
by permno month year;
firstpermno=first.permno;
lastpermno=last.permno;
firstyear=first.year;
lastyear=last.year;
*firstmonth=first.month;
*lastmonth=last.month;
if lastyear=1;
run;

proc sort data=monthly;
by permno date;
run;

data sigma;
set monthly;
if month=1 then date=mdy(month,31,year(date));
if month=2 and year(date)in ('2000','2004','2008','2012','2016') then date=mdy(2,29,year(date));
else if month=2 and year(date) not in ('2000','2004','2008','2012','2016') then date=mdy(2,28,year(date));
if month=3 then date=mdy(month,31,year(date));
if month=4 then date=mdy(month,30,year(date));
if month=5 then date=mdy(month,31,year(date));
if month=6 then date=mdy(month,30,year(date));
if month=7 then date=mdy(month,31,year(date));
if month=8 then date=mdy(month,31,year(date));
if month=9 then date=mdy(month,30,year(date));
if month=10 then date=mdy(month,31,year(date));
if month=11 then date=mdy(month,30,year(date));
if month=12 then date=mdy(month,31,year(date));
drop firstpermno lastpermno lastyear firstyear;
by permno date;
run;

/* Annualizing Sigma */

Data Sigma2;
Set Sigma;
Sigma_Annualized= sqrt(252/(64-1))*three months rolling volatility;
run;

[Ksharp]

Sorry . I have no time to read the paper . And could you start a brand-new topic . let more people to see your question and might get more help .
P.S. don't forget post the sample data and result you want to see .