/* This SAS code documents the steps for creating the Final Dataset used in the paper: */

/* Short-Term Institutions, Analyst Recommendations, and Mispricing: The Role of Higher-Order Beliefs by Cremers, Pareek, and Sautner */


/* STEP 1: Calculate fund turnover variable (here: fundturnlen, in the paper: FUNDTURNOVER) from 13F institutional holdings data. Requires several sub-steps. */


/* STEP 1a: Use Institutional Investor 13F Holdings data file from WRDS named “Holdings_type” */

/* Require monthly CRSP Returns data file named “crspdata” from WRDS with cusip permno date shrcd prc ret shrout, where shrcd is the share code, prc is the stock price, ret is the monthly holding period return and shrout is the number of shares outstanding (in thousands) */

data crsp; set crsp.crspdata;
month=month(date);
if month(date)=3 or month(date)=6 or month(date)=9 or month(date)=12;
rqdate = intnx('quarter',date,0,'end');
if SHRCD=10 or SHRCD=11; 
if ret >-1;    	    /*Delete observations with return less than -100%*/
if prc=. then delete; /*Delete observations with missing or negative price*/
if prc le 0 then delete;
run;

data test; set Holdings.Holdings_type;
rqdate = intnx('quarter',rdate,0,'end');
run;

proc sort data=test; by permno rqdate; run;
proc sort data=crsp; by permno rqdate; run;


/* STEP 1b: Exclude stocks that are missing in the CRSP database */

data test;
merge crsp(in=incrsp) test(in=intest);
by permno rqdate;
if incrsp=1 and intest=1;
run;


/* STEP 1c: Create qtrno2, an index variable for quarter numbers (e.g., for 1980 1st quarter qtrno2=1, for 2006 4th quarter qtrno2=108); value, the dollar value of the holdings in $ million; and pctshares, the portfolio weight of the institution in a stock*/

data test;set test;
value=(shares*prc)/1000000; 
qtrno2 = (year(rqdate)-1980)*4 + qtr(rqdate);
pctshares= shares/(10*shrout);
rename mgrno=fundno;
if shares ~=. and shrout ~=. and cusip~="";
keep cusip permno mgrno shrout prc rqdate rdate value  qtrno2 pctshares shares;
run;


/* STEP 1d: Calculate the last quarter for a fund. This step helps in eliminating the quarter generated beyond the last quarter in the sample when we fill in the gaps for institutions that report semiannually*/

proc sort data=test; by fundno qtrno2; run;
data maxqtr; set test;
by fundno qtrno2;
if last.fundno;
run;
data maxqtr; set maxqtr;
rename qtrno2=maxqtr;
keep fundno qtrno2;
run;

/* STEP 1e: Fill in the gaps when institutions report semiannually */

data fundqtr; set test;
keep fundno qtrno2; run;
proc sort data=fundqtr nodup; by fundno descending qtrno2; run;
data fundqtr; set fundqtr;
by fundno descending qtrno2;
leadqtrno2 = lag1(qtrno2);
if first.fundno then leadqtrno2=.;
run;
data fundqtr; set fundqtr;
if leadqtrno2-qtrno2=2;
if leadqtrno2~=.;
run;

proc sort data=test; by fundno qtrno2; run;
proc sort data=fundqtr; by fundno qtrno2; run;
data test2;
merge test(in=in1) fundqtr(in=in2);
by fundno qtrno2;
 if in1=1 and in2=1;
 run;

data test2; set test2;
 qtrno2= qtrno2+1;
  semiannual=1;
 run;
data testchange;
 set test test2;
 run;

/* STEP 1f: Calculate lagged holdings, percentage holdings, and value (here shares2, pctshares2, value2) as inputs to later calculate turnover at the institution level (here: fundturnover_inst) */

data pctchange; set testchange;
keep qtrno2 permno fundno pctshares2 shares2 lastshrout value2;
qtrno2 = qtrno2+1;
pctshares2=pctshares;
shares2=shares;
lastshrout = shrout;
value2=value;
run;

proc sort data=testchange; by qtrno2 permno fundno; run;
proc sort data=pctchange; by qtrno2 permno fundno; run;
data testchange;
merge testchange pctchange;
by qtrno2 permno fundno;
run;

data testchange; set testchange;
if pctshares=. then pctshares=0;
if pctshares2=. then pctshares2=0;
if shares=. then shares=0;
if shares2 =. then shares2=0;
if shrout=. then shrout=lastshrout;
if lastshrout=. then lastshrout=shrout;
if value2=. then value2=0;
run;

proc sort data=testchange; by fundno; run;
proc sort data=maxqtr; by fundno; run;
data testchange;
merge maxqtr(in=in1) testchange(in=in2);
by fundno;
if in1=1 and in2=1;
run;

data testinst; set testchange;
if qtrno2<=maxqtr;
drop leadqtrno2 rdate rqdate prc maxqtr cusip;
run;

data testinst; set testinst;
if shares=. then shares=0;
if shares2=. then shares2=0;
if value=. then value=0;
if value2=. then value2=0;
keep qtrno2 fundno permno shares shares2 value value2 semiannual;
run;

/* STEP 1g: Calculate turnover at the institution level (here: fundturnover_inst) as an input to later calculate fund turnover */
	
data crsp; set crsp.crspdata;
qtrno2=(year(date)-1980)*4+qtr(date);
if month(date)=3 or month(date)=6 or month(date)=9 or month(date)=12;
keep qtrno2 permno prc;
if prc>=0;
if prc~=.;
run;

proc sort data=testinst; by qtrno2 permno; run;
proc sort data=crsp; by qtrno2 permno; run;
data testinst;
merge testinst(in=in1) crsp(in=in2);
by qtrno2 permno;
if in1=1 and in2=1;
run;

data testinst; set testinst;
num = (abs(shares-shares2))*prc;
den = (1000000*(value+value2))/2;
run;

proc sort data=testinst; by fundno qtrno2 semiannual; run;
proc means data=testinst noprint;
by fundno qtrno2 semiannual;
output out=fundturnover_inst sum(num)=sumnum sum(den)=sumden;
run;
data fundturnover_inst; set fundturnover_inst;
fundturnover = sumnum/sumden;
keep qtrno2 fundno fundturnover semiannual;
run;

data fundturnover_inst; set fundturnover_inst;
if semiannual =1 then fundturnover=.; 
run;


proc sort data=fundturnover_inst; by fundno qtrno2; run;
data fundturnover_inst; set fundturnover_inst;
by fundno qtrno2;
lagturn1 = lag(fundturnover);
if first.fundno then lagturn1=.;
run;

proc sort data=fundturnover_inst; by fundno qtrno2; run;
data fundturnover_inst; set fundturnover_inst;
by fundno qtrno2;
lagturn2 = lag(lagturn1);
if first.fundno then lagturn2=.;
run;
proc sort data=fundturnover_inst; by fundno qtrno2; run;
data fundturnover_inst; set fundturnover_inst;
by fundno qtrno2;
lagturn3 = lag(lagturn2);
if first.fundno then lagturn3=.;
run;

data fundturnover_inst; set fundturnover_inst;
meanfundturn= mean(fundturnover,lagturn1,lagturn2,lagturn3); /*Calculate the mean turnover over last four quarters at the institution level (here: meanfundturn) */
run;

data fundturnover_inst; set fundturnover_inst;
keep qtrno2 fundno meanfundturn;
if meanfundturn~=.;
if qtrno2~=.;
if fundno~=.;
run;

proc sort data=fundturnover_inst; by qtrno2; run;
proc rank data=fundturnover_inst groups=3 out=fundturnover_inst;
by qtrno2;
var meanfundturn;
ranks turnrank;
run;

/* STEP 1g: Calculate fund turnover at the stock level (here: fundturnlen, in the paper: FUNTURNOVER) */

data testinst2; set testinst;
keep qtrno2 permno fundno shares;
run;

proc sort data=fundturnover_inst; by qtrno2 fundno; run;
proc sort data= testinst2; by qtrno2 fundno; run;
data testinst2;
merge testinst2(in=in1) fundturnover_inst(in=in2);
by qtrno2 fundno;
if in1=1 and in2=1;
run;

data testinst2; set testinst2;
fundturnshares = meanfundturn*shares;
keep qtrno2 fundno permno fundturnshares shares;
run;

proc sort data=testinst2; by qtrno2 permno; run;
proc means data=testinst2 noprint;
by qtrno2 permno;
output out=stock_fundturnover sum(fundturnshares)=fundturnshares sum(shares)=sumshares;
run;

data stock_fundturnover; set stock_fundturnover;
fundturnlen = fundturnshares/sumshares;
keep qtrno2 permno fundturnlen;
run;

data stock_fundturnover; set stock_fundturnover;
if fundturnlen~=.;
run;

/* STEP 2: Merge fund turnover data with analyst recommendations data from IBES, stock characteristics data from CRSP, accounting data from COMPUSTAT, and 13F institutional holdings data from WRDS to get the Final Dataset */
 
/* Chars is a data file that includes the following stock characteristics calculated at the quarterly frequency using CRSP, COMPUSTAT and 13F data:

1. Market Cap (here: mcap, in the paper: MCAP)
2. Book to Market Ratio (here: bmratio, in the paper: BMRATIO)
3. Institutional Ownership(here: io, in the paper: IO)
4. Average Daily Turnover (here: avgdailyturnover, in the paper: TURNOVER)
5. Past 12 month returns (here: MOM12, in the paper: MOM12)
The dataset is at the quarterly frequency and also includes cusip, permno, sic code (siccd), a quarter index (qtrno2), and the fiscal year (fyear)*/
 
data temp; set stock_fundturnover;
keep qtrno2 permno fundturnlen;
run;

proc sort data=chars; by qtrno2 permno; run;
proc sort data= temp; by qtrno2 permno; run;
data finaldataset;
merge chars(in=in1) temp(in=in2);
by qtrno2 permno;
if in1=1;
run;

/* STEP 3: Merge idiosyncratic risk variable (here: idioriskff, in the paper: IDIORISK) */

proc sort data=finaldataset; by qtrno2 permno; run;
proc sort data=idioriskff; by qtrno2 permno; run;
data finaldataset;
merge finaldataset(in=in1) idioriskff(in=in2);
by qtrno2 permno;
if in1=1;
run;

/* STEP 4: Merge and create transient institutional ownership variable (here: transientpct, in the paper: TRANSIENT IO) from Brian Bushee’s webpage */

data temp; set bushee_variables;
qtrno2=(year-1980)*4+qtr;
keep qtrno2 permno transientpct io_tra;
transientpct=io_tra/io;
run;


proc sort data=finaldataset; by qtrno2 permno; run;
proc sort data=temp; by qtrno2 permno; run;
data finaldataset;
merge finaldataset(in=in1) temp(in=in2);
by qtrno2 permno;
if in1=1;
run;

/* STEP 5: Merge short-term ownership variable (here: stio_fundturn, in the paper STIO) and long-term ownership variable (here: ltio_fundturn, in the paper: LTIO). These variables are calculated using (i) the institution level average fund turnover variable calculated above (meanfundturn,)and (ii) the institution holding file(testinst)*/

data temp; set fundturnover_ltstio;
qtrno2=(year-1980)*4+qtr;
keep qtrno2 permno stio_fundturn ltio_fundturn;
run;

proc sort data=finaldataset; by qtrno2 permno; run;
proc sort data= temp; by qtrno2 permno; run;
data finaldataset;
merge finaldataset(in=in1) temp(in=in2);
by qtrno2 permno;
if in1=1;
run;

/* STEP 6: Merge analyst consensus recommendations (here: meanrec, in the paper: MEANREC) and number of analysts covering a firm (here: numrec, in the paper: NUMREC) from the IBES Recommendation Summary data*/

data temp; set ibesrecosummary;
qtrno2=(year(statpers)-1980)*4+qtr(statpers);
rename meanrec=meanrec_ibes;
run;

data temp; set temp;
meanrec=6-meanrec_ibes;
run;

proc sort data=temp; by cusip qtrno2 statpers; run;
data temp; set temp;
by cusip qtrno2 statpers;
if last.qtrno2;
run;

data temp; set temp;
keep cusip qtrno2 meanrec numrec;
run;

proc sort data=temp; by qtrno2 cusip; run;
proc sort data=finaldataset; by qtrno2 cusip; run;
data finaldataset;
merge finaldataset(in=in1) temp(in=in2);
by qtrno2 cusip;
if in1=1;
run;

/* STEP 7: Merge earnings volatility variable (here: earnvol). We use data from COMPUSTAT annual to calculate earnings volatility */

data temp; set jointmz.earnvol;
keep cusip fyear earnvol;
run;

proc sort data=temp; by fyear cusip; run;
proc sort data=finaldataset; by fyear cusip; run;
data finaldataset;
merge finaldataset(in=in1) temp(in=in2);
by fyear cusip;
if in1=1;
run;

/* STEP 8: Merge buy and sell herding measures (in the paper: BHM, SHM) */

proc sort data=finaldataset; by qtrno2 permno; run;
proc sort data=herdingmeasures_final2; by qtrno2 permno; run;
data finaldataset;
merge finaldataset(in=in1) herdingmeasures_final2(in=in2);
by qtrno2 permno;
if in1=1;
run;

/* STEP 9: Calculate and merge R&D to sale variable (here: rdsale)*/

data temp; set rddata;
keep rdsale cusip fyear;
run;

proc sort data=temp; by cusip fyear; run;
proc sort data=finaldataset; by cusip fyear; run;
data finaldataset;
merge finaldataset(in=in1) temp(in=in2);
by cusip fyear;
if in1=1;
run;

/* STEP 10: Define intangible industries (here intangible)*/

data finaldataset; set finaldataset;
if 2821<=siccd<=2829 then intangible=1;
if 2831<=siccd<=2839 then intangible=1;
if 3571<=siccd<=3579 then intangible=1;
if 3671<=siccd<=3679 then intangible=1;
if 4801<=siccd<=4899 then intangible=1;
if 7301<=siccd<=7399 then intangible=1;
if 8701<=siccd<=8799 then intangible=1;
run;

data finaldataset; set finaldataset;
if intangible=. then intangible =0;
run;

/* STEP 11: /*Keep observations with nonmissing FUNDTURNOVER, nonmissing MEANREC, and stock prices greater than $1*/  

data finaldataset; set finaldataset;
if fundturnlen~=.;
if meanrec~=.;
if prc>=1;
run;

