topic Rolling cumulative 3-month and 9-month returns in SAS Procedures

Rolling cumulative 3-month and 9-month returns

smilingmelbourne — Sun, 22 May 2011 10:07:14 GMT

Hi, I need to compute rolling 3-month and 9-month returns. I wrote this code but it is very awkward (and lengthy with y1, y2, y3, vv...y8);

Can you please help suggest a better yet simple code? Thanks

data temp;
input x y;
datalines;
15 151
151 54
890 521
451 90
521 588
515 85
52 80
89 56
24 55
252 51
145 58
;
run;

data m3; /*Rolling 3-month*/
set temp;
n=_n_;
y1=lag(y); y2=lag(y1);
cum_y=y+y1+y2;
if mod(n,3)^=0 then delete;
keep x cum_y;
run;

data m9; /*Rolling 9-month*/
set temp;
n=_n_;
y1=lag(y); y2=lag(y1); y3=lag(y2); y4=lag(y3); y5=lag(y4); y6=lag(y5); y7=lag(y6); y8=lag(y7);
cum_y=y+y1+y2+y3+y4+y5+y6+y7+y8;
if mod(n,9)^=0 then delete;
keep x cum_y;
run;

Re: Rolling cumulative 3-month and 9-month returns

Patrick — Sun, 22 May 2011 14:00:58 GMT

data m3;
set temp;
retain cum_y;
cum_y=sum(cum_y,y);
if mod(_n_,3)=0 then
do;
output;
call missing(cum_y);
end;
run;

data m9;
set temp;
retain cum_y;
cum_y=sum(cum_y,y);
if mod(_n_,9)=0 then
do;
output;
call missing(cum_y);
end;
run;

... and "better" than y3=lag(y2); would have been y3=lag3(y);

HTH
Patrick

Re: Rolling cumulative 3-month and 9-month returns

smilingmelbourne — Sun, 22 May 2011 23:55:37 GMT

Thanks, Pattrick, for help. I'd read the book on Output several times, and didn't quite really catch the idea until I've read your code! I just couldn't think of using Output right after the MOD function. Yesterday I found a macro from a university for this rolling, and the macro was far, far complicated than yours. Basically, they used Proc Means for each 3-month and 9-month interval, then output the datasets, then use Proc Append to concatenate all datasets, and for the same thing it has at leas 5 SAS programs.

Thanks so much and I've learnt a lot from your codes. Message was edited by: smilingmelbourne