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!
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.
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.
Thanks! It works perfectly!
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: