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!
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.