Help using Base SAS procedures

cumulative returns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

cumulative returns

[ Edited ]

dear sas members

 

hi

 

i'm trying to calculate cumulative returns, but i dont know how can i do... 

 

i want to calculate monthly return by using daily returns.

[(1+R1)(1+R2)...(1+Rn)]-1

 

my data set:

date return(%)

19910801 5

19910802 3

19910803 2

19910804 3

...

19910831 3

19910901 -1

19910902 -0.8

...

 

(from 1991081 to 20151231)

 

Is there any way to calculate cumulative return for each month?

 

 

Your help is truly appreciated!

 

Thanks,

 


Accepted Solutions
Solution
‎02-24-2017 08:47 AM
Valued Guide
Posts: 797

Re: cumulative returns

[ Edited ]

Instead of keeping running products, sum the logs of (1+returnpct/100).  At the end of the month take the antilog and subtract 1 (and mult by 100 if you want percentages).

 

In SAS, you can merge a dataset with itself, but offset by one record (see "firstobs=2") to determine whether the record in hand is the last for the current month:

 

data mreturns (drop=sumlog);
  merge dreturns  dreturns(firstobs=2 keep=date rename=(date=nxtdate));
  retain sumlog 0;
  sumlog=sum(sumlog,log10(1+returnpct/100));
  if month(date)^=month(nxtdate) then do;
    mreturn=10**sumlog-1;
    output;
    sumlog=0;
  end;
run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

Re: cumulative returns

Hi,

 

Generally a good idea to search for things first, I have seen at least three similar posts in the last day or two:

https://communities.sas.com/t5/forums/searchpage/tab/message?q=cumulative

Solution
‎02-24-2017 08:47 AM
Valued Guide
Posts: 797

Re: cumulative returns

[ Edited ]

Instead of keeping running products, sum the logs of (1+returnpct/100).  At the end of the month take the antilog and subtract 1 (and mult by 100 if you want percentages).

 

In SAS, you can merge a dataset with itself, but offset by one record (see "firstobs=2") to determine whether the record in hand is the last for the current month:

 

data mreturns (drop=sumlog);
  merge dreturns  dreturns(firstobs=2 keep=date rename=(date=nxtdate));
  retain sumlog 0;
  sumlog=sum(sumlog,log10(1+returnpct/100));
  if month(date)^=month(nxtdate) then do;
    mreturn=10**sumlog-1;
    output;
    sumlog=0;
  end;
run;
Occasional Contributor
Posts: 6

Re: cumulative returns

i'm not good at mathmatics... so i can't exactly understand about the steps....Cat LOL

I solved it.

Thank you so much!

 

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 167 views
  • 1 like
  • 3 in conversation