WRDS SAS Macro for creating Fama-French Size and Book-to-Market 2 by 3 portfolios using CRSP, Compustat, and CCM


/* ********************************************************************************* */
/* ************** W R D S   R E S E A R C H   A P P L I C A T I O N S ************** */
/* ********************************************************************************* */
/* Program   : SIZE_BM.SAS                                                           */
/* Summary   : Assign stocks into 6 Size-BM portfolios                               */
/* Date      : February 2008                                                         */ 
/* Author    : Denys Glushkov, WRDS                                                  */
/* Revision 1: Mar 2011 (Denys Glushkov)                                             */
/* Revision 2: Jan 2023 (Eunji Oh)                                                   */
/*                                                                                   */
/* Details   : Macro assigns the stocks into six Size-BM portfolios based on the     */
/*             methodology outlined on Ken French website at                         */
/*             http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/Data_Library   */
/*             /six_portfolios.html                                                  */ 
/*                                                                                   */
/* The size breakpoint for year t is the median NYSE market equity at the end of June*/ 
/* of year t. BE/ME for June of year t is the book equity for the last fiscal year   */
/* end in t-1 divided by ME for December of t-1. The BE/ME breakpoints are the 30th  */
/* and 70th NYSE percentiles.                                                        */
/*                                                                                   */
/* Parameters : - BDATE: Sample Start Date                                           */
/*              - EDATE: Sample End Date                                             */
/*              - Link : CCM linktable b/w CRSP Permno and Compustat GVKEY.          */
/*                       Link table must contain 'LPERMNO' for permno, 'GVKEY',      */
/*                       'LinkDt' for link start date,'LinkEndDt' for link end date  */
/*                       and                                                         */
/*                                                                                   */
/* To run the program, a user should have access to CRSP monthly stock,              */
/* Compustat Annual, and CRSP/Compustat Merged database                              */
/* ********************************************************************************* */

%MACRO SIZE_BM (bdate=, edate=, link=);
Step 1. Extract CRSP Data for NYSE and AMEX Common Stocks  
        Merge historical codes with CRSP Monthly Stock File      
        Restriction on Share Code: common shares only            
%let filtr = (shrcd in (10,11));         
* Selected variables from the CRSP Monthly Stock File;     
%let fvars =  prc ret shrout;                                
*  Selected variables from the CRSP Monthly Event File;
%let evars =  exchcd shrcd dlret;                            
* Modify beginning and ending dates;
%let begdate=intck('year',&bdate,-1); 

/* Invoke CRSPMERGE WRDS Research Macro. Data Output: CRSP_M */

data msex2;
   set crsp_m;
   by permno date;
   * Create size variable;
   size_lag=lag(size); *Lag Size for weights;
   ldate = lag(date);
   if first.permno then size_lag = size / (1+ret); 
   * Option for Delisting Returns;
   ret = sum(ret,dlret);
   * Comment previous line not to adjust for delisting events;
   if size > 0;
   drop prc shrout ldate;

Step 2. Assign Stocks to NYSE Size-Based groups 
proc sort data=msex2 (keep=date size exchcd) out=msex3;
   where month(date)=6 and exchcd=1;
   by date;

proc means data=msex3 noprint;
   var size;
   by date;
   output out=nyse (drop=_freq_ _type_) median=/autoname;

proc sql;
   create table size_assign
   as select a.permno, a.date, a.size,
	case when size <= size_median then 'Small' else 'Big'
		end as size_port
   from msex2 (keep=permno date size where = (month(date)=6)) as a
   left join nyse as b
   on a.date= b.date;

2. Create Book Equity(BE) measure 
from Compustat (definition from Daniel and Titman (JF, 2006)
"Market Reactions to Tangible and Intangible Information"
data comp_extract;
   set comp.funda 
   (where=(fyr > 0 and at > 0 and consol='C' and 
           indfmt='INDL' and datafmt='STD' and popsrc='D'));
   if missing(SEQ)=0 then she=SEQ;else
   if missing(CEQ)=0 and missing(PSTK)=0 then she=CEQ+PSTK;else
   if missing(AT)=0 and missing(LT)=0 and missing(MIB)=0 then she=AT-(LT+MIB);
   else she=.;
   if missing(PSTKRV)=0 then BE0=she-PSTKRV;else 
   if missing(PSTKL)=0 then BE0=she-PSTKL; else 
   if missing(PSTK)=0 then BE0=she-PSTK; else BE0=.;
   * Converts fiscal year into calendar year data;
   if (1 <= fyr <= 5) then date_fyend=intnx('month',mdy(fyr,1,fyear+1),0,'end');
   else if (6 <= fyr <= 12) then date_fyend=intnx('month',mdy(fyr,1,fyear),0,'end');
   format date_fyend date9.;
 * Accounting data since calendar year 't-1';
   if (year(date_fyend) >= year(&bdate) - 1) 
      and (year(date_fyend) <= year(&edate) + 1);
   keep gvkey calyear fyr BE0 date_fyend indfmt consol datafmt popsrc datadate TXDITC;

proc sql; 
  create table comp_extract
   as   select a.gvkey, a.calyear, a.fyr, a.date_fyend, 
        case when missing(TXDITC)=0 and missing(PRBA)=0 then BE0+TXDITC-PRBA else BE0
	end as BE
   from comp_extract a left join 
        comp.aco_pnfnda (keep=gvkey indfmt consol datafmt popsrc datadate prba) b
   on   a.gvkey=b.gvkey and a.indfmt=b.indfmt and 
        a.consol=b.consol and a.datafmt=b.datafmt and 
        a.popsrc=b.popsrc and a.datadate=b.datadate;

Step 3. Create Book to Market (BM) ratios at December 
proc sql;
   create table BM0	(where=(BM>0))
   as select a.gvkey, a.calyear, c.permno, c.exchcd, c.date, 
   a.be/(abs(c.prc)*c.shrout/1000) as BM
   from comp_extract as a, 
		&link as b,		
		crsp_m (where=( month(date)=12)) as c
	where a.gvkey=b.gvkey and 
        ((b.linkdt <= c.date <= b.linkenddt) or 
	 (b.linkdt<=c.date and b.linkenddt=.E) or
         (c.date <= b.linkenddt and b.linkdt=.B)) and b.lpermno=c.permno
   and a.calyear = year(c.date) and (abs(c.prc)*c.shrout)>0;

4. Keep only those cases with valid stock market in June 
proc sql;
   create table BM
   as select a.gvkey, a.permno, a.bm, a.calyear, a.date as decdate, 
	     a.exchcd, b.date, b.size, b.size_port
   from BM0 as a, size_assign as b
   where a.permno=b.permno
   and intck('month',a.date,b.date)=6 and b.size>0;

5. Assign stocks to NYSE BM-based groups 
proc sort data=BM out=nyse1 (keep=permno bm calyear decdate);
   where exchcd=1;
   by decdate;

proc univariate data=nyse1 noprint;
   var bm;
   by decdate;
   output out=nyse2 pctlpts = 30 70 pctlpre=per;

*Merge back with master file that contains all securities 
from NYSE, Nasdaq and AMEX;
proc sql;
   create table bm1
   as select a.permno, a.gvkey, a.bm, a.size, a.size_port, a.date, a.decdate,
   case when bm <= per30 then 'Low'
   		when per30 < bm <= per70 then 'Medium'
		else 'High' 
		end as bm_port
   from BM as a, nyse2 as b
   where a.decdate=b.decdate;
/* The 'date' variable refers to June, whereas                */
/* 'decdate' variable refers to December of the previous year */

proc sort data=bm1; by permno descending date;run;

data size_bm_port; set bm1;
by permno descending date;
if first.permno then leaddate=intnx('month',date,-12,'end');
format date leaddate decdate date9.;
rename date=size_date decdate=bm_date;
label date='Valid date for firm size';
label decdate='Valid date for Book-to-Market';

proc sort data=size_bm_port; by permno size_date;run;

proc sql; drop table nyse1, nyse2, nyse, size_assign, 
		msex2, msex3, bm, bm0, bm1, comp_extract;

/* ********************************************************************************* */
/* *************  Material Copyright Wharton Research Data Services  *************** */
/* ****************************** All Rights Reserved ****************************** */
/* ********************************************************************************* */

Top of Section