Dear all,
I have data like demonstrated in the code. "lret" is the stock return of a particular firm. "lbenchret" is the return of the market accordingly. This data is just the short version for demonstration. In the full sample, I have 36 months for more than 100 firms with all proper "lret" and "lbenchret".
data bhar;
input ticker $ Counthmonth EvntMonth AccMonth :yymmn6. lret lbenchret ;
datalines;
AA 0 201208 201208 0.0140682449 0.0195706004
AA 1 201208 201209 0.0338819793 0.0239470567
AA 2 201208 201210 -0.029220052 -0.019987832
AA 3 201208 201211 -0.018846242 0.0028426588
AA 4 201208 201212 0.031600109 0.0070434466
AA 5 201208 201301 0.0182653297 0.0491977573
AA 6 201208 201302 -0.033355557 0.0109998789
AA 7 201208 201303 0 0.0353553677
AA 8 201208 201304 -0.002350231 0.0179241582
AA 9 201208 201305 0.003523198 0.0205501713
AA 10 201208 201306 -0.083381591 -0.015112958
AA 11 201208 201307 0.0164873283 0.0482777567
AA 12 201208 201308 -0.028063068 -0.031798258
;
run;
I want to calculate the buy-and-hold return (BHAR) for 12 months/24 months/36 months of all firms base on lret and lbenchret. The formula is as follow:
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] }
where: i = firm i, lret = return of firm i in particular month, lbenchret = return of market for firm i in particular month.
How can I create a SAS code to do so?
Thank you so much!
I do not have SAS on this computer, so I cannot test, but I think something like this should work:
data want;
set bhar;
by ticker;
array loglret (0:12) 8 _temporary_;
array logbench(0:12) 8 _temporary_;
if first.ticker then
call missing(of loglret(*),of logbench(*));
_N_=mod(_N_,13);
loglret(_N_)=log(1+lret);
logbench(_N_)=log(1+lbenchret);
n_months=n(of loglret(*)); /* number of months used in calculation */
bhar=exp(sum(of loglret(*))-sum(of logbench(*));
run;
The idea is that the arrays are overwritten if there are more than 13 months for a given ticker.
data bhar;
input ticker $ Counthmonth EvntMonth AccMonth :yymmn6. lret lbenchret ;
datalines;
AA 0 201208 201208 0.0140682449 0.0195706004
AA 1 201208 201209 0.0338819793 0.0239470567
AA 2 201208 201210 -0.029220052 -0.019987832
AA 3 201208 201211 -0.018846242 0.0028426588
AA 4 201208 201212 0.031600109 0.0070434466
AA 5 201208 201301 0.0182653297 0.0491977573
AA 6 201208 201302 -0.033355557 0.0109998789
AA 7 201208 201303 0 0.0353553677
AA 8 201208 201304 -0.002350231 0.0179241582
AA 9 201208 201305 0.003523198 0.0205501713
AA 10 201208 201306 -0.083381591 -0.015112958
AA 11 201208 201307 0.0164873283 0.0482777567
AA 12 201208 201308 -0.028063068 -0.031798258
;
run;
data want;
set bhar;
by ticker;
retain cum_lret cum_lben 1;
if first.ticker then do;cum_lret=1;cum_lben=1;end;;
lret=sum(lret,1);
lbenchret=sum(lbenchret,1);
cum_lret=cum_lret*lret;
cum_lben=cum_lben*lbenchret;
if last.ticker then BHAR=cum_lret-cum_lben;
run;
Dear Ksharp, what if I have two different event days but for the same 1 firm with the same ticker? How should I revise your code?
data bhar;
input ticker $ Counthmonth EvntMonth AccMonth :yymmn6. lret lbenchret ;
datalines;
AA 0 201208 201208 0.0140682449 0.0195706004
AA 1 201208 201209 0.0338819793 0.0239470567
AA 2 201208 201210 -0.029220052 -0.019987832
AA 3 201208 201211 -0.018846242 0.0028426588
AA 4 201208 201212 0.031600109 0.0070434466
AA 5 201208 201301 0.0182653297 0.0491977573
AA 6 201208 201302 -0.033355557 0.0109998789
AA 7 201208 201303 0 0.0353553677
AA 8 201208 201304 -0.002350231 0.0179241582
AA 9 201208 201305 0.003523198 0.0205501713
AA 10 201208 201306 -0.083381591 -0.015112958
AA 11 201208 201307 0.0164873283 0.0482777567
AA 12 201208 201308 -0.028063068 -0.031798258
;
run;
data want (drop=_:);
array cumbnchmrk {-36:36} _temporary_ (73*1);
array cumfirm {-36:36} _temporary_ (73*1);
set bhar;
by ticker;
retain _bnchmrkdone 0;
if _bnchmrkdone=0 then cumbnchmrk{counthmonth}= cumbnchmrk{counthmonth-1}*(1+lbenchret);
if last.ticker then _bnchmrkdone=1;
cumfirm{counthmonth}=cumfirm{counthmonth-1}*(1+lret);
if counthmonth>=12 then bhar12 = cumfirm{counthmonth}/cumfirm{counthmonth-12}
- cumbnchmrk{counthmonth}/cumbnchmrk{counthmonth-12};
if counthmonth>=24 then bhar24 = cumfirm{counthmonth}/cumfirm{counthmonth-24}
- cumbnchmrk{counthmonth}/cumbnchmrk{counthmonth-24};
if counthmonth>=36 then bhar36 = cumfirm{counthmonth}/cumfirm{counthmonth-36}
- cumbnchmrk{counthmonth}/cumbnchmrk{counthmonth-36};
run;
This develops "true" 12 (not 13), 24, and 36-month rolling returns, but only for complete windows (i.e. no "short" windows).
Dear mkeintz, I want to calculate 12 months. Month "0" is the event month. That's why it's 13 months there in the data. Since your code has "countmonth-1", I think that I do not need to delete the event month before applying your code, right?
Btw, what does "73*1" mean?
How about in case that there are 2 events on 2 different days for the same month for the same company with the same ticker?
I applied your code to my whole data, but it did not work 😞
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.