In this example another event study is performed. In particular, (abnormal) stock return is regressed on unexpected earnings to find the markets' response for each 'unit' of unexpected earnings. The most common data sources (CRSP, Compustat as well as IBES) are used in this example.

You will need a login for WRDS in order to retrieve the required data.

Earnings Response Coefficient

The earnings response coefficient (ERC) is the stock market reaction (the change in stock price) for one unit of unexpected earnings. This can be measured around the earnings announcement (event study), or over a longer period, for example a year (association study).

In this example the stock market's reaction measured around the announcement date is regressed against unexpected earnings, where the unexpected earnings are measured as the difference between reported earnings per share and the most recent IBES consensus forecast. The earnings announcements in 2008 are included in the dataset.

The stock return between the data of the analyst earnings forecast up to the day prior to the event window is included as a control variable. This variable is correlated with the measurement error in unexpected earnings. (Why? if good news is released after the analyst earnings forecast date, but before the earnings announcement, then stock price increases, and also the 'implicit' market expectation of earnings increases. The analyst earnings expectation used is not updated, so, there is measurement error in unexpected earnings (being the actual reported earnings minus the analyst earnings forecast).

Matching Compustat-CRSP-IBES

In this example, the main datasets on WRDS are used. Compustat is used as the starting point (fundq contains 'RDQ' which is the earnings announcement date). GVKEY of compustat is matched with CCM (Compustat-CRSP merged) to obtain (historical) permno. Historical permno is used to match with crsp.dsenames to obtain (historical) CUSIP, which is used to match against ibes.idsum to get the IBES ticker (which is not the same as the 'regular' stock ticker).

WRDS has an alternative approach to match Compustat/CRSP with IBES, see the WRDS website: Home -> Support -> Data -> Sample Programs -> I/B/E/S -> IBES Sample Programs -> iclink.sas.

Reported earnings

Obviously, reported earnings are included in Compustat. However, also IBES includes reported earnings per share. The IBES actuals are adjusted ('street' measures of earnings) so that they are a better benchmark to compute unexpected earnings than the actual earnings reported in Compustat.

CRSP DSF versus Erdport1?

In this example, the abnormal stock return is computed as the difference between the (raw) return and the decile stock return (meaning, the average stock return that day for firms with similar market cap). Erdport1 carries fewer firms than the DSF (daily stock file). If you want to maximize the number of firms in your sample, then use DSF instead. This requires to construct size deciles and computing size decile returns. Queries on Erdport1 are executed faster than DSF (which probably is more popular, resulting in long execution time).

If you use rsubmit and get a 'timeout' on DSF, it is an option to download the DSF to the local computer. (Note: the full DSF is 13 gigabyte, so it is an idea to only download the relevant period and needed variables.)

SAS code

The SAS code illustrates the following:
- selecting the earnings announcement date from Compustat
- using the CRSP-Compustat link table to match Compustat with CRSP
- using CRSP-dsenames to retrieve the historical CUSIP
- retrieving analyst earnings forecasts and actual earnings from IBES using the historical CUSIP
- using size deciles to compute CAR as the size adjusted stock return
- running the regression for each size decile

Download (right-click and select "save target as") the SAS code below.

libname myLib3 "G:\research\sas_projects\learn_accounting_wrds\example 3 ERC\sasdata";
 
/* retrieve all earnings announcement dates in 2008 */
%let wrds = wrds.wharton.upenn.edu 4016;options comamid = TCP remote=WRDS;
signon username=_prompt_;
 
/* statements after 'rsubmit' are processed on wrds server;*/
rsubmit;
 
/* libname assignments: look in directory '/wrds/comp/sasdata/naa' for datasets in 'comp' 
('comp' could be anything); */
libname comp '/wrds/comp/sasdata/naa';
			 
/*
retrieve these variables:
gvkey: Global Company Key 
rdq: Report Date of Quarterly Earnings
datadate: Data Date (end of calendar quarter)
prccq: Price Close - Quarter 
 
the piece "and indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' " uniquely 
identifies firm years, without this line many
observations would be included more than once.
*/
 
PROC SQL;
  create table rdq(keep = gvkey rdq datadate prccq) as
  select a.* 
  from comp.fundq a
  	where year(rdq) = 2008
		and indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' ;
  quit;
 
/* download the newly created dataset 'rdq' to the local machine;*/
proc download data=rdq out=myLib3.a_rdq;run;
 
endrsubmit;
 
 
/*It is good practice is to create a 'key' that uniquely identifies observations. in this case a 
combination of a firm identifier (gvkey) and fiscal quarter (datadate) makes sense;*/
 
data myLib3.b_rdq;
set  myLib3.a_rdq;
 
/*when browsing the dataset, the 'key' looks odd, since the datadate is shown as a number;*/
key = gvkey || "_" || datadate;
 
/*let's keep only those firmquarters that were reported within 90 days after quarter's end;*/
if ((rdq - datadate) <=90);
 
run;
 
/* companies that change their fiscal year end during the year will have multiple entries in fundq.
one way to deal with this is excluding these observations all together.
In other words, the 'key' created above needs to occur once for a firm.;
 
First, create a table with 'key' and the number of occurences;
*/
proc sql;
	create table myLib3.c_countKeys as
 
	select key, count(*) as numKey
	from
		myLib3.b_rdq
	group by key;
quit;
 
/* take dataset a_rdq, but only keep the observations where numKey in b_countKeys is 1;*/
 
proc sql;
 
	create table myLib3.d_uniqueObs as
 
	select a.*
	from
		myLib3.b_rdq a,
		myLib3.c_countKeys b
	where
		a.key = b.key
	and b.numKey = 1;
 
quit;
 
/*
	collect firm identifiers:
	- get permno on CCM (compustat-crsp merged) (it is called 'lpermno')
	- get historic cusip on crsp.dsenames (it is called 'ncusip')
	- get ibes ticker on ibes.idsum (it is called 'ticker')
 
*/
	
 
rsubmit;
libname crsp ('/wrds/crsp/sasdata/cc' '/wrds/crsp/sasdata/sd');
libname ibes '/wrds/ibes/sasdata';
 
/* bring local file to wrds server;*/
proc upload data=myLib3.d_uniqueObs out=getThese;
 
/* match with compustat-crsp merged to retrieve PERMNO 
 
CC merged has a linkdt and linkenddt for which the record is valid 
linkdt: First Effective Date of Link 
linkenddt: Last Effective Date of Link 
 
announcement date (rdq) must be between linkdt and linkenddt: a.linkdt <= b.rdq <= linkenddt
usually linkdt and linkenddt is a date, but linkdt can be 'B' (beginning) and linkenddt 
can be 'E' (end).
 
linkprim: Primary issue marker for the link. Based on
Compustat Primary/Joiner flag (PRIMISS), indicating
whether this link is to Compustat?s marked primary
security during this range. ("C" and "P" indicate primarly links)
Not inlcuding "and a.linkprim IN ("C", "P") " will give some double observations (meaning, primary 
and secondary securities are included (for example,
class A and class B shares). This can be overcome by including a single security. 
On the other hand, including the line will prevent such double securities, but (somehow) some firms 
will not end up in the sample. 
*/
 
PROC SQL;
  create table ccMerged as
  select a.*, b.lpermno
  from getThese a, crsp.ccmxpf_linktable b
  	where a.gvkey = b.gvkey
	and b.lpermno ne .
	and b.linktype in ("LC" "LN" "LU" "LX" "LD" "LS")
	and b.linkprim IN ("C", "P") 
	and ((a.rdq >= b.LINKDT) or b.LINKDT = .B) and 
       ((a.rdq <= b.LINKENDDT) or b.LINKENDDT = .E)	 ;
  quit;
 
/* downloading file for testing/debugging (is not necessary, since the next step is on WRDS also);*/
proc download data=ccMerged out=myLib3.e_ccMerged;
 
/* match with dsenames for historic cusip (NCUSIP) ;*/
 
PROC SQL;
  create table dsenames as
  select a.*, b.ncusip
  from ccMerged a, crsp.dsenames b
  where 
  		a.lpermno = b.PERMNO
	and b.namedt <= a.datadate <= b.nameendt
	and b.ncusip ne "";
  quit;
 
/* downloading file for testing/debugging (is not necessary, since the next step is on WRDS also);*/
proc download data=dsenames out=myLib3.f_dsenames;run;
 
/* match with ibes: Company Identification (idsum)*/
 
PROC SQL;
  create table ibesdata as
  select distinct a.*, b.ticker as ibes_ticker
  from dsenames a, ibes.idsum b
  where 
  		a.NCUSIP = b.CUSIP
	and a.rdq > b.SDATES 
;
quit;
proc download data=ibesdata out=myLib3.g_withIbes;
run;
endrsubmit;
 
/*
	now we have the dates of the earnings announcements ('rdq') and all firm identifiers
 
	next, collect:
	- the analyst earnings forecast, prior to the earnings announcement window (so, rdq-2)
	- the reported eps number (from ibes, which is 'cleared' from non-recurring items)
	- stock return and decile returns for the days around the earnings announcement
 
*/
 
 
/*
IBES estimates:
STATPERS: I/B/E/S Statistical Period (date of the forecast)
FPEDATS: Forecast Period End Date (needs to equal datadate; the end of the quarter)
meanest: Mean Estimate
ACTUAL: Actual Value, from the Detail Actuals File 
FPI: Forecast Period Indicator (is 6 for the coming quarter)
*/
 
rsubmit;
libname ibes '/wrds/ibes/sasdata';
 
proc upload data=myLib3.g_withibes out=getIbes;
 
PROC SQL;
  create table ibesdata as
  select a.*, b.STATPERS, b.MEANEST
  from getIbes a, ibes.STATSUMU_EPSUS   b
  where 
  		a.ibes_ticker = b.ticker
	and b.MEASURE="EPS"
	and b.FISCALP="QTR"
	and b.FPI = "6"
	and a.rdq - 45 < b.STATPERS < a.rdq -2
	and a.datadate -5 <= b.FPEDATS <= a.datadate +5
;
quit;
proc download data=ibesdata out=myLib3.h_ibesEstimate;
run;
endrsubmit;
 
/* statsumu has a single 'summary' consensus number for each month
  since we required the consensus number to be before the rdq date, we need to take the last one;*/
 
proc sort data = myLib3.h_ibesEstimate; by key STATPERS;run;
 
/* this dataset will only include the last estimate;*/
data myLib3.i_ibesEstimateMostRecent;
set  myLib3.h_ibesEstimate;
by key;
if last.key then output;run;
 
/*
IBES actuals:
 
5 PENDS Num 8 Period End Date, SAS Format 
6 MEASURE Char 3 Measure Data Type Indicator 
7 PDICITY Char 3 Periodicity 
8 VALUE Num 8 Actual Value 
 
*/
 
rsubmit;
libname ibes '/wrds/ibes/sasdata';
 
proc upload data=myLib3.i_ibesEstimateMostRecent out=getIbes;
 
PROC SQL;
  create table ibesdata as
  select a.*, b.value as actual
  from getIbes a, ibes.ACTU_EPSUS  b
  where 
  		a.ibes_ticker = b.ticker
	and	b.PENDS - 5 < a.datadate < b.PENDS +5
	and b.MEASURE="EPS"
    and b.PDICITY = "QTR"
;
quit;
 
proc download data=ibesdata out=myLib3.j_ibesActuals;
run;
endrsubmit;
 
/*
	create variables for the market reaction:
	- cumulate (abnormal) return over event window
 	- stock return leading up to the event (return between date of analyst forecast
	and 2 days before event window)
 
 
*/
 
rsubmit;
 
 
libname crsp '/wrds/crsp/sasdata/ix' ;
 
proc upload data=myLib3.j_ibesActuals out=getDecile;run;
 
/*
get stock return and size portfolio return for days -1 through +1 around the earning 
announcement (rdq)
dataset:  Year-end Cap. Deciles with Daily Returns - NYSE/AMEX/NASDAQ (erdport1);
PERMNO: PERMNO 
RET: Returns 
date: Calendar Date 
capn: Year End Capitalization Portfolio Assignment 
decret: Decile Return 
 
*/
 
PROC SQL;
  create table decileData as
  select a.*, b.date, b.ret, b.decret, capn
  from 
	getDecile a, crsp.erdport1  b
  where 
		b.date-1 <= a.rdq <= b.date+1
	and a.lpermno = b.permno;
  quit;
 
proc download data=decileData out=myLib3.k_decileData;
run;
endrsubmit;
 
proc sort data = myLib3.k_decileData; by key date;run;
 
 
/* compute cumulative abnormal return (car), computed as size adjusted abnormal returns;
i.e. the firm's return over the three days minus the return of firms with a similar size 
over the same period */
 
data myLib3.l_CAR (drop = date ret decret);
set myLib3.k_decileData;
by key;
/* retain means that the contents of these variables will be remembered over the observations 
(as every observations is the return for 1 day, and it is needed to sum these over 3 days)
*/
retain car;
if first.key then car=1;
 
/* the cumulative return increases with stock return, the decile return is subtracted;*/
car = car + ret - decret;
 
/* we are only interested in keeping the cumulative 3 day return (and not 
cumulative 1 and 2 day) */
if last.key then output;
run;
 
 
/* compute return between the IBES date and two days before earnings announcement
this is the period in which new information affect the stock price before the event window,
while it is not reflected in the analyst earnings expectation
 
* create a dataset with the minimal variables, then upload this dataset and compute the 
pre-announcement return,then merge back with the l_car. this keeps the intermediate datasets 
smaller (mainly an issue with large datasets);
*/
 
data myLib3.m_keys (keep = key lpermno STATPERS rdq);
set myLib3.l_car;
run;
 
rsubmit;
libname crsp ('/wrds/crsp/sasdata/sd' '/wrds/crsp/sasdata/ix') ;
 
proc upload data=myLib3.m_keys out=getReturns;run;
 
/* collect stock return for each day between the date of the analyst earnings forecast and the 
start of the event window;*/
PROC SQL;
  create table returnData as
  select a.*, b.date, b.ret
  from getReturns a, crsp.erdport1  b
  where  
		a.STATPERS+1 <= b.date <= a.rdq -2
	and a.lpermno = b.permno;
  quit;
 
proc sort data = returnData nodup;by key date;
proc download data=returnData out=myLib3.n_returnData;
run;
endrsubmit;
 
/* missing and invalid returns are dropped;*/
data myLib3.n_returnData;
set myLib3.n_returnData;
if RET gt -55;		* missing: -66, -77, -88 etc;
if (1*RET eq RET) ;	* must be numeric;
run;
 
proc sort data = myLib3.n_returnData; by key date;
 
/* calculate the cumulative pre announcement return, only keep the last record for each 'key';*/
data myLib3.o_preAnnounceRet;
set  myLib3.n_returnData;
by key;
retain preAnnRet;
if first.key then preAnnRet = 1;
preAnnRet = preAnnRet* (1+ret);
if last.key then output;
run;
 
/* merge l_car with pre-announcement return, the resulting set will have all 
data required to compute final variables;*/
 
proc sql;
	create table myLib3.p_dataset as
	select a.*, b.preAnnRet
	from
		myLib3.l_car a,
		myLib3.o_preAnnounceRet b
	where
		a.key = b.key;
 
quit;
 
/* 
	compute unexpected earnings (unex) as actual eps minus analyst forecast, deflated by stock
	price at end of quarter;
	also create indicator variable for losses and interaction with loss and unex;
	take the natural log of the return variables
*/
data myLib3.q_vars;
set  myLib3.p_dataset;
if actual ne .;
if MEANEST ne .;
if prccq ne .;
if car ne .;
if preAnnRet ne .;
 
unex = (actual - MEANEST) / prccq;
loss = 0;
if actual < 0 then loss = 1;
loss_unex = loss * unex;
car_ln = log(car);
preAnnRet_ln = log(preAnnRet);
run;
 
 
 
/*****************************************
Trim or winsorize macro
* byvar = none for no byvar;
* type  = delete/winsor (delete will trim, winsor will winsorize;
*dsetin = dataset to winsorize/trim;
*dsetout = dataset to output with winsorized/trimmed values;
*byvar = subsetting variables to winsorize/trim on;
****************************************/
 
%macro winsor(dsetin=, dsetout=, byvar=none, vars=, type=winsor, pctl=1 99);
 
%if &dsetout = %then %let dsetout = &dsetin;
    
%let varL=;
%let varH=;
%let xn=1;
 
%do %until ( %scan(&vars,&xn)= );
    %let token = %scan(&vars,&xn);
    %let varL = &varL &token.L;
    %let varH = &varH &token.H;
    %let xn=%EVAL(&xn + 1);
%end;
 
%let xn=%eval(&xn-1);
 
data xtemp;
    set &dsetin;
    run;
 
%if &byvar = none %then %do;
 
    data xtemp;
        set xtemp;
        xbyvar = 1;
        run;
 
    %let byvar = xbyvar;
 
%end;
 
proc sort data = xtemp;
    by &byvar;
    run;
 
proc univariate data = xtemp noprint;
    by &byvar;
    var &vars;
    output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H;
    run;
 
data &dsetout;
    merge xtemp xtemp_pctl;
    by &byvar;
    array trimvars{&xn} &vars;
    array trimvarl{&xn} &varL;
    array trimvarh{&xn} &varH;
 
    do xi = 1 to dim(trimvars);
 
        %if &type = winsor %then %do;
            if not missing(trimvars{xi}) then do;
              if (trimvars{xi} < trimvarl{xi}) then trimvars{xi} = trimvarl{xi};
              if (trimvars{xi} > trimvarh{xi}) then trimvars{xi} = trimvarh{xi};
            end;
        %end;
 
        %else %do;
            if not missing(trimvars{xi}) then do;
              if (trimvars{xi} < trimvarl{xi}) then delete;
              if (trimvars{xi} > trimvarh{xi}) then delete;
            end;
        %end;
 
    end;
    drop &varL &varH xbyvar xi;
    run;
 
%mend winsor;
 
/* invoke macro to winsorize */
%winsor(dsetin=myLib3.q_vars, dsetout=myLib3.u_finalWinsorized , byvar=none, 
vars= car_ln preAnnRet_ln unex loss_unex, type=winsor, pctl=1 99);
 
/* capn is capitalization decile (from crsp.erdport1);*/
proc sort data = myLib3.u_finalWinsorized; by capn;
 
/* regression by size decile */
PROC REG OUTEST = myLib3.v_regOutput data=myLib3.u_finalWinsorized;
   ID capn;
   MODEL  car_ln = preAnnRet_ln unex loss loss_unex/ NOPRINT;
   by capn;
RUN ;

All rights reserved. © 2010-2014 wrds.us [Copyright] [Privacy Statement] [Disclaimer] [About]