Dear Madam/Sir,
I would like to calculate market-adjusted buy and hold return as follows:
BHAR(i) = { [(1+lret) of month 0] * [(1+lret) of month 1] * [(1+lret) of month 2] * [(1+lret) of month 3] * ... * [(1+lret) of month 12] } - { [(1+lbenchret) of month 0] * [(1+lbenchret) of month 1] * [(1+lbenchret) of month 2] * [(1+lbenchret) of month 3] * ... * [(1+lbenchret) of month 12] }
First, I merge CSRP monthly return data and Compustat fiscal year month( variable name:closemonth in the current file).
I have made sas program code below using posting in this community. However, I don't know how to use "closemonth" to compute buy hold return for the previous eleven months and return for the fiscal year month.
%macro calret;
data c4;
set c3;
by permno;
lret=log(ret+1);
lmret=log(vwretd+1);
array laglret[11] laglret1-laglret11;
array laglmret[11] laglmret1-laglmret11;
%do j=1 %to 11;
laglret&j=lag&j(lret);
laglmret&j=lag&j(lmret);
%end;
if first.permno then count=1;
do i=count to 11;
laglret[i] = .;
laglmret[i] = .;
end;
count +1 ;
cumret= exp( sum(of lret laglret1-laglret11)) -1;
mcumret= exp( sum(of lmret laglmret1-laglmret11)) -1;
if last.permno then BHAR=cumret-mcumret;
%mend calret;
%calret;
run;
Any help will be highly appreciated.
Thank you
Sincerely,
Joon1
What do you want the output to look like? Do you want rolling 12-month return-over-benchmark? If so, then what is the significance of the closing month data, since the output would be monthly, and would have an updated value for every month.
Or do you want one output record per year, for the fiscal year closing month?
By the way, since you are probably using the same benchmark for every company in your analysis, there is no need to recalculate the rolling benchmark returns for each company over the same time span. You can calculate it once, and store the benchmark returns in a two-way matrix, with 12 columns and one row for each year in your data.
But first please provide, in the form of a sas data step, what the output data should look like.
Thank you so much for your kind reply, mkeintz.
I want one output record per year, for the fiscal year closing month. Thank you so much for your help again. Joon1.
PERMNO | DATE | closemonth | RET | VWRETD | BHAR | |
78736 | 19910731 | . | 0.02439 | 0.0468 | ||
78736 | 19910830 | . | 0.04762 | 0.02682 | ||
78736 | 19910930 | . | 0.01705 | -0.011 | ||
78736 | 19911031 | . | 0 | 0.01782 | ||
78736 | 19911129 | . | -0.1285 | -0.0373 | ||
78736 | 19911231 | . | -0.1282 | 0.10678 | ||
78736 | 19920131 | . | 0.16177 | -0.0012 | ||
78736 | 19920228 | . | -0.0633 | 0.01335 | ||
78736 | 19920331 | . | -0.0541 | -0.0237 | ||
78736 | 19920430 | . | -0.1429 | 0.01342 | ||
78736 | 19920529 | . | -0.1833 | 0.00644 | ||
78736 | 19920630 | 6 | -0.1837 | -0.0193 | calculated BHAR |
That's not an annual file in your response. It's a monthly file, in which one record out of 12 has a value for market-adjusted BHAR.
And because you have not provided the data in the form of a SAS data step, the program below is completely untested.
Also, per my previous comment, it doesn't make sense to calculate the benchmark returns over-and-over again, as per your initial program. So my program below assumes you have, in addition to your C3 dataset, another dataset I will call BENCHMARK, containing DATE and VWRETD (the benchmark monthly return). It will be a short file, since it only has one record per month over your entire time range. (while C3 has one record per month, but for every PERMNO).
data want;
** Generate 12-month rolling benchmark returns **
** Do this only once and save in an array **;
if _n_=1 then do until (end_of_benchmark);
set benchmark end=end_of_benchmark;
array bmrk_ret {2001:2019,1:12} _temporary_ ;
array logplusone {12} _temporary_;
y=year(date);
m=month(date);
logplusone{m}=log(1+vwret);
if n(of logplusone{*})=12 then bmrk_ret{y,m}=exp(sum(of logplusone{*}))-1;
end;
set c3;
by permno;
if first.permno then call missing(of logplusone{*});
m=month(date);
y=year(date);
logplusone{m}=log(1+ret);
if closemonth^=.;
** Missing Paren, correction below ** if n(of logplusone{*})=12 then bhar=exp(sum(of logplusone{*})-1 - bmrk_ret{y,m};
if n(of logplusone{*})=12 then bhar=exp(sum(of logplusone{*}))-1 - bmrk_ret{y,m};
run;
Lags can often be very useful, but they are too much work for this particular application. Instead it makes use of array that are marked "_TEMPORARY_", which means 2 things:
(1) the array data will not be output, and
(2) importantly, the contents of the arrays are retained across all observations.
So not only can you retrieve the benchmark returns long after they are calculated, you also always have the most recent 12-months of permno returns in the array LOGPLUS1.
IMPORTANT; This program assumes not only that the data are sorted by date, within permno, but also that there are no holes in the data. This is because if there were a hole (say missing "MARCH" for permno with closemonth=6), then the 12-month return would use the most recent data for every month but MARCH, whose data would be 12-months too old.
Editted note: I noticed a missing paren in the code, commented the offending statement, and inserted a correct one.
Thank you so much, mkeintz for your kind advice.
Your program worked well as follows. However, I checked BHAR calculation of one company(permno=10001) and it seems that calculated BHAR based on your program is high (the attached excel sheet). It will be grateful if you can think of reasons why the gap exists. Also how I can add one line to enable program to calculate BHAR when at least 6 monthly observations are available related to your comment below.
"This is because if there were a hole (say missing "MARCH" for permno with closemonth=6), then the 12-month return would use the most recent data for every month but MARCH, whose data would be 12-months too old."
data c4;
** Generate 12-month rolling benchmark returns **
** Do this only once and save in an array **;
if _n_=1 then do until (end_of_benchmark);
set benchmark end=end_of_benchmark;
array bmrk_ret {1990:2019,1:12} _temporary_ ;
array logplusone {12} _temporary_;
y=year(date);
m=month(date);
logplusone{m}=log(1+vwretd);
if n(of logplusone{*})=12 then bmrk_ret{y,m}=exp(sum(of logplusone{*}))-1;
end;
set c3;
by permno;
if first.permno then call missing(of logplusone{*});
m=month(date);
y=year(date);
logplusone{m}=log(1+ret);
if closemonth^=.;
if n(of logplusone{*})=12 then bhar=exp(sum(of logplusone{*})-1 - bmrk_ret{y,m});
run;
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
93110 at 399:17 93110 at 399:22
NOTE: There were 2745939 observations read from the data set WORK.BENCHMARK.
NOTE: There were 2745939 observations read from the data set WORK.C3.
NOTE: The data set WORK.C4 has 222689 observations and 33 variables.
NOTE: DATA statement used (Total process time):
real time 4.72 seconds
cpu time 4.71 seconds
Thank you so much
Joon1
Dear mkeniz,
I have difficulty to understand the generated output by sas program. I would like to compute the following BHAR:
Buy and Hold Abnormal return= ((1+return(m1))(1+return(m2))----(1+return(m12)) - (1+marketreturn(m1)(1+marketreturn(m2))---(1+marketreturn(m12)), where m1=closemonth-11, m2=closemonth-10, m3=closemonth-9, m4=closemonth-8, m5=closemonth-7, --- m12=closemonth
As shown in the excel sheet, the computed values in three different ways are different.
computed value by original definition above: 0.052
computed value by log and exponential: 0.023
computed value by current sas program: 0.388
It will be highly appreciative if you can give an idea how to fix program to generate computed value by original definition (0.052).
data c4;
** Generate 12-month rolling benchmark returns **
** Do this only once and save in an array **;
if _n_=1 then do until (end_of_benchmark);
set benchmark end=end_of_benchmark;
array bmrk_ret {1990:2019,1:12} _temporary_ ;
array logplusone {12} _temporary_;
y=year(date);
m=month(date);
logplusone{m}=log(1+vwretd);
if n(of logplusone{*})=12 then bmrk_ret{y,m}=exp(sum(of logplusone{*}));
end;
set ch2;
by permno;
if first.permno then call missing(of logplusone{*});
m=month(date);
y=year(date);
logplusone{m}=log(1+ret);
if closemonth^=.;
if n(of logplusone{*})=12 then bhar=exp(sum(of logplusone{*})- bmrk_ret{y,m});
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: