topic Re: include only Week Dates in Event Study in SAS Programming

include only Weekdays (trading days) & exclude weekends & holidays in Event Study

LucyDang — Tue, 18 Dec 2018 23:51:42 GMT

Hello SAS gurus,

I am calculating the cumulative abnormal return for an event study. I used the event window of (-180, +1). My concern is how I can include only weekdays/trading days for my event window (that means excluding weekend and holidays). For example, some announcement (0, the event) will take place on Friday, so the +1 day will be Monday, NOT Saturday. Similarly, some announcement (0, the event) will take place on Monday, and I only want to include 180 trading days (weekdays) before the event day without weekends and holidays.

The code I used is provided by KurtBremser's code in this post "Dates for Event Study"

data Event_intermediate;
	format date EventDate yymmddn8.;
	set stock.events (keep=EventDate Ticker);
	do Date = EventDate - 180 to EventDate + 1;
		output;
	end;
run;

Please help me with the code!

Thank you!

Re: include only Week Dates in Event Study

r_behata — Tue, 18 Dec 2018 22:52:49 GMT

Post the sample data and the code you have tried .

Re: include only Week Dates in Event Study

LucyDang — Tue, 18 Dec 2018 23:13:42 GMT

Hi r_behata, I have updated the post! Thank you!

Re: include only Weekdays (trading days) in Event Study

Reeza — Tue, 18 Dec 2018 23:20:06 GMT

Use INTNX with the WEEKDAY option, but this doesn't include holidays.

If you need to factor in holidays you need a different approach.

dateLater = intnx('weekday', eventDate, 180);

Or you can test each day with the WEEKDAY() function. Saturday is 7 and Sunday is 1.

@LucyDang wrote:

Hello SAS gurus,

I am calculating the cumulative abnormal return for an event study. I used the event window of (-180, +1). My concern is how I can include only weekdays for my event window. For example, some announcement (0, the event) will take place on Friday, so the +1 day will be Monday, NOT Saturday. Similarly, some announcement (0, the event) will take place on Monday, and I only want to include 180 trading days (weekdays) before the event day.

The code I used is provided by KurtBremser's code in this post "Dates for Event Study"
data Event_intermediate;
	format date EventDate yymmddn8.;
	set stock.events (keep=EventDate Ticker);
	do Date = EventDate - 180 to EventDate + 1;
		output;
	end;
run;
Please help me with the code!

Thank you!

Re: include only Weekdays (trading days) in Event Study

LucyDang — Tue, 18 Dec 2018 23:49:29 GMT

Hi Reeza, thank you. I have one more question: "how can I exclude the holidays in the (-180, +1) window period?"

Re: include only Weekdays (trading days) in Event Study

Reeza — Tue, 18 Dec 2018 23:58:32 GMT

That's a lot harder because the holidays can vary. You need to create your own custom table of dates with the dates you want to include then and use that within INTNX. That's a lot more complicated of a problem.
Holidays vary from company to company and/or country to country. You may need to consider that as well.

Here's an example of how to solve this type of situation.

http://www.sascommunity.org/wiki/Generating_Holiday_Lists

Re: include only Weekdays (trading days) & exclude weekends & holidays in Event Study

ShiroAmada — Wed, 19 Dec 2018 00:23:21 GMT

Is it something lie this...

data Event_intermediate;
   eventdate='18Jan2019'd;
	format date EventDate yymmddn8.;
	do Date = EventDate - 180 to EventDate + 1;
		output;
	end;
run;

data want;
  set Event_intermediate;
  where weekday(date) not in (1, 7);
  wkday=weekday(date);
run;

data holidays;
  infile datalines;
  input
  holiday date9.;
datalines;
31dec2018
01jan2019
;
run;

proc sql;
  delete from want
  where date in (select holiday from holidays);
quit;

Re: include only Weekdays (trading days) & exclude weekends & holidays in Event Study

LucyDang — Sat, 22 Dec 2018 06:55:04 GMT

Unfortunately, I still do not have the answer to this problem.

Please see the example datalines below. In this file, I have some variables for one stock: date, eventdate, ticker $, mrkReturn (return of the market), return (of this stock), before (#of days before/after the event date), and wday (check the weekday of each date, from 2=monday to 6=friday).

data now;
input date eventdate ticker $ mrkReturn return before wday;
datalines;
20120830 20120831 AA -0.007806 -0.01171 -1 5
20120831 20120831 AA 0.005073 0.014218 0 6
20120904 20120831 AA -0.001166 -0.01635 4 3
;
run;

Normally, if the event date fall in (2,5), that's fine because the +1 day I want to use to calculate the abnormal return will be the following day (3,6 respectively). However, if the event day falls in wday=6 (Friday), I need to calculate the abnormal return on Monday (skip Sat, Sunday, and in this case, it even skipped Monday (2012/09/03) because it is not a trading day), in this file the variable before = 4. I need to make the variable "before" = 1 for later calculation.

How I make a program that automatically recognizes the following trading day as a +1 day of the event and sets value of "before"=1?

Is that likely that I can create a code and assign "if EventDate = Friday, then retain & write the next row (which record data for a trading day) to output table & set "before" = 1"?

Thank you!

Re: include only Weekdays (trading days) & exclude weekends & holidays in Event Study

Reeza — Sat, 22 Dec 2018 17:28:29 GMT

The easiest way to do this is with the customized calendar approach I mentioned in my previous reply. Did you try that? If so, post the code you've tried and I'll take a look.

Re: include only Weekdays (trading days) & exclude weekends & holidays in Event Study

mkeintz — Sun, 23 Dec 2018 00:53:42 GMT

Skipping weekends will be relatively easy. But what about holidays and other market-closures? Sep 11, 2001 was a Tuesday - the market didn't re-open until the following Monday, or the recent market closure in honor of George HW Bush.

You probably have daily stock trading file. If so, you could use the date variables to generate a stock trading day calendar. Then you could use it toe generate the (-180,1) trading day range for the event study. In the unlikely event you don't have such a dataset, you can generate one from publically available S&P500 data. I found such a source at https://www.investing.com/indices/us-spx-500-historical-data, which goes back to January 2006.

Re: include only Weekdays (trading days) & exclude weekends & holidays in Event Study

LucyDang — Tue, 25 Dec 2018 02:23:11 GMT

Hi Reeza, I used a very dumb way to fix the problem. I don't think think that my approach will be helpful for the future. Since I am very new to SAS (just study SAS for several weeks), it's very difficult for me to understand what you said that why I think of doing like following.

I just keep everything as is in the Event data (include all holidays & weekends) but .with longer time period (-200, +4) to cover for the weekends & holidays). Then I merged the event data with the daily stock return data. Since daily stock return data was from CRPS database, it only includes trading days, when I merged, the holidays & weekends automatically disappeared in the merged file.

I splited this merged data into "estper" table including all observation before the event date and into "evntper" table including all observation after the event date. In table "estper" I do some minor calculation for the abnormal return to have a table named "mmparam" and use this table to merge with the "eventper" to create "ar" tables.

In the table "evntper", variable "before" that I mentioned in a post related with this post, only includes a value of 0, 3 or 4. I then looked at the eventdate to see how many dates are on Friday and its following Mondays are holidays and noted these specific dates, then I create three tables. One table includes Friday-event date with normal working day on Monday. Second table includes Friday-event date with holiday on Monday. Final table includes all other event dates. After that, I manually change the value of "before" from 3 to 1 (Monday is the third day of the Event; Sat=1st and Sun=2nd were deleted when I merged 2 table). For Friday-event date with Monday is a holiday, I change the value of "before" from 4 to 1 (Sat=1, Sun=2, Mon=3 are not working/trading day).

After I successfully changed the value of the variable "before", I concatenated the 3 tables to have the final clean table "arclean".

Quite a long story =))

BTW, how to master my SAS skill? I feel it too difficult 😞

data Event_intermediate;
	format date EventDate yymmddn8.;
	set stock.events (keep=EventDate Ticker);
	do Date = EventDate - 200 to EventDate + 4;
		output;
end;
run;
proc sort data=Event_intermediate;
	by TICKER Date;
run;
data stock.want;
	merge
		Event_intermediate (in=a)
		stock.StockDayClean (in=b)
	;
	by TICKER Date;

	if a and b;
	before=Date-EventDate;
	Ewday=weekday(EventDate);
run;

proc sort data=stock.want;
	by TICKER EventDate Date;
run;

data stock.estper stock.evntper;
	set stock.want;
	if before<0 then output stock.estper;
	if before>=0 then output stock.evntper;
run;
proc reg data=stock.estper outest=mmparam (rename=(intercept=alpha MrkReturn=beta)
keep=TICKER EventDate Date intercept MrkReturn) noprint;
by TICKER EventDate;
model return = MrkReturn;
quit;
data ar;
merge stock.evntper mmparam;
by TICKER EventDate;
ar = return - alpha - beta*MrkReturn;
run;
data arED6H arED6WK arEDR;
set ar;
if EventDate in ('31AUG2012'd, '12FEB2016'd) then output arED6H;
else if Ewday=6 then output arED6WK;
else output arEDR;
run;
data arED6Hclean;
set arED6H;
if WdayStock=3 then before=1;
run;
data arED6WKclean;
set arED6WK;
where before<4;
if WdayStock=2 then before=1;
run;
data arEDRclean;
set arEDR;
where before<2;
run;
data stock.arclean;
set arEDRclean arED6WKclean arED6Hclean;
run;
proc sort data=stock.arclean;
by ticker EventDate;
run;

Re: include only Weekdays (trading days) & exclude weekends & holidays in Event Study

LucyDang — Tue, 25 Dec 2018 02:26:16 GMT

Hi mkeintz,

How can I do as what you said:

If so, you could use the date variables to generate a stock trading day calendar. Then you could use it toe generate the (-180,1) trading day range for the event study.

I do have trading data for daily stock return.

Thank you so much!