Calculate cumulative annual return based on monthly returns

[Amatron]

Hi,

I have a .sas file containing monthly returns. In order to get annual returns, I am trying to write a SAS code, which takes each monthly return of each given year, adds 1 and multiplies them together.

For instance, if there are monthly returns from March- Dec,

March 2008  +5%

April 2008    -7%

etc

Dec 2008  +2%

Jan 2009  +3%

etc.

Dec 2009 -2%

the calculation should look like (1.05*0.93*...*1.02) for 2008, and (1.03*...*0.98) for 2009

I have many different stocks, which are all identified by a PERMNO number unique to that stock, and stock returns from 1998-2013.

After the calculation, SAS should return the annual cumulative return and copy it into a separate database, i.e. the resulting output database should look like this:

Permno   Year    Cumulative return

10104     2008      1.035

              2009      1.015

11038    1998       1.01

             1999       1.005

             2000        1.08

I have written the SAS code below, but for some reason, it doesn't work as intended. I have tried quite a few workarounds, but unfortunately, wasn't successful.

-------------------------------------------------------------------------------------------------------------------------------------

data ReturnsSAS;

set cleandat.ReturnSAS; n

year=year(date);

month=month(date);

proc sql;

  create table cum_returns as

  select permno                   , product(1+ret)  as cum_return

         , min(ret) as minret     , max(ret) as maxret

         , n(ret) as n_periods    , nmiss(ret) as n_miss

         , sum(ret=.P) as n_dot_p , min(date) as first_date

         , max(date) as last_date

  from ReturnsSAS

  where ('01jan1998'd <= date <= '31dec2013'd);

quit;

print data=ReturnsSAS.cum_returns; format first_date last_date yymmdd10.;

run;

------------------------------------------------------------------------------------------------------------------

Many thanks for your help,

Amatron

[RW9]

How about something like:

data have;
  length month $3 pcent $10;
  infile datalines delimiter=',';
  input permno year month $ pcent $;
datalines;
10104,2008,Jan,+5
10104,2008,Feb,-7
10104,2008,Mar,+2
10104,2008,Apr,+3
10104,2009,Jan,-2
10104,2009,Feb,+5
;
run;

proc sql;
  create table WANT as
  select  PERMNO,
          YEAR,
          MONTH,
          case  when index(PCENT,"+")>0 then 1.00 + (input(strip(tranwrd(PCENT,"+","")),best.) / 100)
                else 1.00 - (input(strip(tranwrd(PCENT,"-","")),best.) / 100) end as VAL
  from    WORK.HAVE;
quit;

data want (drop=lstyear lstpermno);
  set want;
  attrib cum_return format=best.;
  by permno;
  retain cum_return lstyear lstpermno;
  if _n_=1 then do;
    lstpermno=permno;
    lstyear=year;
    cum_return=val;
  end;
  else if lstyear ne year then do;
    lstyear=year;
    cum_return=val;
  end;
  else if lstpermno ne permno then do;
    lstpermno=permno;
    lstyear=year;
    cum_return=val;
  end;
  else do;
    cum_return=cum_return * val;
  end;
  if last.permno then output;
run;

[Ksharp]

You need post some sample data .

And I am sure @Pierre Gagnon will be very happy to help you. he have done it before in SQL.

[Haikuo]

Maybe OT, but I found it absurd that SAS does not have a PRODUCT function in par with SUM function, given that GEOMEAN function is at your disposal, otherwise Proc SQL will be fit right in.

Here I would recommend using a data step and a DOW.

Haikuo

[PGStats]

I would do something like:

proc sql;

create table annualReturns as

select permNo, year,

  count(ret) as nbMonths,

  exp(sum(log(1+ret))) as cumulativeReturn,

  exp(12*mean(log(1+ret))) as extrapolatedReturn

from monthlyReturns

group by permNo, year;

select * from annualReturns;

quit;

where cumulativeReturn is the cumulated return for the months present in the data and extrapolatedReturn is the expected cumulative return assuming that missing months will have the same average return as the known returns for that year.Note there is no PRODUCT summary function in SAS SQL, hence the exp(sum(log())).

(untested)

PG

[Ksharp]

If you would like to do it in data step.

data have;
  length month $3 ;
  infile datalines delimiter=',';
  input permno year month $ pcent ;
datalines;
10104,2008,Jan,+5
10104,2008,Feb,-7
10104,2008,Mar,+2
10104,2008,Apr,+3
10104,2009,Jan,-2
10104,2009,Feb,+5
;
run;
data want ;
 set have;
 by permno year;
 retain cum 1;
 if not missing(pcent) then cum=cum*(1+pcent*0.01);
 if last.year then do; output;cum=1;end;
run;

Xia Keshan