In this example, the same analysis as in Measuring ERC (event study) is repeated using macros.

SAS Macros

The SAS macro language is based on text replacement, which can be capitalized on to automate similiar SAS code.

In this example, several macros are included:
- SignOn2WRDS: a small macro that signs in on the WRDS server
- addNumGroup: adds a counter 'by group' to a dataset
- compFundQ: collects variables from Compustat Fundamental Quarterly
- getKeys: collects permno, cusip, IBES ticker based on gvkey
- getIbes: retrieves analyst forecast and actual earnings for a quarter
- crspErdport1: computes cumulative stock return (raw and size adjusted) over some period
- winsor: winsorizes a dataset

Most of these macros have not been tested, so proceed with caution when you use them.

Feel free to discuss/add feedback to these macros in the forums on this site.

SAS code

This example consists of two files, one file with macros, and another one with the main script (invoking the macros). The file with the macros needs to be ran first (open the file in SAS, execute with F3).

Download example file (right-click and select "save target as").

Download macro file (right-click and select "save target as")

libname myLib5 "G:\research\sas_projects\learn_accounting_wrds\example 5 ERC macros\sasdata";
 
/* 	Sign on to WRDS server;*/
 
%SignOn2WRDS;
 
/* 	Collect Compustat-fundQ variables,
	retrieve earnings announcement dates (rdq) and end of quarter stock price (prccq) 
	for quarters in 2008; 
	in addition to vars (rdq and prccq), the macro will collect gvkey, datadate and fyr */
 
%compFundQ(outp=myLib5.a_rdq, key=key, year1=2008, year2=2008, vars=rdq prccq);
 
/* 	Drop obs with very late earnings announcements; */
 
data myLib5.a_rdq;
set myLib5.a_rdq;
if ((rdq - datadate) <=90);
run;
 
/*	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') 
 
	The macro will append the keys (lpermno, cusip and ibes_ticker) to the dataset provided */
 
%getKeys(dset=myLib5.a_rdq, outp=myLib5.b_withKeys, gvkey=gvkey, date=rdq);
 
 
/*	Next, collect:
	- the analyst earnings forecast, prior to the earnings announcement window (so, rdq-2)
	- the reported eps number (from ibes, more in line with 'core' earnings than Compustat earnings)
	- stock return and decile returns for the days around the earnings announcement 	
 
	For this macro, the forecast must have been issued prior to variable 'date' 
	(which is rdq, the earnings announcement date).
	Variable 'quarter' is the end of the quarter that the forecast must relate to.*/
 
%getIbes(dset=myLib5.b_withKeys, outp=myLib5.c_withIbes, date=rdq, ibes_ticker=ibes_ticker,
	quarter=datadate);
 
 
/*	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) 
 
 	Stock return over day rdq-1 through rdq+1 */
 
%crspErdport1(dset=myLib5.c_withIbes, outp=myLib5.d_withCAR, key=key, permno=lpermno, date1=rdq-1, 
	date2=rdq+1);
 
/* 	Rename variables created by macro (macro will be called again next for pre-announcement return 
	Drop the variables that are not used */
 
data myLib5.d_withCAR (drop = ret_raw ret_sizeadj dsfRnumDays capn);
set myLib5.d_withCAR;
car_ln = log(ret_sizeadj);
run;
 
 
/* 	Notice that the same macro is called again for the preannouncement return: 
	stock return over day STATPERS+1 (IBES date of analyst earnings forecast) through rdq -2 */
 
%crspErdport1(dset=myLib5.d_withCAR, outp=myLib5.d_withCAR, key=key, permno=lpermno, 
	date1=STATPERS+1, date2=rdq-2);
 
/* 	Preannouncement return is the log of ret_raw which equals 1 plus the daily returns*/
 
data myLib5.d_withCAR (drop = ret_raw ret_sizeadj dsfRnumDays);
set myLib5.d_withCAR;
preAnnRet_ln = log(ret_raw);	 
run;
 
/* 
	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; */
 
data myLib5.q_vars;
set  myLib5.d_withCAR;
if actual ne .;
if MEANEST ne .;
if prccq ne .;
if car_ln ne .;
if preAnnRet_ln ne .;
 
unex = (actual - MEANEST) / prccq;
loss = 0;
if actual < 0 then loss = 1;
loss_unex = loss * unex;
run;
 
/* 	Invoke macro to winsorize */
 
%winsor(dsetin=myLib5.q_vars, dsetout=myLib5.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 = myLib5.u_finalWinsorized; by capn;
 
/* 	Pooled OLS regression by size decile */
 
PROC REG OUTEST = myLib5.v_regOutput data=myLib5.u_finalWinsorized;
   ID capn;
   MODEL  car_ln = preAnnRet_ln unex loss loss_unex/ NOPRINT;
   by capn;
RUN ;

SAS code macros

/*
	macro's for example 5: ERC study using macro's
 
	Note: these macro's have not been thoroughly tested!!!!
	=======================================================
 
*/
 
/*	macro signOn
	shorthand for signing into wrds
*/
 
%macro SignOn2WRDS;
 
%let wrds = wrds.wharton.upenn.edu 4016;options comamid = TCP remote=WRDS;
signon username=_prompt_;
 
%mend;
 
**************************************************************************************;
 
/*	macro addNumGroup
	adds a column with the count of a group, useful for identifying double rows
*/
 
%macro addNumGroup( var, dset=_last_, outp=_last_ );
 
proc sql;
	create table work.addNum_temp as
 
	select &var, count(*) as num&var
	from
		&dset 
	group by &var;
quit;
 
proc sql;
 
	create table work.addNum_temp2 as
 
	select a.*, b.num&var
	
	from
		&dset a
	LEFT JOIN
		work.addNum_temp b
	on a.&var = b.&var;
 
quit;
 
data &outp;
set work.addNum_temp2;
run;
 
* clean up;
proc datasets library=work;
   delete addNum_temp1 - addNum_temp2;
run;
 
%mend addNumGroup;
 
**************************************************************************************;
 
/*	macro compFundQ
	- creates new dataset (no dataset as input)
	- gets vars from fundQ for year1 till year2
	- creates a key (gvkey || datadate)
	- drops obs that changed fiscal quarter
*/
 
%macro compFundQ(outp=, key=, year1=, year2=, vars=);
 
/* 	Push the macro variables to the remote server (without the next line the WRDS server 
	would not 'understand' &vars etc); */
 
%syslput vars=&vars;
%syslput year1=&year1;
%syslput year2=&year2;
 
rsubmit;
libname comp '/wrds/comp/sasdata/naa';
			 
/*
gvkey: Global Company Key 
fyr: month of fiscal year end (for example 12 for December)
datadate: Data Date 
*/
 
PROC SQL;
  create table fun (keep = gvkey fyr datadate &vars) as
  select a.* 
  from comp.fundq a
  	where 
		&year1 <= year(datadate) <= &year2
	and indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' ;
  quit;
 
proc download data=fun out=work.compFundQ_t1;run;
endrsubmit;
 
* drop firms with multiple entries (have switched fyr);
data work.compFundQ_t2;
set work.compFundQ_t1;
&key = gvkey || datadate;	* combinations of gvkey and datadate need to be unique;
run;
	
* add a variable with the number of observations for each key;
%addNumGroup(dset = work.compFundQ_t2, outp = work.compFundQ_t3, var=&key);
 
* only keep those observations that are unique (i.e., firms with multiple rows are excluded);
data &outp (drop = num&key);
set work.compFundQ_t3;
if num&key eq 1;run;
 
%mend compFundQ;
 
 
*****************************************************************************************;
 
/*	macro getKeys
	- retrieves permno, cusip and ibes_ticker based on gvkey
*/
 
%macro getKeys(dset=,outp=,gvkey=,date=);
 
%syslput dset=&dset;
%syslput outp=&outp;
%syslput gvkey=&gvkey;
%syslput date=&date;
	
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=&dset 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.&date >= b.LINKDT) or b.LINKDT = .B) and 
       ((a.&date <= b.LINKENDDT) or b.LINKENDDT = .E)	 ;
  quit;
 
/* 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.&date <= b.nameendt
	and b.ncusip ne "";
  quit;
 
/* 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.&date > b.SDATES
;
quit;
proc download data=ibesdata out=&outp;
run;
endrsubmit;
 
%mend;
 
*********************************************************************************************;
 
/*	macro getIbes
	- retrieves analyst forecast and actual earnings for a quarter
*/
 
%macro getIbes(dset=, outp=, ibes_ticker=, date=, quarter=);
 
%syslput dset=&dset;
%syslput outp=&outp;
%syslput ibes_ticker=&ibes_ticker;
%syslput date=&date;
%syslput quarter=&quarter;
 
/*
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=&dset 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.&date - 45 < b.STATPERS < a.&date -2
	and a.&quarter -5 <= b.FPEDATS <= a.&quarter +5
;
quit;
 
 
/* 	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 = ibesdata; by key STATPERS;run;
 
/* this dataset will only include the last estimate;*/
data ibesdataMostRecent;
set  ibesdata;
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 
 
*/
 
PROC SQL;
  create table ibesdata as
  select a.*, b.value as actual
  from ibesdataMostRecent a, ibes.ACTU_EPSUS  b
  where 
  		a.&ibes_ticker = b.ticker
	and	b.PENDS - 5 < a.&quarter < b.PENDS +5
	and b.MEASURE="EPS"
    and b.PDICITY = "QTR"
;
quit;
 
proc download data=ibesdata out=&outp;
run;
endrsubmit;
 
%mend;
*********************************************************************************************;
 
/*	macro crspErdport1
	- computes cumulative stock return (raw and size adjusted) over some period
*/
 
%macro crspErdport1(dset=, outp=, key=, permno=, date1=, date2=);
 
data work.crspR_temp1 (keep = key permno date1 date2);
set  &dset;
permno = &permno;
key = &key;
date1 = &date1;
date2 = &date2;
 
* date2 needs to be on or after date1;
if date2>= date1;		
 
run;
 
proc sort data = work.crspR_temp1 nodup; by key permno date1 ;run;
 
rsubmit;
libname crsp('/wrds/crsp/sasdata/cc' '/wrds/crsp/sasdata/ix' '/wrds/crsp/sasdata/sm'
	'/wrds/crsp/sasdata/sd');
 
proc upload data=work.crspR_temp1 out=j;run;
 
* note: some observations may be missing in erdport1 that are included in dsf 
	(daily stock file);
 
PROC SQL;
  create table tsrdata as
  select j.*, a.date, a.ret, a.decret as decile_ret, a.capn
  from crsp.erdport1 a, j
  where j.date1 <= a.date <= j.date2 and a.permno = j.permno;
 quit;
 
 
proc download data=tsrdata out=work.crspR_temp2;
run;
endrsubmit;
 
proc sort data = work.crspR_temp2; by key date;run;
 
* compute cumulative returns ;
data work.crspR_temp3 ;
set work.crspR_temp2;
by key;
retain counter ret_raw ret_sizeadj;
if first.key then counter=0;
if first.key then ret_raw=1;
if first.key then ret_sizeadj=1;
 
if ret eq . then ret = 0;
counter+1;
 
ret_sizeadj = ret_sizeadj + ret - decile_ret;
ret_raw     = ret_raw     + ret;
 
if last.key then output;
run;
 
proc sql;
 
	create table &outp as
 
	select a.*, b.ret_raw, b.ret_sizeadj, b.counter as dsfRnumDays, b.capn
 
	from
		&dset a
	LEFT JOIN
		work.crspR_temp3 b
	ON
		a.&key = b.key;
 
quit;
 
* clean up;
proc datasets library=work;
   delete crspR_temp1 - crspR_temp3;
run;
 
%mend;
 
 
 
*********************************************************************************************;
 
/*	macro winsor
	- winsorizes a dataset
*/
 
/*****************************************
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;

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