options errorabend;
libname perm "../data";
libname ibes "../data/raw";
libname crsp "../data/raw";
libname comp "../data/raw";
libname audit '../data/raw';
%include 'modules/Macro Repository.sas';

****************************************;
* File to create firmn-qtr and firm-year controls from various WRDS datasets;
* To include: siccd, exchcd, shrcd, permno, ff48, firm age, market cap, leverage (variations?);
*             assets, roa, salesgrth_qtr, salesgrth_a, TACC, WCACC, CFO, EARN, HHI (SIC), Tobin's Q,
*             ;
* 
****************************************; 

*Pull COMPUSTAT Variables;
%let head_vars = gvkey conm tic cik cusip datadate rdq indfmt datafmt popsrc consol curcdq fyr fqtr fyearq;
%let fin_vars = /* B/S Items */ ltq atq ceqq cshoq prccq dlcq dlttq dd1q req rectq invtq lctq actq ppentq
				/* I/S Items */ ibq saleq epspxq capxy xrdq xrdy
				/* CF  Items */ oancfy;
%let beg_year = 1990;
%let end_year = 2022;


data x; set comp.fundq
(keep = &head_vars &fin_vars 
);						
where 
/* (tic in ("HPQ","MSFT","APPL")) and */
		(year(datadate) ge &beg_year) and (year(datadate) le &end_year)
		and (indfmt='INDL') and (datafmt='STD') and (popsrc='D') and (consol='C') and (curcdq='USD')
		and (atq > 0);
lnAssets = log(atq);
MVE = CSHOQ*prccq;
LNMVE = log(MVE);									
BTM = CEQQ / (CSHOQ*prccq) ;
MTB = (MVE + ATQ - CEQQ)/ ATQ; /*As defined in GLZ 2021*/
year=year(datadate);
Lev=sum(dd1q,dlttq)/(atq); /*As defined in GLZ 2021*/
if ibq < 0 and ibq ne . then loss = 1;
else if ibq ne . then loss = 0;
else loss = .;
cikn = cik*1;
drop indfmt datafmt popsrc consol CEQQ;					
run;

proc sort data=x nodupkey; by gvkey datadate;
run;

%QUARTERIZE (INSET=x,OUTSET=x,IDVAR=fyr gvkey,FYEAR=fyearq,FQTR=fqtr,VARS=oancfy xrdy capxy); 

*Grab permno from WRDS COMPUSTAT-CRSP linking table and IBES ticker from iclink;
proc sql; create table x2 as select distinct
	a.*, b.lpermno as permno
    from x as a left join crsp.ccmxpf_linktable
	(where=(linktype in ('LU', 'LC', 'LD', 'LF', 'LN', 'LO', 'LS', 'LX'))) as b
    on (a.gvkey = b.gvkey) 
	and (b.linkdt <= a.datadate or b.linkdt = .B) 
	and (a.datadate <= b.linkenddt or b.linkenddt = .E);
 	quit;

data x2; set x2;
where (permno ne .) and (gvkey ne "");
run;

proc sort data = x2 nodupkey; by gvkey datadate;
run; 

*Grab IBES Ticker from Iclink table ('IBES TICKER to CRSP PERMNO (Jun 2018 update).sas');
proc sql; create table x3 as select							
	a.*, b.ticker as ibesticker
    from x2 a left join crsp.ibcrsphist b
    on a.permno eq b.permno
    and (b.sdate-90 le a.datadate or b.sdate = .)
    and (a.datadate le b.edate+90 or b.edate = .);
quit;

data x3; set x3;
where (ibesticker ne "");
run;

proc sort data = x3 nodupkey; by gvkey datadate;
run; 

*Get Historical SIC code from CRSP;
proc sql; create table x3b as select	
	a.*, b.siccd, b.exchcd, shrcd
	from x3 as a left join crsp.mse as b
	on (a.permno eq b.permno) and b.date le a.datadate le b.NAMEENDT
	order by gvkey, datadate, date;
	quit;

proc sql;
	create table x3c as select
	a.*, b.gsubind as gics
	from x3b as a left join comp.names as b
	on a.gvkey = b.gvkey
	and b.year1 le a.fyearq le b.year2;
quit;

*grab price from crsp;
proc sql; create table x4 as select	
	a.*, abs(b.prc) /*ranges reported as the negative midpoint/average*/ as crsp_price, date  /*may need to adjust CRSP price for splits etc.*/
	from x3c as a left join crsp.dsf as b
	on (a.permno eq b.permno) 
	and a.datadate - 6 le b.date le a.datadate 
	order by gvkey, datadate, date;
quit;

data x4; set x4; by gvkey datadate date;
if first.datadate;
if gvkey ne "" and permno ne .;
drop date;
run;

proc sort data = x4 nodupkey; by gvkey datadate;
run;

data x5; set x4; by gvkey datadate;
array vars atq saleq prccq fyearq fqtr rdq datadate epspxq;
array vars_tm1 atq_tm1 saleq_tm1 prccq_tm1 fyearq_tm1 fqtr_tm1 rdq_tm1 datadate_tm1 epspxq_tm1;
do over vars;
	vars_tm1 = lag(vars);
end;
if first.gvkey then do over vars_tm1;
	vars_tm1 = .;
end;
year_dif = fyearq-fyearq_tm1;
qtr_dif = fqtr-fqtr_tm1;
* Ensure that the prior quarter is actually the prior quarter;
if (year_dif = 0 and qtr_dif = 1) or (year_dif = 1 and qtr_dif = -3) then do over vars_tm1;
	vars_tm1 = vars_tm1;
end;
else do over vars;
	vars_tm1 = .;
end;
drop year_dif qtr_dif;
run;


proc sort data = x5 nodupkey; by gvkey descending datadate;
run;

data x6; set x5; by gvkey descending datadate;
array vars ibq fyearq fqtr rdq datadate epspxq;
array vars_tp1 ibq_tp1 fyearq_tp1 fqtr_tp1 rdq_tp1 datadate_tp1 epspxq_tp1;
do over vars;
	vars_tp1 = lag(vars);
end;
if first.gvkey then do over vars_tp1;
	vars_tp1 = .;
end;
year_dif = fyearq-fyearq_tp1;
qtr_dif = fqtr-fqtr_tp1;
* Ensure that the prior quarter is actually the prior quarter;
if (year_dif = 0 and qtr_dif = -1) or (year_dif = -1 and qtr_dif = 3) then do over vars_tp1;
	vars_tp1 = vars_tp1;
end;
else do over vars_tp1;
	vars_tp1 = .;
end;
drop year_dif qtr_dif;
run;

proc sort data = x6 nodupkey; by gvkey datadate;
run;

proc sql;
	create table x6b as select
	a.*, b.saleq as saleq_tm4, b.epspxq as epspxq_tm4
	from x6 as a left join x6 as b
	on a.gvkey = b.gvkey
	and a.fyearq = b.fyearq+1
	and a.fqtr = b.fqtr;
quit;

data x7; set x6b;
roa = ibq / atq_tm1;
earn = ibq / atq_tm1;
roa_tp1 = ibq_tp1 / atq;
salesgrth_q = saleq/saleq_tm1 - 1;
salesgrth_a = saleq/saleq_tm4 - 1; 
sic3 = substr(put(siccd,z4.),1,3);
accruals = (ibq - oancfy_q)/atq_tm1;
run;

proc sql;
	create table x7a as select distinct
	a.*, std(b.earn) as earn_vol
	from x7 as a left join x7 as b
	on a.gvkey = b.gvkey
	and a.fyearq-15 le b.fyearq le a.fyearq
	group by a.gvkey, a.datadate;
quit;

proc sort data = x7a nodupkey; by gvkey datadate;
run;

proc sql;
	create table temp_pers as select distinct
	a.gvkey, a.datadate, b.datadate as date, b.epspxq, b.epspxq_tm1
	from x7a as a left join x7a as b
	on a.gvkey = b.gvkey
	and intnx('year',a.datadate,-15) le b.datadate le a.datadate;
quit;

proc sort data = temp_pers nodupkey; by gvkey datadate date;
run;

proc printto log="../logs/proc_reg_junk.log"; run;
proc reg data = temp_pers outest=fcst_est tableout noprint edf; by gvkey datadate;
model epspxq = epspxq_tm1;
run; quit;
proc printto; run;

data pers; set fcst_est (keep = gvkey datadate _type_ intercept epspxq_tm1 _p_ _EDF_ _RSQ_);
where _type_ = "PARMS";
NOBS = _P_+_EDF_;
drop _p_ _edf_ _type_ ;
if nobs ge 3;
run;

proc sql;
	create table x7b as select distinct
	a.*, b.epspxq_tm1 as earn_persist
	from x7a as a left join pers as b
	on a.gvkey = b.gvkey
	and a.datadate = b.datadate;
quit;

proc sort data = x7b nodupkey; by gvkey datadate;
run;

* For SIC3 HHI, I can calculate at the quarterly level or at the annual level;
proc sql;
	create table x8 as select
	*, saleq/sum(saleq)*100 as HHI_p1
	from x7b group by sic3, fyearq, fqtr
	order by gvkey, datadate;
quit;

proc sql;
	create table x9 as select
	*, sum(HHI_p1**2) as HHIq_SIC3
	from x8 group by sic3, fyearq, fqtr
	order by gvkey, datadate;
quit;

data a; set comp.funda
(keep = gvkey conm tic cik cusip datadate indfmt datafmt popsrc consol curcd fyr fyear 
		sale at aqp rcp sstk prstkc dv dltis dltr dlcch
);						
where 
	/* (tic in ("HPQ","MSFT","APPL")) and */
	(year(datadate) ge &beg_year) and (year(datadate) le &end_year)
		and (indfmt='INDL') and (datafmt='STD') and (popsrc='D') and (consol='C') and (curcd='USD')
		and (at > 0);
year = year(datadate);
merger = (AQP ne 0 and aqp ne .);
restructure = (RCP ne 0 and RCP ne .);
array spvars sstk prstkc dv dltis dltr dlcch;
do over spvars;
	if missing(spvars) then spvars = 0;
end;
financing = (SSTK-PRSTKC - DV + DLTIS - DLTR - DLCCH)/AT;
drop indfmt datafmt popsrc consol ;					
run;


*Grab permno from WRDS COMPUSTAT-CRSP linking table and IBES ticker from iclink;
proc sql; create table a2 as select distinct
	a.*, b.lpermno as permno
    from a as a left join crsp.ccmxpf_linktable
	(where=(linktype in ('LU', 'LC', 'LD', 'LF', 'LN', 'LO', 'LS', 'LX'))) as b
    on (a.gvkey = b.gvkey) 
	and (b.linkdt <= a.datadate or b.linkdt = .B) 
	and (a.datadate <= b.linkenddt or b.linkenddt = .E);
quit;

data a2; set a2;
where (gvkey ne "") and (permno ne .);
run;

proc sort data = a2 nodupkey; by gvkey datadate;
run; 

*Grab IBES Ticker from Iclink table ('IBES TICKER to CRSP PERMNO (Jun 2018 update).sas');
proc sql; create table a3 as select							
	a.*, b.ticker as ibesticker
    from a2 a left join crsp.ibcrsphist b
    on a.permno eq b.permno
	and (b.sdate-90 le a.datadate or b.sdate = .)
	and (a.datadate le b.edate+90 or b.edate = .);
    quit;

data a3; set a3;
where (ibesticker ne "");
run;

proc sort data = a3 nodupkey; by gvkey datadate;
run; 

*Get Historical SIC code from CRSP;
proc sql; create table a3b as select	
	a.*, b.siccd, substr(put(b.siccd,z4.),1,3) as sic3, b.exchcd, shrcd
	from a3 as a left join crsp.mse as b
	on (a.permno eq b.permno) and b.date le a.datadate le b.NAMEENDT
	order by gvkey, datadate, date;
	quit;

    
proc sql;
	create table a3c as select distinct
	gvkey, datadate, year, fyear, sic3, (sale/sum(sale)*100) as HHI_part1
	from a3b group by sic3, year;
quit;

proc sql;
	create table a4 as select distinct
	sic3, year, fyear, sum(hhi_part1**2) as HHI_sic3
	from a3c group by sic3, year;
quit;

proc sql;
	create table x10 as select
	a.*, b.hhi_sic3
	from x9 as a left join a4 as b
	on a.sic3 = b.sic3
	and a.year = b.year;
quit;

data x10; set x10;
if sic3 = . then do;
	hhi_sic3 = .;
	hhiq_sic3 = .;
end;
run;

proc sql;
	create table x10b as select
	a.*, b.datadate as fyear_end, b.financing as financing_tp1, b.restructure as restructure_tp1, b.merger as merger_tp1
	from x10 as a left join a as b
	on a.gvkey = b.gvkey
	and a.fyearq = b.fyear-1;
quit;

proc sort data = x10b nodupkey; by gvkey datadate;
run;

%get_returns(dsetin=x10b,dsetout=x11,mkt=vwretd,freq=dsf,n_before=63,n_after=0,event_date = datadate,car_name=car,bhar_name=bhar,n_name=n,std_name=stdret);
%get_returns(dsetin=x11,dsetout=x12,mkt=vwretd,freq=dsf,n_before=1,n_after=1,event_date = rdq,car_name=car,bhar_name=bhar,n_name=n,std_name=stdret);


proc sort data = x12 out = temp_ret
	(keep = permno datadate rdq datadate_tm1 rdq_tm1 datadate_tp1 rdq_tp1) nodupkey; by permno datadate;
run;

proc sql;
	create table ret as select distinct
	a.*, b.date, b.ret, b.vol*b.cfacshr as vol_adj, b.shrout*b.cfacshr as shrout_adj
	from temp_ret as a left join crsp.dsf as b
	on a.permno = b.permno
	and intnx('month',a.datadate,-11) le b.date le a.datadate;
quit;

proc sql;
	create table ret as select distinct
	a.*, b.dlret
	from ret as a left join crsp.dsedelist (where=(missing(dlret)=0)) as b
	on a.permno = b.permno
	and a.date = b.dlstdt
	order by a.permno, a.datadate, a.date;
quit;

proc sql;
	create table ret2 as select distinct
	a.*, b.vwretd
	from ret as a left join crsp.dsi as b
	on a.date = b.date
	order by permno, datadate, date;
quit;

data ret2; set ret2;
exret = (1+ret)*sum(1,dlret)-1-vwretd;
ret = (1+ret)*sum(1,dlret)-1;
run;

proc printto log="../logs/proc_reg_junk.log"; run;
proc reg data = ret2 edf outest=_params (rename=(intercept=alpha vwretd=beta)
		keep=permno datadate intercept vwretd _rmse_ _p_ _edf_) noprint;
		by permno datadate;
		model ret=vwretd;
quit;
proc printto; run;

data ret2b;
	merge ret2 (in=a) _params;
	by permno datadate;
	abret = ret-alpha-beta*vwretd;
	logret=log(1+ret);
	var_estp = _rmse_*_rmse_;
	nobs=_p_+_edf_;
    label var_estp='Estimation Period Variance'
        abret=   'One-day Abnormal Return (AR)'
        ret=     'Raw Return'
        datadate= 'Event Date'
    drop vwretd _p_ _edf_ ;
	if a;
run;

proc sql; create table ret3 as select distinct
	permno, datadate, exp(sum(log(1+ret))) - exp(sum(log(1+vwretd))) as BHAR_YR,
		std(ret) as stdret_yr, mean(vol_adj*100/shrout_adj*1000) as mturn_yr, count(*) as N_ret, std(abret) as idio_vol
	from ret2b group by permno, datadate
	order by permno, datadate
;
quit;

proc sql;
	create table x13 as select distinct
	a.*, b.bhar_yr, b.stdret_yr, b.mturn_yr, b.idio_vol
	from x12 as a left join ret3 as b
	on a.permno = b.permno
	and a.datadate = b.datadate;
quit;

proc sql;
	create table ret as select distinct
	a.*, b.date, b.ret, b.vol*b.cfacshr as vol_adj, b.shrout*b.cfacshr as shrout_adj
	from temp_ret as a left join crsp.dsf as b
	on a.permno = b.permno
	and intnx('weekday',a.rdq,5) le b.date le intnx('weekday',a.rdq_tp1,5);
quit;

proc sql;
	create table ret2 as select distinct
	a.*, b.vwretd
	from ret as a left join crsp.dsi as b
	on a.date = b.date
	order by permno, datadate, date;
quit;

proc sql; create table ret3 as select distinct
	permno, datadate, exp(sum(log(1+ret))) - exp(sum(log(1+vwretd))) as BHAR_QTR_TP1,
		std(ret) as stdret_QTR_TP1, mean(vol_adj*100/shrout_adj*1000) as mturn_QTR_TP1, count(*) as N_ret
	from ret2 group by permno, datadate
	order by permno, datadate
;
quit;

proc sql;
	create table x14 as select distinct
	a.*, b.bhar_QTR_TP1, b.stdret_QTR_TP1, b.mturn_QTR_TP1
	from x13 as a left join ret3 as b
	on a.permno = b.permno
	and a.datadate = b.datadate;
quit;

proc sort data = x14 out = y nodupkey; by gvkey datadate;
run;
* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
* Analyst benchmark - Modified code from Tim Seidel 													   ;
* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
* Prepare to merge IBES;
/*proc sql;*/
/*	create table bench as select*/
/*	a.*, b.ticker as ibesticker*/
/*	from y as a left join home.iclink as b*/
/*	on a.permno=b.permno;*/
/*quit;*/

*add analyst data;
data i; set ibes.statsum_epsus 
	(keep = ticker statpers fpi measure usfirm numest medest
		stdev actual anndats_act fpedats);
where 
/*	(ticker in ('MSFT', 'HPW', 'SNO','AAPL')) and */
		(measure eq 'EPS') and (fpi = '6') and (year(statpers) ge &beg_year);
drop measure usfirm;
year = year(fpedats);
run;

data ibesdata; set i;
if missing(stdev) then stdev=0;
actualdiff=actual-MEDEST;
run;

data ibesdata; set ibesdata;
* Difference between statistical period and announcement date;
days=ANNDATS_ACT-statpers;
run;

proc sort data=ibesdata; by ticker FPEDATS days;
run;

proc sql;
	create table follow as select
	ticker, year, max(numest) as follow
	from ibesdata group by ticker, year;

	create table ibesdata as select
	a.*, b.follow
	from ibesdata as a left join follow as b
	on a.ticker=b.ticker
	and a.year=b.year;
quit;

proc sort data = ibesdata nodupkey; by ticker statpers fpedats; run;

data ibesdata; set ibesdata;
if days le 0 then delete;
run;

proc sort data=ibesdata; by ticker FPEDATS days;
run;
data ibesdata2; set ibesdata; by ticker FPEDats days;
if first.FPEDats;
run;

data ibesdata2; set ibesdata2;
horizon=days/30;
run;

*create lag actual;
proc sort data=ibesdata2; by ticker FPEDATS;
run;

data ibesdata2; set ibesdata2; by ticker FPEDATS;
actual_tm1=lag(actual);
actualdiff_tm1=lag(actualdiff);
lagyear=lag(year);
if first.ticker then do; actual_tm1=.; actualdiff_tm1=.; lagyear=.; end;
run;

proc sort data=ibesdata2; by ticker FPEDATS;
run;

*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*Merge to main dataset - add to main program;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
proc sql; create table bench as select
	a.*, b.actualdiff, b.actualdiff_tm1, (b.actual-b.actual_tm1) as chgeps, b.actualdiff/a.prccq as SUE, 
		b.actualdiff_tm1/a.prccq_tm1 as fcstError_tm1, b.follow, log(follow) as lnfollow
	from y as a left join ibesdata2 as b
	on a.ibesticker = b.ticker
	and month(a.DATADATE) = month(b.FPEDATS)
	and year(a.DATADATE) = year(b.FPEDATS);	
	quit;

proc sort data=bench nodupkey; by gvkey datadate;
run;

data bench1; set bench;
if actualdiff ne . then dif = actualdiff;
else if actualdiff = . and chgeps ne . then dif=chgeps;
else dif=.;
if -0.03 le actualdiff lt 0 then miss_3=1; else miss_3=0;
if actualdiff lt 0 then miss=1; else miss=0;
dif_at=dif*cshoq/atq_tm1;
*dif_chgeps_at = chgeps*cshoq/atq_tm1;
if actualdiff ge 0 then beat = 1; else beat = 0;
run;

proc sql;
	create table z2 as select distinct
	a.*, (a.datadate - min(b.datadate))/365 as firm_age
	from bench1 as a left join comp.fundq as b
	on a.gvkey = b.gvkey
	and (b.indfmt='INDL') and (b.datafmt='STD') and (b.popsrc='D') and (b.consol='C') and (b.curcdq='USD')
	group by a.gvkey;
quit;

data perm.wrds_data; set z2;
run;
