topic Re: Standard deviation on rolling basis in SAS Programming

Standard deviation on rolling basis

AbuChowdhury — Sat, 17 Mar 2018 20:20:50 GMT

Hi Experts,

I have daily returns data for firms. I would like to calculate the standard deviation of the returns (say, sigma) from the daily observations for the last 3 months on a rolling basis. For example, sigma for for firm i in december 2017 will be the standard deviations of the daily returns from september to november 2017. Similarly, sigma for firm i in november 2017 will be the standard deviations of the daily returns from august to october 2017.

Thanks,

Abu

Re: Standard deviation on rolling basis

Reeza — Sat, 17 Mar 2018 22:22:44 GMT

Can you post some sample data? Have you tried anything so far as well? PROC EXPAND is probably where I'd start, but I'd also consider changing your definition. If you use the previous 90 days for example or X trading days. 3 months is not a standard measure and you'll see blips because of February issue alone.

Re: Standard deviation on rolling basis

Ksharp — Sun, 18 Mar 2018 10:29:50 GMT


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;

Re: Standard deviation on rolling basis

AbuChowdhury — Sun, 18 Mar 2018 14:38:21 GMT

Thank you Xia. But proc sql takes very long time if there are millions of observations. Is there any other way such as using array or using macro?

Re: Standard deviation on rolling basis

PaigeMiller — Sun, 18 Mar 2018 16:16:25 GMT

Yes, of course calculations of this nature are going to take a very long time if you have millions of observations. If speed doing the calculation on millions of observations is a concern, you should have stated this at the beginning.

Please read what Reeza said and show us some sample data, and show us the desired results.

Re: Standard deviation on rolling basis

AbuChowdhury — Sun, 18 Mar 2018 17:36:23 GMT

Ksharp's (Xia) code is ok but I would like to know if it's possible to do using macro or using array. I attach the sample dataset. Test file is attached since sas data file cannot be attached.

Re: Standard deviation on rolling basis

PaigeMiller — Sun, 18 Mar 2018 20:15:37 GMT

Yes, of course it would be possible to write this as a macro. I don't know if it would be faster on millions of observations because I never tried. As @Reeza has stated, PROC EXPAND might also be a good solution.

Then, there is this solution, which works for regression coefficients with PROC REG, but something similar using PROC SUMMARY ought to work for standard deviations https://communities.sas.com/t5/SAS-Procedures/How-to-capture-the-regression-coeffecient-for-certain-time/m-p/445977#M69436

(and this could be customized to handle the different number of days in each month)

Re: Standard deviation on rolling basis

Reeza — Sun, 18 Mar 2018 20:13:07 GMT

If it was a fixed interval a different approach would be easier but since the number of changes each month depending on the month that's the biggest problem.

You could probably expand the temporary array approach used here, but PROC EXPAND is still your best option. Have you looked into that at all? It would likely also be the fastest option as SAS would have optimized it already.

http://support.sas.com/kb/41/380.html

You would need to set the array size to the largest possible number of records, probably 93 to be safe.

Re: Standard deviation on rolling basis

Ksharp — Mon, 19 Mar 2018 14:36:07 GMT

I think Hash Table could do that. Post some data ,maybe I could give a try.

Re: Standard deviation on rolling basis

AbuChowdhury — Mon, 19 Mar 2018 16:51:51 GMT

Hi Xia,

Sample data is attached in text format. Please try with hash object.

Re: Standard deviation on rolling basis

Ksharp — Tue, 20 Mar 2018 13:38:12 GMT

OK. Try this one. Notice: each PERMNO and NAMES_DATE have only one Return.

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 want;
 if _n_=1 then do;
   if 0 then set have(rename=(ret=_ret));
   declare hash h();
   h.definekey('Names_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{*});
 do i=intnx('month',monyy,-3) to intnx('month',monyy,-1,'e');
   if h.find(key:i)=0 then do;n+1;x{n}=_ret; end;
 end;
 std=std(of x{*});
 output;
end;

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

Re: Standard deviation on rolling basis

AbuChowdhury — Tue, 20 Mar 2018 18:27:18 GMT

It's lot faster now. It also shows the result i.e. the standard deviation. But it shows the following error many times:

ERROR: Duplicate key.

Re: Standard deviation on rolling basis

Ksharp — Wed, 21 Mar 2018 12:21:10 GMT

That means there are multiply return for the same PERMNO and NAMES_DATE .

Make sure a PERMNO and a NAMES_DATE have only one return.

To sum(return) in each PERMNO and NAMES_DATE combination.

After that running code would not generate that error information.

proc summary data=have;

by PERMNO NAMES_DATE ;

var return;

output out=want sum=;

run;

OR you want the last return for each group of PERMNO and NAMES_DATE.

data want;

set have;

by PERMNO NAMES_DATE ;

if last.NAMES_DATE ;

run;

Re: Standard deviation on rolling basis

Ksharp — Wed, 21 Mar 2018 12:57:29 GMT

OK. If you want retain those multiple returns in the same PERMNO and the same Names_Date.

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 want;
 if _n_=1 then do;
   if 0 then set have(rename=(ret=_ret));
   declare hash h(multidata:'y');
   h.definekey('Names_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{*});
 do i=intnx('month',monyy,-3) 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{*});
 output;
end;

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

Re: Standard deviation on rolling basis

AbuChowdhury — Wed, 21 Mar 2018 16:21:38 GMT

Thanks a lot Xia.

Re: Standard deviation on rolling basis

Ksharp — Sat, 31 Mar 2018 10:44:49 GMT

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;

Re: Standard deviation on rolling basis

AbuChowdhury — Sat, 31 Mar 2018 13:07:09 GMT

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;

Re: Standard deviation on rolling basis

Ksharp — Sun, 01 Apr 2018 10:35:09 GMT

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;

Re: Standard deviation on rolling basis

Nieves — Mon, 20 Aug 2018 05:46:37 GMT

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;

Re: Standard deviation on rolling basis

Ksharp — Tue, 21 Aug 2018 10:29:05 GMT

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;