
/*THIS PROGRAM COMPUTE VARIABLES USING NON-GAAP DATA FROM KURT GEE'S WEBSITE*/
/*DATA COMPREHENSIVE FROM FISCAL YEARS 2003-2020 SORTED BY GVKEY-DATADATE*/
/*OBTAIN A SAMPLE OF COMPUSTAT FIRMS FROM 2003-2020*/
rsubmit;
data a1 (keep=gvkey year conm datadate datafqtr eps_gaap); set comp.fundq;
where consol='C' and indfmt='INDL' and datafmt='STD' and popsrc='D' and curcdq = 'USD' and 2003<=fyearq<=2020;
rename fyearq=year;
rename epsfiq=eps_gaap; 
format datadate date9.; run;
proc sort data=a1 nodupkey; by gvkey datadate; run;
proc download data=a1 out=a1; run;
endrsubmit;
/*MERGE WITH KURT GEE'S DATASET: Nongaap_data_2003to2020*/
proc sql;
  create table a2 as select
    a.*, b.mgr_exclude, b.MGR_NG_EPS as eps_mgr
  from a1 a left join Nongaap_data_2003to2020 b
  on a.gvkey=b.gvkey and
  	 a.datadate=b.datadate; quit;
proc sort data=a2 nodupkey; by gvkey datadate; run;
/*OBTAIN IBES DATA*/
rsubmit;
proc upload data=a2 out=a2; run;
proc sql;
  create table a3a as select
    a.*, b.datadate as fyenddt
  from a2 a left join comp.funda b
  on a.gvkey=b.gvkey and
  	 a.year=b.fyear and
	 b.consol='C' and b.indfmt='INDL' and b.datafmt='STD' and b.popsrc='D' and b.curcd = 'USD'; quit;
proc sort data=a3a nodupkey; by gvkey year datadate; run;
/*OBTAIN PERMNO FROM CRSP-COMPUSTAT LINK FILE*/
proc sql;
  create table a3 as select
    a.*, b.lpermno as permno, b.linkdt, b.linkenddt
  from a3a a left join crsp.ccmxpf_linktable b
  on a.gvkey=b.gvkey and
	 b.linktype in ("LU" "LC" "LD" "LF" "LN" "LO" "LS" "LX") and 
	(b.LINKDT <= a.fyenddt or b.LINKDT = .B) and 
    (a.fyenddt <= b.LINKENDDT or b.LINKENDDT = .E) and
	 b.usedflag=1 and
	 b.lpermno~=.; quit;
proc sort data=a3; by gvkey year datadate descending linkenddt;
proc sort data=a3 out=a3 (drop=linkdt linkenddt) nodupkey; by gvkey year datadate; run;
/*OBTAIN TICKER FROM ICLINK FILE: KEEP THE OBSEVATION WITH THE LOWER SCORE WHERE ONE GVKEY MATCHES TO MULTIPLE IBES TICKER*/
proc upload data=iclink out=iclink; run;
proc sql;
  create table a4 as select
    a.gvkey, a.year, a.datadate, a.permno, b.ticker, b.score
  from a3 a left join iclink b
  on a.permno=b.permno; quit;
proc sort data=a4; by gvkey datadate score; run;
proc sort data=a4 nodupkey; by gvkey datadate; run;
data a4; set a4;
if ticker='' then delete; run;
/*OBTAIN ANALYST ESTIMATE FROM IBES ACTUALS FILE*/
proc sql;
  create table a5 as select
    a.*, b.value as eps_ibes, b.actdats
  from a4 a left join ibes.act_epsus b
  on a.ticker=b.ticker and
  	 a.datadate=b.pends and
	 b.measure='EPS' and b.pdicity='QTR' and b.curr_act='USD'; quit;
proc sort data=a5 nodupkey; by gvkey datadate; run;
/*MERGE DATASETS TOGETHER*/
proc sql;
  create table a6 as select
    a.*, b.eps_ibes
  from a2 a left join a5 b
  on a.gvkey=b.gvkey and
  	 a.datadate=b.datadate; quit;
proc sort data=a6 nodupkey; by gvkey datadate; run;
/*CLEAN UP DATASET*/
/*DROP INSTANCES WHERE GAAP EPS IS MISSING FROM COMPUSTAT BECAUSE WILL NEED TO CALCULATE EXCLUSIONS*/
data a7; set a6; where eps_gaap~=.;
/*FOLLOWING HSU ET AL. (2022 JAE), SET EXCLUSIONS TO ZERO IF ACTUAL EPS DATA IS AVAILABLE IN IBES*/
exclusions=eps_mgr-eps_gaap;
if eps_mgr=. and eps_ibes~=. then exclusions=0; run;
data a7; set a7;
/*FOLLOWING HSU ET AL. (2022)*/
nongaap=0; if exclusions~=0 then nongaap=1; if exclusions=. then nongaap=.;
nongaap_higher=0; if exclusions>0 then nongaap_higher=1; if exclusions=. then nongaap_higher=.;
nongaap_lower=0; if exclusions<0 then nongaap_lower=1; if exclusions=. then nongaap_lower=.; run;
/*CALCULATE FREQUENCY BY FISCAL YEAR: REQUIRES ALL 4 QUARTERS*/
data a7; set a7; where nongaap~=.; run;
proc sort data=a7 nodupkey; by gvkey year datadate; run;
proc means data=a7 noprint; by gvkey year;
  output out=a7stats sum(nongaap)=nongaap sum(nongaap_higher)=nongaap_higher sum(nongaap_lower)=nongaap_lower; run;
/*REQUIRES ALL 4 QUARTERS*/
data a8; set a7stats; where _freq_=4; 
ngfreq=nongaap/4;
ng_higherfreq=nongaap_higher/4;
ng_lowerfreq=nongaap_lower/4; run;
/*MERGE DATASET TOGETHER*/
data a6a; set a6 (keep=gvkey year conm);
proc sort data=a6a nodupkey; by gvkey year; run;
proc sql;
  create table a10 as select
    a.*, b.ngfreq, b.ng_higherfreq, b.ng_lowerfreq
  from a6a a left join a8 b
  on a.gvkey=b.gvkey and
  	 a.year=b.year; quit;
proc sort data=a10 nodupkey; by gvkey year; run;
data nongaap; set a10; where ngfreq~=.;
label	ngfreq='Non-Gaap Frequency(requires 4 quarters)'
		ng_higherfreq='Non-Gaap higher than Gaap earnings Frequency(requires 4 quarters)'
		ng_lowerfreq='Non-Gaap lower than Gaap earnings Frequency(requires 4 quarters)'; run;
proc download data=nongaap out=nongaap; run;
endrsubmit;

PROC EXPORT DATA= WORK.nongaap 
            OUTFILE= "C:\JAR Files\nongaap.dta" 
            DBMS=STATA REPLACE; run;
RUN;
