filename myfile 'D:\Dropbox\Step3Log.log';
proc printto log=myfile;run;

libname database "D:\Dropbox\EPA Data\Code and Data\Data\Database";
libname data "D:\Dropbox\EPA Data\Code and Data\Data";

*Clean EPA's Edgar log data;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
*a. Merge with Filing data from SEC master.idx files;
data epa;set database.EPAEdgar;
RequestYear=year(date);
if missing(cik)=0;
if missing(accession)=0;
run;*239,371;
proc sort data=epa;by cik accession;run;
proc sql;
create table epa as 
select a.*, b.Form_Type,b.Date_Filed
from epa as a left join database.Uniquefile_form as b
on (a.cik=b.cik)and(a.accession=b.accession);
quit;
data epa;set epa;
if missing(Form_Type)=0;
drop extention size norefer find browser nip noagent zone;
run;*239,345;
*******************************************************************************;
*******************************************************************************;
*b.Match CIK with gvkey and permno;
proc import datafile ='D:\Dropbox\EPA Data\Code and Data\Data\Database\WRDS_GvkeyCIKlink.csv' out=link dbms=csv replace;run;
*fndate=first filing date, lndate=last filing date;
proc sort data=link nodupkey;by cik fndate LNDATE;run;
data link;set link;
newvar=put(fndate, 8.);
format newfndate YYMMDD10.;
newfndate=input(trim(newvar),YYMMDD10.);
newvar2=put(LNDATE, 8.);
format newlndate YYMMDD10.;
newlndate=input(trim(newvar2),YYMMDD10.);
run;
proc sql;
create table epa as 
select a.*, b.gvkey
from epa as a left join link as b
on (a.cik=b.cik)and(a.Date_Filed>=b.newfndate)and(a.Date_Filed<=b.newlndate);
quit;
*Merge with CCM link table; 
data link;set database.Ccmxpf_lnkhist;
if lpermno ne .;
if linkenddt =.E then linkenddt=29991231;
if LINKTYPE='LC' or LINKTYPE='LU';*CCM link;
ngvkey=gvkey*1;
run;
proc sort data=link nodupkey;by ngvkey lpermno linkdt linkenddt;run;
*Unique request ID;
data epa;set epa;
RequestID=_n_;
run;
proc sql;
create table epa as 
select a.*, b.lpermno,b.LPERMCO
from epa as a left join link as b
on (a.gvkey=b.ngvkey) and(a.Date_Filed ge b.linkdt) and (a.Date_Filed le b.linkenddt);
quit;
data epa;set epa;
if gvkey ne .;
if lpermno ne .;
run;
proc sort data=epa nodupkey;by RequestID;run;*146,289;
data data.EPAEdgar_clean;set epa;run;



*EPA Enforcement Case: Add Characteristics Variables;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data case;set data.rawcase;run;
*Penalty and Compliance cost;
data case;set case;
Penalty=FED_PENALTY*1;
Ccost=COMPLIANCE_ACTION_COST*1;
if missing(Penalty)=1 then Penalty=0;
if missing(Ccost)=1 then Ccost=0;
if TOTAL_SEP>0 then SEP=1;else SEP=0;
run;
************************************************************************************************************************;
************************************************************************************************************************;
*Resolution date of cases;
proc import out=a datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\CASE_MILESTONES.csv" dbms=csv replace;run;
data a;set a;
format date yymmdd10.;
date=input(trim(ACTUAL_DATE),MMDDYY10.);
run;
proc sort data=a;by CASE_NUMBER descending date;run;
proc sql;  
create table case_milestone
as select a.CASE_NUMBER,b.*
from case as a left join a as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit; 
proc sort data=case_milestone;by CASE_NUMBER date;run; 
*Identify final order date (Admin cases) or Referal to DOJ date(Judicial cases);
data temp;set case_milestone;
format Resolution_DOJ mmddyy10.;
format Resolution_FO mmddyy10.;
DOJ=find(SUB_ACTIVITY_TYPE_DESC,"Justice");
FO=find(SUB_ACTIVITY_TYPE_DESC,"Final Order");
if DOJ>0 then Resolution_DOJ=date;
if FO>0 then Resolution_FO=date;
run;
data temp1;set temp;if Resolution_DOJ ne .;run;
proc sort data=temp1;by CASE_NUMBER Resolution_DOJ;run;
proc sort data=temp1 nodupkey;by CASE_NUMBER;run;
data temp2;set temp;if Resolution_FO ne .;run;
proc sort data=temp2;by CASE_NUMBER Resolution_FO;run;
proc sort data=temp2 nodupkey;by CASE_NUMBER;run;
proc sql;  
create table case
as select a.*,b.Resolution_DOJ
from case as a left join temp1 as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit; 
proc sql;  
create table case
as select a.*,b.Resolution_FO
from case as a left join temp2 as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit; 
data case(drop=Resolution_DOJ Resolution_FO);set case;
format Resolution_Date mmddyy10.;
if judicial=1 then Resolution_Date=Resolution_DOJ;
if judicial=0 then Resolution_Date=Resolution_FO;
format Resolution_Month mmddyy10.;
Resolution_Month=intnx('month',Resolution_Date,0,'end');
if Resolution_Month ne .;
run;
proc sort data=case;by Resolution_Date;run;*3346;
************************************************************************************************************************;
************************************************************************************************************************;
*Add more case characteristics;
proc import out=case1 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\CASE_ENFORCEMENTS.csv" dbms=csv replace;run;
proc import out=case2 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\CASE_ENFORCEMENT_CONCLUSIONS.csv" dbms=csv replace;run;*law and region office code;
proc import out=case3 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\CASE_ENFORCEMENT_TYPE.csv" dbms=csv replace;GUESSINGROWS=13000;run;
*Regional office of each case;
proc sort data=case1 nodupkey;by CASE_NUMBER;run;
proc sql;
create table case as 
select a.*,b.REGION_CODE
from case as a left join case1 as b
on a.CASE_NUMBER=b.CASE_NUMBER;
quit;
data case;set case;
if region_code=. then region_code=0;*Headquarter=0;
run;
*Primary law violated;
proc sort data=case2 nodupkey;by CASE_NUMBER;run;
proc sql;
create table case as 
select a.*,b.PRIMARY_LAW
from case as a left join case2 as b
on a.CASE_NUMBER=b.CASE_NUMBER;
quit;
data case;set case;
if missing(PRIMARY_LAW)=1 then primary_law='OTHER';
run;
*Expedited case;
data case3;set case3;
if find(ENF_TYPE_DESC,"Expedited")>0|find(ENF_TYPE_DESC,"expedited")>0 then expedited=1;else expedited=0;
run;
proc sort data=case3 nodupkey;by case_number;run;
proc sql;  
create table case
as select a.*,b.expedited
from case as a left join case3 as b
on a.case_number=b.case_number; 
quit;
data data.case;set case;run;*3346;


*EPA Monthly Edgar downloads from Jan 2003 to Jun 2017;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data edgar;set data.EPAEdgar_clean;
format edgar_date mmddyy10.;
edgar_date=intnx('month',date,0,'end');
run;
proc sort data=edgar;by gvkey edgar_date;run;
*Total downloads: group downloads by month;
proc means data=edgar noprint;by gvkey edgar_date;
output out=edgar_sum n(RequestID)=Total ;
run;
proc sort data=edgar_sum nodupkey;by gvkey edgar_date;run;
data data.EPAedgar_month;set edgar_sum;run;*9,079 firm month;


*Market and Financial Variables;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data funda;set database.ccm_funda;
if missing(lpermno)=0;
if fic="USA";*domestic firms only;
if fyear>=2002 and fyear<=2018;
keep gvkey lpermno CONM fyear datadate;
run;
proc sort data=funda nodupkey;by gvkey datadate;run;*84,465;
proc sort data=funda nodupkey;by gvkey fyear;run;*84,465;
************************************************************************************************************************;
************************************************************************************************************************;
*Industry SIC code and Market cap at FYE;
proc sort data=database.crsp_msf out=crspmsf nodupkey;by permno date;run;
data crspmsf;set crspmsf;
SIC=put(SICCD,4.0);
run;
proc sort data=crspmsf nodupkey;by permno date;run;
proc sql; 
create table funda
as select a.*, b.SIC,b.NAICS,b.SHRCD,b.EXCHCD
from funda as a left join crspmsf as  b
on a.lpermno=b.permno and intck('month',a.datadate,b.date)=0; 
quit; 
*CRSP starts cover NAICS in 2004 (For 2002/2003, use 2004 NAICS);
data temp;set funda;if NAICS ne .;run;
proc sort data=temp;by gvkey fyear;run;
proc sort data=temp nodupkey;by gvkey;run;
proc sql; 
create table funda
as select a.*, b.NAICS as NAICS2004
from funda as a left join temp as b
on a.gvkey=b.gvkey; 
quit; 
data funda(drop=NAICS2004);set funda;
if NAICS=. then NAICS=NAICS2004;
run;
************************************************************************************************************************;
************************************************************************************************************************;
*Firm financial characteristics;
data comp;set database.ccm_funda;run;
proc sort data=comp nodupkey;by gvkey datadate;run;
proc sql;
create table comp as 
select a.*,b.sale as lagSale,b.at as lagAT,
       b.ni as ni_lag,b.OPEPS as lagOPEPS,
	   b.act as lagact,b.lct as laglct,b.che as lagche,b.dlc as lagdlc,
	   b.rect as lagrect
from comp as a left join comp as b
on (a.gvkey=b.gvkey)and intck('month',a.datadate,b.datadate)=-12;
quit;
data comp;set comp;
ngvkey=gvkey*1;
if dlc=. then dlc=0;
if txp=. then txp=0;
*Edgar determinants;
MV=prcc_f*csho;
BTM=ceq/(prcc_f*csho);
CASH=che/lagat;
TANGIBILITY=ppent/lagat;
LEV=(dltt+dlc)/lagat;
ROA=ni/lagat;
SG=(sale/lagSale)-1;
*Zscore;
WC_TA=((act-che)-(lct-dlc-txp))/at;
RE_TA=re/at;
EBIT_TA=OIADP/at;
MV_TL=(prcc_f*csho)/lt;
S_TA=sale/at;
ZSCORE=1.2*WC_TA+1.4*RE_TA+3.3*EBIT_TA+0.6*MV_TL+1*S_TA;
*Discretionary accrual variables;
tacc=((act-lagact)-(lct-laglct)-(che-lagche)+(dlc-lagdlc)-dp)/lagat;
inv_asset=1/lagat;
ch_cashsale=((sale-lagsale)-(rect-lagrect))/lagat;
ppe=ppegt/lagat;
run;
************************************************************************************************************************;
************************************************************************************************************************;
*Merge financial to CCM universe;
data comp;set comp;
keep gvkey cik datadate fyear sale at ngvkey--ppe;
run;
proc sort data=comp nodupkey;by gvkey datadate;run;
proc sql;
create table funda as 
select a.*,b.*
from funda as a left join comp as b
on a.gvkey=b.gvkey and a.datadate=b.datadate;
quit;
proc sort data=funda nodupkey;by gvkey datadate;run;*84,465;
************************************************************************************************************************;
************************************************************************************************************************;
*Discretionary accrual estimated at SIC2-year level;
data funda;set funda;
if SIC='Z' then SIC='9999';
if missing(SIC)=1 then SIC='9999';
run;
%include 'D:\Dropbox\EPA Data\Code and Data\Data\Program help\ff48.sas';
%ind_ff48 (funda,funda,SIC,ind_code);
proc sort data=funda;by ind_code fyear;run;
proc reg data=funda;by ind_code fyear;
model tacc=inv_asset ch_cashsale ppe;
output out=funda r=DISACC p=PACC;
run;quit;
data funda;set funda;
drop tacc--FF_IND WC_TA--S_TA pacc;
run;
************************************************************************************************************************;
************************************************************************************************************************;
*Historical CIK;
proc import datafile ='D:\Dropbox\EPA Data\Code and Data\Data\Database\WRDS_GvkeyCIKlink.csv' out=link dbms=csv replace;run;
*fndate=first filing date, lndate=last filing date;
proc sort data=link nodupkey;by gvkey;run;
proc sql;
create table funda as 
select a.*, b.cik as cikh
from funda as a left join link as b
on (a.ngvkey=b.gvkey)and(a.datadate>=b.fndate)and(a.datadate<=b.lndate);
quit;
data funda;set funda;
if cikh=. then cikh=cik;
run;
************************************************************************************************************************;
************************************************************************************************************************;
*Loughran&McDonald 10-K WORD count and Complexity;
proc import datafile ="D:\Dropbox\EPA Data\Code and Data\Data\Database\LoughranMcDonald_10KComplexity_2001-2018.csv" out=a dbms=csv replace;run;
data a;set a;
newvar=put(filing_date, 8.);
format newfndate YYMMDD10.;
newfndate=input(trim(newvar),YYMMDD10.);
year=year(newfndate);
run;
proc sql;
create table funda as
select a.* ,b.Complexity
from funda as a left join a as b
on a.cikh=b.cik and intck('month',a.datadate,b.newfndate)>0 and intck('month',a.datadate,b.newfndate)<12;
quit;
proc sort data=funda nodupkey;by gvkey datadate;run;
data data.funda;set funda;run;*84,465 firm-fyear obs;



*Firm-year sample from Jan 2003 to June 2017;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
*Sample filters;
data sample;set data.funda;
if NAICS ne .;
if EXCHCD=1 or EXCHCD=2 or EXCHCD=3;*Keep stocks listed on AMEX, NSDQ&NYSE;
if SHRCD=10 or SHRCD=11 or SHRCD=12;*Keep common shares first digit=1;
run;*67,282;
*Require financial data;
data sample;set sample;
log_MVE=log(MV);
abs_disacc=abs(DISACC);
NAICS2=substr(naics,1,2);
NAICS6=NAICS*1;
if log_mve ne . and btm ne . and sg ne . and zscore ne .;
if cash ne . and lev ne . and roa ne . and tangibility ne .;
if complexity ne . and abs_disacc ne .;
run;*43,425;
*Require the month in a FY is between 2003Jan and 2017June with edgar log data;
proc import out=calendar datafile="D:\Dropbox\EPA Data\Code and Data\Data\calendar.csv" dbms=csv replace;run;
proc sql;  
create table sample
as select a.*,b.date as date_monthly
from sample as a left join calendar as b
on intck('month',b.date,a.datadate)>=0 and intck('month',b.date,a.datadate)<=11;
quit; 
data sample;set sample;
if missing(date_monthly)=0;
if date_monthly>='01Jan2003'D and date_monthly<='30Jun2017'D;
run;
proc sort data=sample nodupkey;by gvkey fyear date_monthly;run;
proc sort data=sample nodupkey;by gvkey fyear;run;*40,702 firm-year;
*Keep industries with EPA enforcement history;
proc sort data=sample out=firm nodupkey;by gvkey;run;
data case;set data.case;
case=1;
if year(Resolution_Month)>=2003 and year(Resolution_Month)<=2017;
run;
proc sql;  
create table firm
as select a.*,b.case
from firm as a left join case as b
on a.lpermno=b.permno; 
quit; 
data ind;set firm;if case=1;EPA_icase=1;run;
proc sort data=ind nodupkey;by NAICS;run;
proc sql;  
create table sample
as select a.*,b.EPA_icase
from sample as a left join ind as b
on a.NAICS=b.NAICS; 
quit;
data sample;set sample;
if EPA_icase=1;
run;*21,397;
proc freq data=sample;table fyear;run;
*Rank variables;
proc sort data=sample;by fyear;run;
proc rank 
data=sample out=sample  groups=5;by fyear;
var zscore;
ranks rank;
run; 
data sample;set sample;
zscore_qtl=rank/4;
run;
%include 'D:\Dropbox\EPA Data\Code and Data\Data\Program help\winsorn.sas';
%winsor(dsetin=sample, dsetout=sample, byvar=fyear,
vars=btm cash tangibility lev roa sg, pctl=1 99);
data data.firmyear;set sample;run;*21,397 unique firm years;
proc sort data=data.firmyear out=test nodupkey;by gvkey;run;*3176 unique firms;


*Generate monthly file for unique firm-year obs;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data sample;set data.firmyear;drop date_monthly;run;
proc import out=calendar datafile="D:\Dropbox\EPA Data\Code and Data\Data\calendar.csv" dbms=csv replace;run;
proc sql;  
create table sample
as select a.*,b.date as date_monthly
from sample as a left join calendar as b
on intck('month',b.date,a.datadate)>=0 and intck('month',b.date,a.datadate)<=11 
and b.date<='30Jun2017'D and b.date>='01Jan2003'D;
quit; 
proc sort data=sample nodupkey;by gvkey date_monthly;run;*246,826;
************************************************************************************************************************;
************************************************************************************************************************;
*Control variable: Prior 3-year violation measure (after 2 years);
data case;set data.case;case=1;run;
proc sql;  
create table temp
as select a.*,b.case_number,b.case,b.Resolution_Month
from sample as a left join case as b
on a.lpermno=b.permno and intck('month',b.Resolution_Month,a.date_monthly)>=25
and intck('month',b.Resolution_Month,a.date_monthly)<=60; 
quit; 
proc sort data=temp nodupkey;by gvkey date_monthly case_number;run;
proc means data=temp noprint;by gvkey date_monthly;output out=temp1 n(case)=count_3yr;run;
data temp1;set temp1;
log_resolve_3yr=log(sum(1,Count_3yr));
run;
proc sql;  
create table sample
as select a.*,b.log_resolve_3yr
from sample as a left join temp1 as b
on a.gvkey=b.gvkey and a.date_monthly=b.date_monthly; 
quit; 
************************************************************************************************************************;
************************************************************************************************************************;
*Control variable: Indicators for new filings duirng the calendar month;
data file;set database.Nlpsa;
if missing(gvkey)=0;
if form='10-K'|form='10-K/A'|form='10-Q'|form='10-Q/A';
Filings=1;
run;
proc sql;  
create table sample
as select a.*,b.Filings
from sample as a left join file as b
on a.gvkey=b.gvkey and intck('month',b.fdate,a.date_monthly)=0; 
quit; 
proc sort data=sample nodupkey;by gvkey date_monthly;run;
data sample;set sample;
if Filings=. then Filings=0;
run;
************************************************************************************************************************;
************************************************************************************************************************;
*Control variable: Total number of new filings in the month;
data file;set database.Nlpsa;
if missing(gvkey)=0;
Year=year(fdate);
month=month(fdate);
run;
proc sort data=file nodupkey;by gvkey accession;run;
proc sort data=file nodupkey;by gvkey year month accession;run;
proc means data=file noprint;by gvkey year month;output out=temp n(year)=Count_filings;run;
proc sql;  
create table sample
as select a.*,b.Count_filings
from sample as a left join temp as b
on a.gvkey=b.gvkey and year(a.date_monthly)=b.year and month(a.date_monthly)=b.month; 
quit; 
data sample;set sample;
log_filings=log(sum(1,Count_filings));
run;
data data.finalsample;set sample;run;*246,826 firm-month obs;


*Case sample;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data case;set data.case;
ryear=year(Resolution_Month);
if Resolution_Month>='31Jan2003'D and Resolution_Month<='30Jun2017'D;*Cases that could affect 2003Jan-2017Jun;
if PRIMARY_LAW='CERCLA'|PRIMARY_LAW='RCRA'|PRIMARY_LAW='TSCA'|PRIMARY_LAW='SDWA' then FACase=1;else FACase=0;
run;*2314;
*Matchable to the firm-year sample;
proc sql;  
create table case
as select a.*,b.gvkey
from case as a left join data.finalsample b
on a.permno=b.lpermno and a.Resolution_Month=b.date_monthly; 
quit; 
data case;set case;
if gvkey ne .;
run;*865;
*Rank by penalty and Ccost;
proc rank 
data=case out=case  groups=5;
var penalty ccost;
ranks rank1 rank2;
run; 
data case;set case;
penalty_qtl=rank1/4;
ccost_qtl=rank2/4;
if penalty_qtl=1 then HighP=1;else HighP=0;
if ccost_qtl=1 then HighCC=1;else HighCC=0;
run;
proc freq data=case;table ryear;run;
data data.sample_case;set case;run;*865;



*Case sample with predicted investigation timeline;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data case;set data.sample_case;run;
*Related activity;
proc import out=c1 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\CASE_RELATED_ACTIVITIES.csv" dbms=csv replace;run;
data c1;set c1;
if missing(ACTIVITY_STATUS_DATE)=0;
format act_date yymmdd10.;
act_date=input(trim(ACTIVITY_STATUS_DATE),MMDDYY10.);
run;
proc sort data=c1 nodupkey;by case_number ACTIVITY_TYPE_DESC ACTIVITY_STATUS_DATE;run;
proc sql;  
create table act
as select a.case_number,a.resolution_date,b.act_DATE,b.ACTIVITY_TYPE_DESC
from case as a left join c1 as b
on a.case_number=b.case_number; 
quit;
data act;set act;
if act_DATE>=resolution_date then act_DATE=.;
if act_DATE>=resolution_date then ACTIVITY_TYPE_DESC=.;
run;
proc sort data=act;by case_number descending act_DATE;run;
proc sort data=act nodupkey;by case_number;run;
data test;set act;if act_date ne .;run;
***************************************************;
*Add hand collected activity data;
proc import out=a datafile="D:\Dropbox\EPA Data\Code and Data\R2 hand collection data\Case_discovery_timeline.csv" dbms=csv replace;GUESSINGROWS=max;run;
proc sql;  
create table act
as select a.*,b.*
from act as a left join a as b
on a.case_number=b.case_number; 
quit;
data act;set act;
Distance1=intck('month',date1,Resolution_Date_New)-1;
Distance2=intck('month',date2,Resolution_Date_New)-1;
Distance3=intck('month',act_date,Resolution_Date)-1;
if distance3>=50 then distance3=.;
distance=min(Distance1,Distance2,distance3);
*Minimum requirement for 1 month;
if distance=0|distance=-1 then Distance=1;
run;
***************************************************;
*Merge to case sample;
data case;set data.sample_case;run;
proc sql;  
create table case
as select a.*,b.distance
from case as a left join act as b
on a.case_number=b.case_number; 
quit;
proc export data=case outfile= "D:\Dropbox\EPA Data\Output\case_distance.dta" replace; run;
*Please run the prediction model using Stata codes Tables.dta;
***************************************************;
*Import Predicted distance;
proc import out=case datafile="D:\Dropbox\EPA Data\Output\case_pdistance.dta" replace;run;
data case;set case;
pdistance=int(exp(p_distance));
length=distance;
if distance=. then length=pdistance;
*Discovery period end month;
format Discovery_End mmddyy10.;
Discovery_End=intnx('month',Resolution_Date,-length-1,'end');
run;
data case;set case;
drop activity_type_code--compliance_action_cost begyear resolution_doj resolution_fo rank1 rank2 
type_discovery--log_ccost;
run;
proc means data=case n mean std min p1 p10 q1 median q3 p90 p99 max maxdec=0;var distance pdistance length;run;
data data.sample_case;set case;run;*865 unique cases;
*Appendix III;
proc means data=data.sample_case n mean std min q1 median q3 max maxdec=0;var distance pdistance length;run;
proc means data=data.sample_case n mean std min q1 median q3 max maxdec=0;where distance=.;var pdistance length;run;



*Inspection sample;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data inspect;set data.rawinspect;
iyear=year(Inpsection_Month);
if Inpsection_Month>='31Jan2003'D and Inpsection_Month<='30Jun2017'D;*Cases that could affect 2003Jan-2017Jun;
run;*5,105;
proc sql;  
create table inspect
as select a.*,b.gvkey
from inspect as a left join data.finalsample b
on a.permno=b.lpermno and a.Inpsection_Month=b.date_monthly; 
quit; 
data inspect;set inspect;
if gvkey ne .;
run;*2,426;
data data.sample_inspect;set inspect;run;


*Ruling sample;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
*Identify the affected firm-year for each rule;
proc import out=ind datafile="D:\Dropbox\EPA Data\Code and Data\Data\AffectedIndustry.dta" replace;run;
data ind;set ind;
nnaics5=1*naics5;
format date yymmdd10.;
date=input(trim(publication_date),MMDDYY10.);
format Publication_Month mmddyy10.;
Publication_Month=intnx('month',Date,0,'end');
if Publication_Month>='31Jan2003'D and Publication_Month<='30Jun2017'D;
run;
data ind1;set ind;if naics6 ne .;run;
data ind2;set ind;if nnaics5 ne .;run;
data ind3;set ind;if naics4 ne .;run;
data ind4;set ind;if naics3 ne .;run;
data ind5;set ind;if naics2 ne .;run;
proc sort data=ind1 nodupkey;by id naics6;run;
proc sort data=ind2 nodupkey;by id nnaics5;run;
proc sort data=ind3 nodupkey;by id naics4;run;
proc sort data=ind4 nodupkey;by id naics3;run;
proc sort data=ind5 nodupkey;by id naics2;run;
*Affected firm year;
data sample;set data.finalsample(keep=NAICS gvkey date_monthly);
NAICS5=substr(NAICS, 1, 5);
NAICS4=substr(NAICS, 1, 4);
NAICS3=substr(NAICS, 1, 3);
NAICS2=substr(NAICS, 1, 2);
nNAICS6=NAICS*1;
nNAICS5=NAICS5*1;
nNAICS4=NAICS4*1;
nNAICS3=NAICS3*1;
nNAICS2=NAICS2*1;
match=1;
run;
proc sql;  
create table ind1
as select a.*,b.*
from ind1 as a left join sample as b
on a.naics6=b.nNAICS6 and a.Publication_Month=b.date_monthly; 
quit;
proc sql;  
create table ind2
as select a.*,b.*
from ind2 as a left join sample as b
on a.nnaics5=b.nNAICS5 and a.Publication_Month=b.date_monthly; 
quit;
proc sql;  
create table ind3
as select a.*,b.*
from ind3 as a left join sample as b
on a.naics4=b.nNAICS4 and a.Publication_Month=b.date_monthly; 
quit;
proc sql;  
create table ind4
as select a.*,b.*
from ind4 as a left join sample as b
on a.naics3=b.nNAICS3 and a.Publication_Month=b.date_monthly; 
quit;
proc sql;  
create table ind5
as select a.*,b.*
from ind5 as a left join sample as b
on a.naics2=b.nNAICS2 and a.Publication_Month=b.date_monthly; 
quit;
data ind;set ind1 ind2 ind3 ind4 ind5;run;
data ind;set ind;
if missing(GVKEY)=0;
run;
*the affected firm for each rule;
proc sort data=ind nodupkey;by id gvkey;run;*49,433;
proc import out=rule datafile="D:\Dropbox\EPA Data\Code and Data\Data\rule.dta" replace;run;
proc sql;  
create table ind
as select a.*,b.abstract
from ind as a left join rule as b
on a.id=b.id; 
quit;
data ind;set ind;*Rules with financial consideration;
if find(abstract,'financial')>0|find(abstract,'Financial')>0 then finrule=1;else finrule=0;
run;
proc sort data=ind nodupkey;by id gvkey;run;*unique firms each rule affected on the P-month;
data data.sample_rulefirm;set ind;run;*49,433;
proc sort data=ind out=rule nodupkey;by id;run;*237 unique rules;
data data.sample_rule;set rule;keep id date publication_month finrule;run;*237 unique rules;



*Firm-Month sampple;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
*Generate monthly file for unique firm-year obs;
data sample;set data.finalsample;run;
*Add edgar downloads monthly;
proc sql;  
create table sample
as select a.*,b.Total
from sample as a left join data.epaedgar_month as b
on a.ngvkey=b.gvkey and a.date_monthly=b.edgar_date; 
quit; 
data sample;set sample;
if Total=. then Total=0;
log_total=log(Total+1);
if total>0 then edgar=1;else edgar=0;
run;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
*Add enforcement case timeline;
data case;set data.sample_case;run;
proc import out=calendar datafile="D:\Dropbox\EPA Data\Code and Data\Data\calendar.csv" dbms=csv replace;run;
proc sql;  
create table mcase
as select a.*,b.date
from case as a left join calendar as b
on intck('month',b.date,a.Discovery_End)<=5 and intck('month',a.Resolution_Month,b.date)<=12; 
quit; 
proc sort data=mcase nodupkey;by CASE_NUMBER date;run;
data mcase;set mcase;
if date>Discovery_End and date<Resolution_Month then Period="Investigation";
if date<=Discovery_End then Period="Discovery";
if date=Resolution_Month then Period="Resolution";
if date>Resolution_Month then Period="Review";
if Period="Discovery" then DUM_Discover=1;else DUM_Discover=0;
if Period="Investigation" then DUM_Investigate=1;else DUM_Investigate=0;
if Period="Resolution" then DUM_Resolve=1;else DUM_Resolve=0;
if Period="Review" then DUM_Review=1;else DUM_Review=0;
run;
*Generate case period indicator;
proc sort data=mcase;by gvkey date;run;
proc means data=mcase noprint;by gvkey date; 
var DUM_Discover DUM_Investigate DUM_Resolve DUM_Review 
FACase HighP HighCC;
output out=case1 max=DUM_Discover DUM_Investigate DUM_Resolve DUM_Review 
FACase HighP HighCC;
run;
*Add monthly case variables to sample;
proc sql;  
create table sample
as select a.*,b.*
from sample as a left join case1 as b
on a.gvkey=b.gvkey and a.date_monthly=b.date; 
quit; 
data sample;set sample;
if DUM_Discover=. then DUM_Discover=0;
if DUM_Investigate=. then DUM_Investigate=0;
if DUM_Resolve=. then DUM_Resolve=0;
if DUM_Review=. then DUM_Review=0;
if FACase=. then FACase=0;
if highCC=. then highCC=0;
if highP=. then highP=0;
run;
proc sort data=sample nodupkey;by gvkey date_monthly;run;
************************************************************************************************************************;
************************************************************************************************************************;
*Add compliance monitoring;
data inspect;set data.sample_inspect;run;
proc import out=calendar datafile="D:\Dropbox\EPA Data\Code and Data\Data\calendar.csv" dbms=csv replace;run;
proc sql;  
create table minspect
as select a.*,b.date
from inspect as a left join calendar as b
on intck('month',b.date,a.Inpsection_Month)<=6 and intck('month',a.Inpsection_Month,b.date)<=6; 
quit; 
proc sort data=minspect nodupkey;by gvkey Inpsection_Month date;run;
data minspect;set minspect;
Month_id=intck('month',Inpsection_Month,date);
if Month_id>=-6 and Month_id<=-1 then Period="Preparation";
if Month_id=0 then Period="Inspection";
if Month_id>=1 and Month_id<=6 then Period="Review";
if Period="Preparation" then DUM_Prepare=1;else DUM_Prepare=0;
if Period="Inspection" then DUM_Inspect=1;else DUM_Inspect=0;
if Period="Review" then DUM_PostInspect=1;else DUM_PostInspect=0;
run;
proc sort data=minspect;by gvkey date;run;
proc means data=minspect noprint;by gvkey date; 
var DUM_Prepare DUM_Inspect DUM_PostInspect FR_inspect;
output out=temp max=DUM_Prepare DUM_Inspect DUM_PostInspect FR_inspect;
run;
*Add case period indicator;
proc sql;  
create table sample
as select a.*,b.*
from sample as a left join temp as b
on a.gvkey=b.gvkey and a.date_monthly=b.date; 
quit; 
data sample;set sample;
if DUM_Prepare=. then DUM_Prepare=0;
if DUM_Inspect=. then DUM_Inspect=0;
if DUM_PostInspect=. then DUM_PostInspect=0;
if FR_inspect=. then FR_inspect=0;
run;
proc sort data=sample nodupkey;by gvkey date_monthly;run;
************************************************************************************************************************;
************************************************************************************************************************;
*Add ruling;
data ind;set data.sample_rulefirm;drop date;run;
proc import out=calendar datafile="D:\Dropbox\EPA Data\Code and Data\Data\calendar.csv" dbms=csv replace;run;
proc sql;  
create table mind
as select a.*,b.date
from ind as a left join calendar as b
on intck('month',b.date,a.Publication_Month)<=6 and intck('month',a.Publication_Month,b.date)<=6; 
quit; 
proc sort data=mind nodupkey;by id gvkey date;run;
data mind;set mind;
Month_id=intck('month',Publication_Month,date);
if Month_id>=-6 and Month_id<=-1 then Period="Preparation";
if Month_id=0 then Period="Publish";
if Month_id>=1 and Month_id<=6 then Period="Review";
if Period="Preparation" then DUM_preparerule=1;else DUM_preparerule=0;
if Period="Publish" then DUM_publishrule=1;else DUM_publishrule=0;
if Period="Review" then DUM_postrule=1;else DUM_postrule=0;
run;
proc sort data=mind;by gvkey date;run;
proc means data=mind noprint;by gvkey date; 
var dum_preparerule dum_publishrule dum_postrule finrule;
output out=temp max=dum_preparerule dum_publishrule dum_postrule finrule;
run;
*Add case period indicator;
proc sql;  
create table sample
as select a.*,b.*
from sample as a left join temp as b
on a.gvkey=b.gvkey and a.date_monthly=b.date; 
quit; 
data sample;set sample;
if dum_preparerule=. then dum_preparerule=0;
if dum_publishrule=. then dum_publishrule=0;
if dum_postrule=. then dum_postrule=0;
if finrule=. then finrule=0;
run;
proc sort data=sample nodupkey;by gvkey date_monthly;run;
************************************************************************************************************************;
************************************************************************************************************************;
*Subsample indicators: firms during the regulatory process;
data sample;set sample;
if dum_discover=1|dum_investigate=1|dum_resolve=1|dum_review=1 then senforce=1;else senforce=0;
if dum_prepare=1|dum_inspect=1|dum_postinspect=1 then smonitor=1;else smonitor=0;
if dum_preparerule=1|dum_publishrule=1|dum_postrule=1 then srule=1;else srule=0;
drop conm SHRCD EXCHCD DISACC cikh abs_disacc complexity NAICS2 NAICS6
EPA_icase rank Count_filings Date--_FREQ_;
run;
*Financial ratios used by EPA;
****************************************************************************************************;
****************************************************************************************************;
*Financial ratios;
data comp;set database.ccm_funda;run;
proc sort data=comp nodupkey;by gvkey datadate;run;
data comp;set comp;
if intan=. then intan=0;
if lt=. then lt=at-ceq;
if dp=. then dp=0;
liquid1=act/lct;*Requirement>1.5;
liquid2=(act-lct)/at;*Requirement>-0.1;
prof1=(ni+dp)/lt;*Requirement>0.1;
if ni>0 then prof2=1;else prof2=0;
solv1=(at-lt)/lt;*Requirement>0.5;
TanNA=at-lt-intan;*Requirement>10M;
if TanNA>10 then solv3=1;else solv3=0;
keep gvkey lpermno fyear datadate sale at lt liquid1--solv3;
run;
*Segment data: foreign sales over total sales;
data seg;set database.Compustat_segment_geo;year=year(datadate);run;
proc sort data=seg nodupkey;by gvkey datadate SID;run;
proc freq data=seg;table snms;run;
data seg;set seg;
if missing(SNMS)=0;
if SNMS='America'|SNMS='Americas'|SNMS='Americas (mostly U.S)'|SNMS='Americas - U.S'|SNMS='Americas - U.S.'|SNMS='North America (US)'|SNMS='North America (mainly the United States)'|
SNMS='North America, Mainly USA'|SNMS='North America, principally U.S.'|SNMS='North America,United States'|SNMS='North America,United States,Canada'|SNMS='North America,United States,Domestic'|
SNMS='U.S.A'|SNMS='U.S.A.'|SNMS='UNITED STATES'|SNMS='US'|SNMS='USA'|SNMS='USA Region'|SNMS='United States'|SNMS='United States'|SNMS='United States (U.S.)'|SNMS='United States Operations'|
SNMS='United States Region'|SNMS='United States and it Territories'|SNMS='United States and its Territories'|SNMS='United States of America'|SNMS='United States,Domestic'|SNMS='United states'|SNMS='United states of America'|
SNMS='Unites States'|SNMS='Unted States'|SNMS='United Staes'|SNMS='United State'|SNMS='United State of America'|SNMS='United Stateas'|SNMS='United Stated'|
SNMS='United Stated of America'|SNMS='United States & Territories'|SNMS='North America-US'|SNMS='U.S. Operations' then USA=1;else USA=0;
run;
data a;set seg;if usa=1;if sales ne .|ias ne .;run;
proc sql;  
create table comp
as select a.*,b.sales as sale_us,b.ias as at_us
from comp as a left join a as b
on a.gvkey=b.gvkey and b.datadate=a.dataDate; 
quit;
data comp;set comp;
US_sale=sale_us/sale;*Requirement>90%;
run;
proc sort data=comp nodupkey;by gvkey datadate;run;
****************************************************************************************************;
****************************************************************************************************;
*Add to firm-month sample: most recent years;
proc sql;  
create table sample
as select a.*,b.*,b.datadate as datadate_fd
from sample as a left join comp as b
on a.gvkey=b.gvkey and intck('month',b.datadate,a.date_monthly)>0; 
quit;
proc sort data=sample;by gvkey date_monthly descending datadate_fd;run;
proc sort data=sample nodupkey;by gvkey date_monthly;run;
****************************************************************************************************;
****************************************************************************************************;
data sample;set sample;
if US_sale=. then US_sale=1;
if US_sale>1 then US_sale=1;
solv2=US_sale;
run;
*winsor;
%include 'D:\Dropbox\EPA Data\Code and Data\Data\Program help\winsorn.sas';
%winsor(dsetin=sample, dsetout=sample, byvar=fyear,
vars=liquid1 liquid2 prof1 solv1, pctl=1 99);
data sample;set sample;
drop LT TanNA sale_us--datadate_fd;
data data.sample;set sample;run;
proc export data=sample outfile= "D:\Dropbox\EPA Data\Output\sample.dta" replace; run;*246,826;


*Case level downloads;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data case;set data.sample_case;run;*865;
data sample;set data.sample;run;
*Financial ratios measured as of the most recent FY data before the investigation period starts;
proc sql;  
create table case
as select a.*,b.ngvkey,b.liquid1,b.liquid2,b.prof1,b.prof2,b.solv1,b.solv2,b.solv3
from case as a left join sample as b
on a.gvkey=b.gvkey and intck('month',b.date_Monthly,a.Discovery_End)=0; 
quit; 
*Use resolution if discovery period ratio is not available;
data case1(drop=ngvkey--solv3);set case;if liquid1=.;run;
data case2;set case;if liquid1 ne .;run;
proc sql;  
create table case1
as select a.*,b.ngvkey,b.liquid1,b.liquid2,b.prof1,b.prof2,b.solv1,b.solv2,b.solv3
from case1 as a left join sample as b
on a.gvkey=b.gvkey and intck('month',b.date_Monthly,a.Resolution_Month)=0; 
quit; 
data case;set case1 case2;run;
*Edgar downloads over the investigation period;
data edgar;set data.EPAEdgar_clean;run;
proc sql;  
create table edgar
as select a.*,b.RequestID
from case as a left join edgar as b
on a.ngvkey=b.gvkey and intck('month',b.date,a.Resolution_Month)>0 and intck('month',b.date,a.Discovery_End)<0; 
quit; 
proc sort data=edgar;by CASE_NUMBER;run;
proc means data=edgar noprint;by CASE_NUMBER;
output out=out1 n(RequestID)=Total;
run;
proc sql;  
create table case
as select a.*,b.Total
from case as a left join out1 b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit; 
*Total number of filings over the investigation period;
data file;set database.Nlpsa;
if missing(gvkey)=0;
Year=year(fdate);
run;
proc sort data=file nodupkey;by gvkey accession;run;
proc sql;  
create table file
as select a.*,b.year as file,b.accession
from case as a left join file as b
on a.gvkey=b.gvkey and intck('month',b.fdate,a.Resolution_Month)>0 and intck('month',b.fdate,a.Discovery_End)<0; 
quit; 
proc sql;
create table file as
select *, n(file) as Count_filings
from file group by case_number;
quit;
proc sort data=file nodupkey;by case_number;run;
proc sql;  
create table case
as select a.*,b.Count_filings
from case as a left join file b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit; 
data case;set case;
if Total=. then Total=0;
if Total>0 then edgar=1;else edgar=0;
log_total=log(Total+1);
log_filings=log(sum(1,Count_filings));
run;
proc export data=case outfile= "D:\Dropbox\EPA Data\Output\case.dta" replace; run;*865 unique cases;
data data.t2case;set case;if edgar=1;run;



*Inspection level downloads;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data inspect;set data.sample_inspect;run;
proc sql;  
create table inspect
as select a.permno,a.Inpsection_Month,b.STATUTE_CODE,b.FRIns,b.ACTIVITY_ID
from inspect as a left join data.rawinspect_fac b
on a.permno=b.permno and a.Inpsection_Month=b.Inpsection_Month; 
quit;
*Add edgar and financial;
*Financial is measured as of the most recent FY data before the inspection month;
proc sql;  
create table inspect
as select a.*,b.edgar,b.total,b.log_total,
b.liquid1,b.liquid2,b.prof1,b.prof2,b.solv1,b.solv2,b.solv3,b.log_filings 
from inspect as a left join data.sample b
on a.permno=b.lpermno and a.Inpsection_Month=b.date_Monthly; 
quit;
proc export data=inspect outfile= "D:\Dropbox\EPA Data\Output\inspect.dta" replace; run;*5257 Inspection;
data data.t2insp;set inspect;if edgar=1;run;



*Rulemaking downloads during proposal month;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
data rule;set data.sample_rulefirm;keep id publication_date Publication_Month gvkey NAICS abstract;run;
proc sort data=rule nodupkey;by id gvkey;run;*49433 law-affected firm;
*Add edgar and financial;
*Financial is measured as of the most recent FY data before the proposal month;
proc sql;  
create table rule
as select a.*,b.edgar,b.total,b.log_total,
b.liquid1,b.liquid2,b.prof1,b.prof2,b.solv1,b.solv2,b.solv3,b.log_filings,b.finrule 
from rule as a left join data.sample b
on a.gvkey=b.gvkey and a.Publication_Month=b.date_Monthly; 
quit;
data rule;set rule;
if find(abstract,'CERCLA')>0 then Primary_LAW='CERCLA';
if find(abstract,'RCRA')>0 then Primary_LAW='RCRA';
if find(abstract,'TSCA')>0 then Primary_LAW='TSCA';
if find(abstract,'SDWA')>0 then Primary_LAW='SDWA';
if find(abstract,'CAA')>0 then Primary_LAW='CAA';
if find(abstract,'CWA')>0 then Primary_LAW='CWA';
if find(abstract,'EPCRA')>0 then Primary_LAW='EPCRA';
if find(abstract,'FIFRA')>0 then Primary_LAW='FIFRA';
if missing(Primary_LAW)=1 then Primary_LAW='OTHER';
run;
proc export data=rule outfile= "D:\Dropbox\EPA Data\Output\rule.dta" replace; run;*49433 unique rule-firm;
data data.t2rule;set rule;if edgar=1;run;
