Dates for Event Study

[mig7126]

Hello,

I have an issue on how to figure out the SAS code for an event study that I am doing in SAS.  Specifically, I am running an event study on the 30 stocks of the Dow Jones Industrial Avg., from January 2000 to December 31, 2015.  The event study is related to the abnormal returns from unexpected earnings, or earnings surprises.  Therefore, I have done the following:

(1) I have created a data table with the daily stock and market index return data from CRSP for the 30 different stocks in the Dow Jones Industrial Avg. from January 3, 2000 to December 31, 2015.  The data table contains CUSIP, DATE, RET (Holding period return), and EWRETD (Equal Weighted Mkt Return incl. Dividend)

(2) I also created a table with the quarterly earnings data (eps diluted excl. extraordinary items) from Compustat for the 30 different stocks from January 2000 to December 2015.  The Compustat file has the following variables: CUSIP, EPSFXQ (eps for the quarter excl. extraordinary items), and RDQ (Report date for quarterly earnings - this will be my event date)

(3) To calculate unexpected earnings (UEPS), I am doing it in a very simple manner - specifically, I am taking the difference between the eps from quarters t and t-1. I know that this could pose issues if there is a strong seasonal variation in earnings, but for now, this is the easiest way. 

(4) Therefore, I modified the Compustat data to include UEPS, and deleted all records where UEPS is equal to zero.  Therefore, I am only keeping the records where there is unexpected earnings (either greater than zero or less than zero).  This table effectively contains my Event Dates, which will be the RDQ for all records where UEPS is not equal to zero.

Now, comes the hard part, and this is where I need help with the SAS code.  For each CUSIP and RDQ (Event Date) from my modified Compustat data table, I want to create dates that are 90 days before the Event Date, and 10 days after the Event Date -- therefore, effectively, I want to have 101 days for each Event Date and CUSIP combination.  Then, I want to merge this data with my CRSP stock and market index return data.  Specifically, here are some sample data, and what I want to see as the final outcome:

(A) CRSP Stock and Mkt Return Daily Data

DATE                CUSIP                  RET                 EWRETD

20000103         19121610             -0.032189         0.002878

20000103         25468710              0.021368         0.002878

20000103         26353410             -0.013283         0.002878

20000104         19121610             -0.008869        -0.017466

20000104         25468710              0.062762        -0.017466

20000104         26353410              0.000000        -0.017466

(B) Compustat Modified Data

CUSIP                RDQ                     EPSFXQ             UEPS

19121610           20000719             0.37                     0.39

19121610           20001020             0.43                     0.06

19121610           20010131             0.10                     -0.33

25468710           20000803             0.21                     0.13

25468710           20001109             0.11                    -0.10

(C) I want to create a file that has the following resulting data:

CUSIP                RDQ                   DATE                 RET                EWRETD

19121610           20000719          20000419          0.0310              0.00145

19121610           20000719          20000420          0.0025             -0.02432

19121610           20000719          20000421         -0.0119              0.02501

19121610           20000719          20000422          0.0222              0.00541

Therefore, for each CUSIP and RDQ combination, I want to have daily stock return and market index return for 90 days before the RDQ (Event Date) and 10 days after the RDQ (Event Date).

Can someone please help me with the SAS code to do this?  Since I am a beginner in SAS, it would be great if someone could show me the code without using PROC SQL.

Thanks everyone!

[Kurt_Bremser · Accepted solution]

proc sort data=crsp;
by cusip date;
run;

data intermediate;
format date yymmddn8.;
set compustat (keep=cusip rdq);
do date = rdq - 90 to rdq + 10;
  output;
end;
run;

proc sort data=intermediate;
by cusip date;
run;

data want;
merge
  intermediate (in=a)
  crsp (in=b)
;
by cusip date;
if a and b;
run;

proc sort data=want;
by cusip rdq date;
run;

Please try this and report if it works.

Edit: fixed minor issues/typos after test.

[mig7126]

Thanks! It works perfectly!

[factorhedge]

Hi Kurt

Thanks for the code , I have a similar problem on similar dataset, however I need to create the event window based on the trading days for e.g. I want + 5 and -5 trading days if I just use rdq - 5 to rdq + 5 then it will include non-trading days. Is there a dirty way to find it ? One possible solution I was thinking was to create a counter for day of the week 1 = Monday , 2 = Tues and so on and if an event occurs on 6th day add + 2 and 7th day add +1. Could you please help me?