DATA Step, Macro, Functions and more

Rolling 24 month window-compute stdev of returns

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

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.


Thanks for your time!


lan

Attachment
Attachment

Accepted Solutions
Solution
‎06-02-2013 02:49 PM
Respected Advisor
Posts: 4,649

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

View solution in original post


All Replies
Solution
‎06-02-2013 02:49 PM
Respected Advisor
Posts: 4,649

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

Respected Advisor
Posts: 4,649

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,

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

Respected Advisor
Posts: 4,649

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

Respected Advisor
Posts: 4,649

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 Smiley Happy

-Lan

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 758 views
  • 1 like
  • 2 in conversation