Rolling 24 month window-compute stdev of returns

Solved
Frequent Contributor
Posts: 102

Rolling 24 month window-compute stdev of returns

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.

lan

Accepted Solutions
Solution
‎06-02-2013 02:49 PM
Posts: 5,523

Re: Rolling 24 month window-compute stdev of returns

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;

PG

PG

All Replies
Solution
‎06-02-2013 02:49 PM
Posts: 5,523

Re: Rolling 24 month window-compute stdev of returns

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;

PG

PG
Frequent Contributor
Posts: 102

Re: Rolling 24 month window-compute stdev of returns

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

Posts: 5,523

Re: Rolling 24 month window-compute stdev of returns

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;

PG

PG
Frequent Contributor
Posts: 102

Re: Rolling 24 month window-compute stdev of returns

Thank again, PG!

here is a rudimentary request,

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

Posts: 5,523

Re: Rolling 24 month window-compute stdev of returns

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;

PG

PG
Frequent Contributor
Posts: 102

Re: Rolling 24 month window-compute stdev of returns

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

Posts: 5,523

Re: Rolling 24 month window-compute stdev of returns

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.

PG

PG
Frequent Contributor
Posts: 102

Re: Rolling 24 month window-compute stdev of returns

It worked for full data set.

Thanks

-Lan

🔒 This topic is solved and locked.