How to calculate buy-and-hold return based on daily return data

[Ariel]

Hello, 

Currently I have multiple daily data for multiple firms of the 5 day,  1 month(21 trading days), 3 months(63 trading days), 6 months(126 trading days), 9 months(189 trading days) and  12 months(252 trading days) stock buy-and-hold returns.

MY data is as below: (252 may not be the last data of a firm)

Firm_ID	date	daye	rtn
1101	2009/8/26	1	-0.0173
1101	2009/8/27	2	0.00147
…
1101	2010/8/25	252	0.03
1101		253
1101		…
1102	2011/3/23	1	0.0014
1102	2011/3/24	2	-0.007
…
1102	2011/9/9	252	0.045
1102		…
1205	2013/2/4	1	0.065
…

If I calculate it by hand then:

(1) 5 days: Buy and hold return=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day5)-1

(2) 1 month: BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day21)-1

(3) 3 months: BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day63)-1

....

(6) 12 months:BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day252)-1

However, I don't know how to write a sas program to do this...  

Please give me some suggestions. Thank you!

[user24feb · Accepted solution]

A brute force approach would be (maybe proc timedata is worth looking):

%Let Span=5; *!;

Data A;
  Do Firm=1 To 3;
    Do i=0 To 20;
      Date=IntNX('month',"01JAN2000"d,i,'same');
     Rtn=RanNor(1)/10;
   Output;
 End;
  End;
  Drop i;
  Format Date Date9.;
Run;

%Macro L;
%Do i=1 %To &Span.;
  %If &i.=1 %Then %Do;
  Data B;
    Set A;
    A_Rtn1=(1+Rtn);
  Run;
  %End;
  %Else %Do;
  Data B;
    Set B;
    By Firm;
    A_Rtn&i.=Lag(A_Rtn%Eval(&i.-1));
    If First.Firm Then Call Missing (A_Rtn&i.);
  Run;
  %End;
%End;
%Mend;
%L;

Data B (Drop=A_:);
  Set B;
  Array A A_:;
  Total_Rtn_&Span.=1;
  Do over A;
    Total_Rtn_&Span.=Total_Rtn_&Span.*A;
  End;
  Total_Rtn_&Span.=Total_Rtn_&Span.-1;
Run;

[user24feb]

However, this would give the same result:

%Let Span=5; *!;

Data A;
  Do Firm=1 To 3;
    Do i=0 To 20;
      Date=IntNX('month',"01JAN2000"d,i,'same');
     Rtn=RanNor(1)/10;
   Output;
 End;
  End;
  Drop i;
  Format Date Date9.;
Run;

Data B (Keep=Firm Date Rtn Total_Rtn_&Span.);
  Retain dummy ;
  Set A;
  By Firm;
  If First.Firm Then Do;
    count=0;
 dummy=1; 
  End;
  count+1;
  Drop_Value=Lag&Span.(Rtn); 
  If count gt &Span. Then Do;
    dummy=dummy*(1+Rtn)/(1+Drop_Value);
  End;
  Else Do;
    dummy=dummy*(1+Rtn);
  End;
  Total_Rtn_&Span.=dummy-1;
Run;

[Ariel]

Thanks for your help! 🙂 🙂