topic Re: 1 year (252 days) Buy and hold abnormal return for daily data in SAS Programming

1 year (252 days) Buy and hold abnormal return for daily data

Fer_ — Thu, 12 Jan 2023 23:40:36 GMT

Hello everyone,

I want to calculate buy and hold abnormal return for 252 days for daily dataset for different firms (identify by cusip), I provide my dataset bellow.

BHAR(1) = { [(1+ret) of day1] * [(1+ret) of day2] * [(1+ret) of day 3] * [(1+ret) of day4] * ... * [(1+ret) of day 252] } - { [(1+vwretd) of day1] * [(1+vwretd) of day2] * [(1+vwretd) of day3] * [(1+vwretd) of day4] * ... * [(1+vwretd) of day252 }

I really appreciate your help.

Thanks in advance

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Reeza — Thu, 12 Jan 2023 23:50:53 GMT

Please provide sample data as text or ideally as a data step, not as an image. I'm assuming you have a large data set?
Output is one number per CUSIP per year? Resets every year or is cumulative? Is your data guaranteed to have the correct data intervals or could you have missing data that needs to be accounted for?

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Fer_ — Fri, 13 Jan 2023 00:00:35 GMT

Thanks for your reply.

Yes I have a large dataset. I provide a sample bellow as text. For every year in my sample, I have stock return and market return without missing data.( I already delete them)

cusip	year	ret	vwretd
11001	19900123	-0.055	-0.002
11001	19900124	-0.0098	-0.006
11001	19900125	-0.009	-0.013
11001	19900128	-0.0087	-0.007
..	…	…	…
11001	20211231	0.003	0.0056
11002	19900204	-0.0019	-0.004
11002	19900205	-0.014	-0.0045
…	…	…	…
11002	20211231	0.004	-0.007
11003	19900115	0.0013	0.0016
11003	19900116	-0.014	0.001
…	…	…
11003	20211231	0.005	-0.007

Re: 1 year (252 days) Buy and hold abnormal return for daily data

mkeintz — Fri, 13 Jan 2023 18:40:19 GMT

Do you want rolling 252-day returns?
Do you want only full 252-day windows?

Let's say your answer to each question is yes. Then: (code amended):

data want (drop=_:);
  set have;
  by cusip notsorted;

  if first.cusip then call missing(_obs,_sum_log_cusip_ret_plus1,_sum_log_index_ret_plus1);
  _obs+1;
  
  _sum_log_cusip_ret_plus1 + log(1+ret)    - ifn(_obs>252,lag252(log(1+ret)),0) ;

  _sum_log_index_ret_plus1 + log(1+vwretd) - ifn(_obs>252,lag252(log(1+vwretd)),0);

  window_start = lag251(year) ;

  if _obs>=252 ;
  bhar_252 =  exp(_sum_log_cusip_ret_plus1) - exp(_sum_log_index_ret_plus1) ;
run;

This assumes your data are sorted by cusip and date (which you have labelled as year). Now if the data are not sorted by cusip, but are sorted by date within cusip, you can change

  by cusip;

  by cusip notsorted;

Now note that this involves calculating 252-day cumulative returns for VWRETD for each CUSIP. But the VWRETD is based on a market index, and thus has the same value for every cusip on each given date. This means that if you have, say, 400 CUSIPs, you will be calculating the same values 400 times for the VWRETD components, where you really need to do it only once. After calculating those returns once, you can store them in an array indexed by date. Then, for each CUSIP, just retrieve them for calculating BHAR by date. In other words, you could cut down your total calculations by half (400 times for 400 CUSIP returns, but only once for index returns).

Edited note: I didn't notice a missing close paren in the lag252 argument. It should have been lag252(log(1+ret)), not lag252(log(1+ret). Same with the vwretd-based component. It's now corrected in the code above.

Also I have added retrieval of a window starting date, as per other comments by @Fer_.

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Fer_ — Fri, 13 Jan 2023 03:42:06 GMT

Thanks for your response.

I got these two errors:

ERROR 72-185: The LAG function call has too many arguments.

ERROR 71-185: The IFN function call does not have enough arguments.

for these lines of code:

sum_log_cusip_ret_plus1 + log(1+ret)- ifn(_obs>252,lag252(log(1+ret),0));
_sum_log_index_ret_plus1 + log(1+vwretd) - ifn(_obs>252,lag252(log(1+vwretd),0));

Do you know how to solve them? Thanks in advance.

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Fer_ — Fri, 13 Jan 2023 03:55:00 GMT

And yes I want the rolling 252-days, which means that for each date and each cusip, I want to calculate bhar for that date and 251 days after that. For example for day1, I need bhar from day1 to day 252, then day 2 to day 253, day 3 to day 254...... until then end for each cusip.

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Reeza — Fri, 13 Jan 2023 16:30:52 GMT

The 252 days - it doesn't matter if they're in a 'row' or should it be 252 days and ignore that there are missing values?

Re: 1 year (252 days) Buy and hold abnormal return for daily data

mkeintz — Fri, 13 Jan 2023 18:43:20 GMT

This is why it's helpful to offer sample data in the form of a working data step. I usually test against sample data. In any case, this appears to be the result of a forgotten close paren, which I've changed in my initial response.

Re: 1 year (252 days) Buy and hold abnormal return for daily data

mkeintz — Fri, 13 Jan 2023 18:44:12 GMT

@Fer_ wrote:

And yes I want the rolling 252-days, which means that for each date and each cusip, I want to calculate bhar for that date and 251 days after that. For example for day1, I need bhar from day1 to day 252, then day 2 to day 253, day 3 to day 254...... until then end for each cusip.

My previous code used the end-of-window date, but I've added code to provide beginning of window date.

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Reeza — Fri, 13 Jan 2023 20:06:06 GMT

@mkeintz your code does a 252 observation rolling calculation. If you're missing 6 months of data in there for some reason it will use part of one year and part of the other? (6 months isn't realistic but the trading days do vary by CUSPID IIRC).

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Fer_ — Fri, 13 Jan 2023 21:40:23 GMT

Thanks for the code, unfortunately as my dataset is too large, I just could provide a small sample of it. I have a question about the code. My dataset starts from 1990 to 2021, but by running the code the window start is from 1991, which means that for every date the code notice to 252 days before that date. but I want to see 252 days ahead of each date. BHAR for day1= (1+ret day1)(1+ret of day2)....(1+ret day252)- ((1+vwretd day1)(1+vwretd day2)...(1+vwretd day252)).

I am thankful for your help.

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Fer_ — Fri, 13 Jan 2023 21:41:44 GMT

I do not have missing values. I already delete them.

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Reeza — Fri, 13 Jan 2023 23:14:52 GMT

So you have a 252 day sequence that's always in order with no daily gaps? My mistake, I interpreted your comment as you deleted the observations with missing data and thus had gaps in your time series, ie your dates could jump if data was missing.

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Fer_ — Fri, 13 Jan 2023 23:16:52 GMT

yes

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Reeza — Fri, 13 Jan 2023 23:37:11 GMT

Glad to hear. I probably wouldn't refer to that as 'I delete them' for your missing data. You've accounted for missing intervals in some manner instead (interpolation, spline etc), which is ideal. Good luck with your analysis!

Re: 1 year (252 days) Buy and hold abnormal return for daily data

mkeintz — Sat, 14 Jan 2023 15:08:07 GMT

@Fer_ wrote:

Thanks for the code, unfortunately as my dataset is too large, I just could provide a small sample of it. I have a question about the code. My dataset starts from 1990 to 2021, but by running the code the window start is from 1991, which means that for every date the code notice to 252 days before that date. but I want to see 252 days ahead of each date. BHAR for day1= (1+ret day1)(1+ret of day2)....(1+ret day252)- ((1+vwretd day1)(1+vwretd day2)...(1+vwretd day252)).

I am thankful for your help.

If you take a look at the code, as revised, you will see that I provided a WINDOWS_START date, so the BHAR values are look-ahead values. So, examine the WINDOWS_START value, instead of the YEAR value (which will indicate the end of the window).

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Fer_ — Sat, 14 Jan 2023 20:33:20 GMT

Thanks for reply, for example for data in 1991, window start is 1990 and year 1991. And my dataset start from 1991 not 1990 by running the code. It seems to me that for 1991, the code starts to see lag 252 days before the date which means that the window start is 1990 (it sees the lag of 1991 not ahead).

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Patrick — Sun, 15 Jan 2023 00:29:36 GMT

@Fer_ wrote:

Thanks for reply, for example for data in 1991, window start is 1990 and year 1991. And my dataset start from 1991 not 1990 by running the code. It seems to me that for 1991, the code starts to see lag 252 days before the date which means that the window start is 1990 (it sees the lag of 1991 not ahead).

The solution provided is really smart and efficient coding. It took me a bit to fully appreciate it and I was grateful to @mkeintz that he kept the code to the relevant minimum and didn't overload it with additional logic like some look-ahead.

If you spend the time to fully appreciate what has been given to you then you'll also see variable window_start. If you want to add the aggregated values at the beginning of your window then just use this variable to merge/join the values from Want to your Have table (on have.cusip=want.cusip and have.year=want.window_start).

Sample data can be mock-up data. It just needs to be representative of your real data.
Some code like below would suffice to create such sample data. If there could be some missing dates in your time series or similar then that's what you would have to mock-up as well for the sample to be representative.

data have;
  do cusip='11001','11999';
    do year='01jun2020'd to '20feb2021'd;
      ret=rand('uniform',-0.09,0.09);
      vwretd=rand('uniform',-0.09,0.09);
      output;
    end;
  end;
  format year yymmddn8.;
run;

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Fer_ — Sun, 15 Jan 2023 00:48:55 GMT

Thanks for the code you sent.

For BHAR 252 days, for 2021 which is the last year in my dataset, I should not have any values after running the codes as I do not have data for 252 days ahead for them. But when I run the code, BHAR have value until the end of my dataset (20211231). That is why I asked question about the codes.

Re: 1 year (252 days) Buy and hold abnormal return for daily data

Patrick — Sun, 15 Jan 2023 01:22:06 GMT

@Fer_ wrote:

Thanks for the code you sent.

For BHAR 252 days, for 2021 which is the last year in my dataset, I should not have any values after running the codes as I do not have data for 252 days ahead for them. But when I run the code, BHAR have value until the end of my dataset (20211231). That is why I asked question about the codes.

WINDOW_START allows you to join the aggregated values to the beginning of the window.

data have;
  do cusip='11001','11999';
    do year='01jun2020'd to '20feb2021'd;
      ret=rand('uniform',-0.09,0.09);
      vwretd=rand('uniform',-0.09,0.09);
      output;
    end;
  end;
  format year yymmddn8.;
run;

data inter (drop=_:);
  set have;
  by cusip notsorted;

  if first.cusip then call missing(_obs,_sum_log_cusip_ret_plus1,_sum_log_index_ret_plus1);
  _obs+1;
  
  _sum_log_cusip_ret_plus1 + log(1+ret)    - ifn(_obs>252,lag252(log(1+ret)),0) ;

  _sum_log_index_ret_plus1 + log(1+vwretd) - ifn(_obs>252,lag252(log(1+vwretd)),0);

  window_start = lag251(year) ;
  format window_start yymmddn8.;

  if _obs>=252 ;
  bhar_252 =  exp(_sum_log_cusip_ret_plus1) - exp(_sum_log_index_ret_plus1) ;
run;

data want;
  merge have(rename=(year=date)) inter(keep=cusip window_start bhar_252 year rename=(window_start=date year=window_end));
  by cusip date;
run;