turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Rolling cumulative 3-month and 9-month returns

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2011 06:07 AM

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;

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

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to smilingmelbourne

05-22-2011 10:00 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Patrick

05-22-2011 07:55 PM

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

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