/*******************************************************************************
SAS program creating ibes_2020q2.dta via WRDS cloud
*******************************************************************************/
************************************IBES***************************************;
*Daily CRSP for adjustment factors and permnos;
data daily;
set crspq.dsf;
by permno date;
price=abs(prc);
if 2019<=year(date)<=2020;
keep permno date cfacpr cfacshr price shrout ret;
proc sort nodupkey; by permno date;

*Get CUSIPs;
data names;
set crspq.dsenames (keep=permno namedt nameendt ncusip exchcd shrcd ticker);
by permno namedt;
nameendt=intnx("month",nameendt,0,"end");
if last.permno then nameendt=today();
proc sort nodupkey; by permno namedt nameendt;

proc sql;
create table crspdata
as select a.*, b.ncusip, b.exchcd, b.shrcd, b.ticker as tick
from daily a, names b
where a.permno=b.permno and b.namedt<=a.date<=b.nameendt;
quit;
proc sort; by permno date descending ncusip;
proc sort nodupkey data=crspdata out=crspdata; by permno date;

data crspdata;
set crspdata;
if shrcd in (10,11);
if exchcd in (1,2,3);
prc_a=price/cfacpr;

data est;
set ibes.detu_epsus;
if fpi in ("6","7","8","9","N"); *quarterly EPS;
if measure="EPS";
if usfirm=1;
estimate=value;

proc sql;
create table detail_crsp_adj
as select a.*, b.cfacpr, b.cfacshr, b.permno
from est a, crspdata b
where a.cusip=b.ncusip and a.anndats=b.date;
quit;

data detail_crsp_adj;
set detail_crsp_adj;
if cfacshr>0 then est_adj=estimate*(1/cfacshr);
drop cfacpr cfacshr;

data dec_est;
set detail_crsp_adj;
if year(fpedats)=2020;
if month(fpedats) in (6,7,8); *first full pandemic quarter;
if year(anndats)=2019; *keep estimates from nov,dec 2019;
if 11<=month(anndats)<=12;
e_eps_dec=est_adj;
proc sort; by permno ticker fpedats analys descending anndats;
proc sort nodupkey; by permno ticker fpedats analys; *most recent estimate for each analyst no older than 2 months;

proc sort; by permno ticker fpedats;
proc means data=dec_est noprint median n;
by permno ticker fpedats;
var e_eps_dec;
output out=dec_est
median=e_eps_dec;
run;

data may_est;
set detail_crsp_adj;
if year(fpedats)=2020;
if month(fpedats) in (6,7,8);
if year(anndats)=2020;
if 4<=month(anndats)<=5;  *keep estimates from apr,may 2020;
e_eps_may=est_adj;
proc sort; by permno ticker fpedats analys descending anndats;
proc sort nodupkey; by permno ticker fpedats analys; *most recent estimate for each analyst no older than 2 months;

proc sort; by permno ticker fpedats;
proc means data=may_est noprint median n;
by permno ticker fpedats;
var e_eps_may;
output out=may_est
median=e_eps_may;
run;

proc sql;
create table revision
as select a.permno, a.ticker, a.fpedats, a.e_eps_dec, b.e_eps_may
from dec_est a left join may_est b
on a.permno=b.permno and a.fpedats=b.fpedats;
quit;
proc sort nodupkey data=revision out=revision; by permno fpedats;

*Add Actuals;
data actuals;
set ibes.actu_epsus;
if measure="EPS";
if pdicity="QTR";
if usfirm=1;
actual=value;
adate=anndats; *earnings announcement date;
keep ticker cusip adate actual pends anntims;

proc sql;
create table revision_
as select a.*, b.actual, b.adate
from revision a left join actuals b
on a.ticker=b.ticker and a.fpedats=b.pends;
quit;
proc sort nodupkey data=revision_ out=revision; by permno fpedats;

*Add FY2 consensus as of 12/31/19;
data est;
set ibes.detu_epsus;
if fpi=2; *FY2 EPS;
if measure="EPS";
if usfirm=1;
estimate=value;

proc sql;
create table detail_crsp_adj
as select a.*, b.cfacpr, b.cfacshr, b.permno
from est a, crspdata b
where a.cusip=b.ncusip and a.anndats=b.date;
quit;

data detail_crsp_adj;
set detail_crsp_adj;
if cfacshr>0 then est_adj=estimate*(1/cfacshr);
drop cfacpr cfacshr;

data dec_est;
set detail_crsp_adj;
if 11<=month(anndats)<=12; *Keep estimates issued in 11/19 and 12/19;
if year(anndats)=2019;
e_eps_fy2_dec=est_adj;
proc sort; by permno ticker fpedats analys descending anndats;
proc sort nodupkey; by permno ticker fpedats analys; *most recent estimate for each analyst no older than 2 months;

proc sort; by permno ticker fpedats;
proc means data=dec_est noprint median n;
by permno ticker fpedats;
var e_eps_fy2_dec;
output out=dec_est_fy2
median=e_eps_fy2_dec;
run;
proc sort; by permno ticker descending fpedats;
proc sort nodupkey; by permno ticker;

proc sql;
create table revision_
as select a.*, b.e_eps_fy2_dec
from  revision a left join dec_est_fy2 b
on a.permno=b.permno;
quit;
proc sort nodupkey data=revision_ out=revision; by permno;

*add Dec-end price;
data dec_end;
set crspdata;
if year(date)=2019;
if month(date)=12;
if day(date)=31;

proc sql;
create table revision_
as select a.*, b.prc_a
from revision a left join dec_end b
on a.permno=b.permno;
quit;
proc sort nodupkey data=revision_ out=revision; by permno;

*Add compustat data;
data comp;
set compd.funda;
if consol="C";
if datafmt="STD";
if indfmt="INDL";
if popsrc="D";
if curcd="USD";
if 2018<=year(datadate)<=2019;
if year(datadate)=2019 & month(datadate)>9 then delete; *3 month lag on financials;
keep gvkey datadate pifo txfo txdfo;
proc sort nodupkey; by gvkey descending datadate;

*Merge CRSP and Compustat;
data linktable;
set crsp.ccmxpf_lnkhist;
proc sort; by gvkey linkdt;
data linktable;
set linktable;
by gvkey linkdt;
if LINKTYPE in ("LU","LC") and LINKPRIM in ("P", "C");
*Extend last link end date to include delisting returns;
if last.gvkey then linkenddt=today();
proc sql;
create table ccm
as select a.*, b.lpermno, b.linkdt, b.linkenddt
from comp a, linktable b
where a.gvkey=b.gvkey and (b.linkdt <= a.datadate or missing(b.linkdt)) and
(b.linkenddt >= a.datadate or missing(b.linkenddt));
quit;
*Prefer non-missing link dates;
proc sort; by gvkey datadate descending linkdt descending linkenddt;
proc sort nodupkey; by gvkey datadate;

*Merge CRSP and Compustat;
proc sql;
create table data
as select a.pifo, a.lpermno, a.txfo, a.txdfo, b.*
from ccm a full join revision b
on a.lpermno=b.permno;
quit;

data data;
set data;
if missing(permno) then permno=lpermno;
drop lpermno;
proc sort nodupkey; by permno;
proc export data=data outfile='/home/usc/rerhard/ibes_2020q2.dta' dbms=dta replace;
