topic Re: Rolling 24 month window-compute stdev of returns in SAS Programming

Rolling 24 month window-compute stdev of returns

LanMin — Sun, 02 Jun 2013 17:50:05 GMT

Dear all,

my sample data as well as a code someone else wrote to compute stdev of returns over rolling 10year window are attached. I ask for your help to compute stdev of returns over rolling 24 months window. For example, for year 1985 , I want to use the past two years (1983-84)24 monthly returns and compute a standard deviation, output to year 1985.

I tried to modify his code, but not sure how to deal with month and year.

Thanks for your time!

lan

Re: Rolling 24 month window-compute stdev of returns

PGStats — Sun, 02 Jun 2013 18:49:35 GMT

Don't touch that messy code. Start anew with:

data have(keep=cusip ret monthDate);
infile "&sasforum.\datasets\24sample.txt" dlm='09'x dsd firstobs=2;
input CUSIP RET Year month;
monthDate = mdy(month, 1, year);
run;

proc sql;
create table roll as
select

     h2.cusip,
     h2.monthDate as periodEndDate format=yymmd7.,
     h2.ret,
     mean(h1.ret) as meanRet,
     std(h1.ret) as stdRet
from
     have as h1 inner join
     have as h2 on h1.cusip=h2.cusip and
          intck("MONTH", h1.monthDate, h2.monthDate) between 1 and 24
group by h2.cusip, h2.monthDate, h2.ret
having count(h2.cusip)=24;

select * from roll;
quit;

Re: Rolling 24 month window-compute stdev of returns

LanMin — Mon, 03 Jun 2013 13:04:12 GMT

Thanks so much!!!

is it easy to change your code a little to have the same standard deviation value for all the month within the same year, i.e. for year 1987 month1-12, they all have the same stdev based on 24 months between 85 and 86.

Lan

Re: Rolling 24 month window-compute stdev of returns

PGStats — Mon, 03 Jun 2013 14:44:00 GMT

If I understand correctly, you could simply copy the January std over the rest of the year. Add the extra step :

data want(drop=currentYear);

set roll; by cusip;

retain stdRetYear currentYear;

if first.cusip or year(periodEndDate) ne currentYear

then call missing(stdRetYear, currentYear);

if month(periodEndDate) = 1 then do;

stdRetYear = stdRet;

currentYear = year(periodEndDate);

end;

run;

Re: Rolling 24 month window-compute stdev of returns

LanMin — Mon, 03 Jun 2013 17:51:00 GMT

Thank again, PG!

here is a rudimentary request,

following your code, I wrote

data want_1 (keep=cusip year stdRetYear);

set want;

year=(periodEndDate);

run;

this year variable is numeric,e.g. 10683 for year 1989, how can I convert it back to normal year ?

Lan

Re: Rolling 24 month window-compute stdev of returns

PGStats — Mon, 03 Jun 2013 18:03:25 GMT

To get a list of Stds, use :

data previousTwoYearStd (keep=cusip year stdRetYear);

set want;

if month(periodEndDate) = 1 then do;

year = year(periodEndDate);

output;

end;

run;

Re: Rolling 24 month window-compute stdev of returns

LanMin — Tue, 04 Jun 2013 21:14:44 GMT

PG, you are fantastic. Thanks!!!!!!

My real data is over 980000 observations (it is a large panel). when I ran your code (see below), it ran out of memory and stopped responding.

+++++++++++++++

proc sql;

create table roll as

select

h2.cusip,

h2.monthDate as periodEndDate format=yymmd7.,

h2.ret,

mean(h1.ret) as meanRet,

std(h1.ret) as stdRet

from

have as h1 inner join

have as h2 on h1.cusip=h2.cusip and

intck("MONTH", h1.monthDate, h2.monthDate) between 1 and 24

group by h2.cusip, h2.monthDate, h2.ret

having count(h2.cusip)=24;

select * from roll;

quit;

++++++++++++++++

I plan to do the following, but have 1 more question.

1. I plan to split the sample (1983-2011) into 3 sets, e.g. 1983-1993, then 1992-2002, then 2001-2011, the overlap is to include the prior 24-month data to compute my stdev.

I just ran your code above with 1983-1993, in the output window, it printed the results,

is there a way to not have the results printed- maybe that helps with memory size issue?

-Lan

Re: Rolling 24 month window-compute stdev of returns

PGStats — Tue, 04 Jun 2013 21:54:48 GMT

Just remove the statement

select * from roll;

I put it there only for checking the result on the small sample. It should make a big difference; try with the whole dataset again. The result is in dataset roll.

Re: Rolling 24 month window-compute stdev of returns

LanMin — Tue, 04 Jun 2013 22:16:36 GMT

It worked for full data set.

Thanks

-Lan