

*TRI RAW DATA (for pollution data (robustness test) and facility-parent firm information);
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
proc import out=year2000 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2000_us.csv" dbms=csv replace;run;
proc import out=year2001 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2001_us.csv" dbms=csv replace;run;
proc import out=year2002 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2002_us.csv" dbms=csv replace;run;
proc import out=year2003 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2003_us.csv" dbms=csv replace;run;
proc import out=year2004 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2004_us.csv" dbms=csv replace;run;
proc import out=year2005 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2005_us.csv" dbms=csv replace;run;
proc import out=year2006 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2006_us.csv" dbms=csv replace;run;
proc import out=year2007 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2007_us.csv" dbms=csv replace;run;
proc import out=year2008 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2008_us.csv" dbms=csv replace;run;
proc import out=year2009 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2009_us.csv" dbms=csv replace;run;
proc import out=year2010 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2010_us.csv" dbms=csv replace;run;
proc import out=year2011 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2011_us.csv" dbms=csv replace;run;
proc import out=year2012 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2012_us.csv" dbms=csv replace;run;
proc import out=year2013 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2013_us.csv" dbms=csv replace;run;
proc import out=year2014 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2014_us.csv" dbms=csv replace;run;
proc import out=year2015 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2015_us.csv" dbms=csv replace;run;
proc import out=year2016 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2016_us.csv" dbms=csv replace;run;
proc import out=year2017 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2017_us.csv" dbms=csv replace;run;
proc import out=year2018 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2018_us.csv" dbms=csv replace;run;
proc import out=year2019 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2019_us.csv" dbms=csv replace;run;
proc import out=year2020 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\TRI Basic Data Files\tri_2020_us.csv" dbms=csv replace;run;

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

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

data tri;
format _4__FACILITY_NAME $70.;
format _13__LONGITUDE $20.;
format _15__PARENT_CO_NAME $70.;
format _20__INDUSTRY_SECTOR $130.;
format _40__CLASSIFICATION $10.;
format _34__CHEMICAL $120.;
format _36__TRI_CHEMICAL_COMPOUND_ID $20.;
format _37__CAS_ $20.;
format _61__ON_SITE_RELEASE_TOTAL $22.;
format _84__OFF_SITE_RELEASE_TOTAL $22.;
format _103__TOTAL_RELEASES $22.;
set year2000 year2001 year2002 year2003 year2004 year2005 year2006 year2007 year2008
year2009 year2010 year2011 year2012 year2013 year2014 year2015 year2016 year2017 year2018 year2019 year2020;
keep _1__YEAR  _2__TRIFD  _3__FRS_ID  _4__FACILITY_NAME _8__ST _9__ZIP _12__LATITUDE _13__LONGITUDE
_14__HORIZONTAL_DATUM _15__PARENT_CO_NAME _16__PARENT_CO_DB_NUM _17__STANDARD_PARENT_CO_NAME
_19__INDUSTRY_SECTOR_CODE _20__INDUSTRY_SECTOR _39__CLEAN_AIR_ACT_CHEMICAL _40__CLASSIFICATION
_34__CHEMICAL _36__TRI_CHEMICAL_COMPOUND_ID _37__CAS_ _46__UNIT_OF_MEASURE 
_61__ON_SITE_RELEASE_TOTAL _84__OFF_SITE_RELEASE_TOTAL  _103__TOTAL_RELEASES;
run;
data tri;set tri;
rename _1__YEAR=YEAR;
rename _2__TRIFD=TRIFD;
rename _3__FRS_ID=FRSID;
rename _4__FACILITY_NAME=FACILITY_NAME;
rename _8__ST=ST;
rename _9__ZIP=ZIP;
rename _12__LATITUDE=LATITUDE;
rename _13__LONGITUDE=LONGITUDE;
rename _14__HORIZONTAL_DATUM=HORIZONTAL_DATUM;
rename _15__PARENT_CO_NAME=PARENT_CO_NAME;
rename _16__PARENT_CO_DB_NUM=PARENT_CO_DB_NUM;
rename _17__STANDARD_PARENT_CO_NAME=STANDARD_PARENT_CO_NAME;
rename _19__INDUSTRY_SECTOR_CODE=INDUSTRY_SECTOR_CODE;
rename _20__INDUSTRY_SECTOR=INDUSTRY_SECTOR;
rename _39__CLEAN_AIR_ACT_CHEMICAL=CLEAN_AIR_ACT_CHEMICAL;
rename _40__CLASSIFICATION=CLASSIFICATION;
rename _46__UNIT_OF_MEASURE=UNIT_OF_MEASURE;
rename _61__ON_SITE_RELEASE_TOTAL=ON_SITE_RELEASE_TOTAL;
rename _84__OFF_SITE_RELEASE_TOTAL=OFF_SITE_RELEASE_TOTAL;
rename _103__TOTAL_RELEASES=TOTAL_RELEASES;
rename _34__CHEMICAL=CHEMICAL;
rename _36__TRI_CHEMICAL_COMPOUND_ID=TRI_CHEMICAL_COMPOUND_ID;
rename _37__CAS_=CAS;
run;
data tri;
retain YEAR  TRIFD  FRSID  FACILITY_NAME ST ZIP LATITUDE LONGITUDE
HORIZONTAL_DATUM PARENT_CO_NAME PARENT_CO_DB_NUM STANDARD_PARENT_CO_NAME
INDUSTRY_SECTOR_CODE INDUSTRY_SECTOR CLEAN_AIR_ACT_CHEMICAL CLASSIFICATION
CHEMICAL TRI_CHEMICAL_COMPOUND_ID CAS
UNIT_OF_MEASURE ON_SITE_RELEASE_TOTAL OFF_SITE_RELEASE_TOTAL TOTAL_RELEASES;
set tri;
run;
data tri;set tri;
NYEAR=YEAR*1;
RELEASE_ONSITE=ON_SITE_RELEASE_TOTAL*1;
RELEASE_OFFSITE=OFF_SITE_RELEASE_TOTAL*1;
RELEASE_TOTAL=TOTAL_RELEASES*1;
if PARENT_CO_NAME ne 'NA';
run;*1,493,131;
proc sort data=tri;by FRSID Year;run;*Firm-year release by main chemicals;
*Aggregated total release by firm-year;
data tri;set tri;if UNIT_OF_MEASURE='Pounds';run;*1,471,775;*Pounds 98.6%;
proc sort data=tri nodupkey;by FRSID Year CHEMICAL RELEASE_TOTAL;run;
proc sql;
create table tri_firmyear as
select *, sum(RELEASE_ONSITE) as RELEASE_ONSITE_SUM, sum(RELEASE_OFFSITE) as RELEASE_OFFSITE_SUM,
       sum(RELEASE_TOTAL) as RELEASE_TOTAL_SUM, n(Year) as CountChemical
from tri group by FRSID, Year;
quit;
proc sort data=tri_firmyear nodupkey;by FRSID Year;run;*360,042;
data tri_firmyear;set tri_firmyear;drop CLEAN_AIR_ACT_CHEMICAL CLASSIFICATION
CHEMICAL TRI_CHEMICAL_COMPOUND_ID CAS ON_SITE_RELEASE_TOTAL OFF_SITE_RELEASE_TOTAL TOTAL_RELEASES
RELEASE_ONSITE RELEASE_OFFSITE RELEASE_TOTAL;
run;
data data.tri;set tri_firmyear;run;*360,042 facility-year data;
*Parent Firm Matching;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
/*Unique parent company for manual matching;
data tri_firmyear;set data.tri;if nyear ge 2010 and nyear le 2020;run;
proc sort data=tri_firmyear out=parent nodupkey;by PARENT_CO_DB_NUM;run;*5417;
data parent;set parent;keep PARENT_CO_NAME PARENT_CO_DB_NUM STANDARD_PARENT_CO_NAME;
if missing(PARENT_CO_NAME)=0;
if missing(PARENT_CO_DB_NUM)=0;
if PARENT_CO_DB_NUM ne 'NA';
run;
proc export data=parent OUTFILE= "D:\Dropbox\ParentCo.xlsx" dbms =xlsx replace;sheet="ParentCo";RUN;
proc export data=database.Parentco_matched OUTFILE= "D:\Dropbox\ParentCo.xlsx" dbms =xlsx replace;sheet="ParentCo";RUN;*/
proc import out=match datafile="D:\Dropbox\EPA Data\Code and Data\Data\ParentCo_Match_TRI.csv" dbms=csv replace;run;
*Tri data with matched parent firm;
data tri_firmyear;set data.tri;nPARENT_CO_DB_NUM=PARENT_CO_DB_NUM*1;run;
proc sort data=match nodupkey;by PARENT_CO_DB_NUM;run;
proc sql;  
create table tri_firmyear
as select a.*,b.Permco,b.Permno,b.COMNAM,b.namedt,b.nameenddt,b.match
from tri_firmyear as a left join match as b
on a.nPARENT_CO_DB_NUM=b.PARENT_CO_DB_NUM; 
quit;  
data tri_firmyear;set tri_firmyear;
if match=1;
run;*183,097;
*unique match between facility and parent firm with permno;
data facility;set tri_firmyear;keep FRSID permno PERMCO COMNAM;run;
proc sort data=facility nodupkey;by FRSID;run;
data data.facility_tri;set facility;run;





*FE&C RAW DATA;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
*Formal enforcement action;
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_VIOLATIONS.csv" dbms=csv replace;run;
proc import out=case3 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\CASE_PENALTIES.csv" dbms=csv replace;run;
proc import out=case4 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\CASE_FACILITIES.csv" dbms=csv replace;run;
proc import out=case5 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\CASE_DEFENDANTS.csv" dbms=csv replace;run;
proc import out=case6 datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\CASE_MILESTONES.csv" dbms=csv replace;run;
*Violation types: for multiple types, keep the rank order=1;
proc sort data=case2;by CASE_NUMBER RANK_ORDER;run;
proc sort data=case2 nodupkey;by CASE_NUMBER;run;
proc sql;  
create table case
as select a.*,b.VIOLATION_TYPE_DESC,b.VIOLATION_TYPE_CODE
from case1 as a left join case2 as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit;  
*Sum of penalty and CC on a case;
proc sort data=case3 nodupkey;by CASE_NUMBER;run;
proc sql;  
create table case
as select a.*,b.FED_PENALTY,b.ST_LCL_PENALTY,b.TOTAL_SEP,b.COMPLIANCE_ACTION_COST
from case as a left join case3 as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit;  
*Add facilities involved in the case;
proc sort data=case4 nodupkey;by CASE_NUMBER REGISTRY_ID;run;
proc sort data=case nodupkey;by CASE_NUMBER;run;
proc sql;  
create table case
as select a.*,b.REGISTRY_ID,b.FACILITY_NAME
from case as a left join case4 as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit;  
proc sort data=case nodupkey;by REGISTRY_ID CASE_NUMBER;run;
*Single-defendant cases;
proc sort data=case5 nodupkey;by CASE_NUMBER DEFENDANT_NAME;run;
data case5;set case5; NACTIVITY_ID=ACTIVITY_ID*1;run;
proc means data=case5 noprint;by CASE_NUMBER;
output out=temp n(NACTIVITY_ID)=CountDft;
run;
data temp;set temp;if CountDft=1;run;*90% cases has single defendant;
proc sql;  
create table temp
as select a.*,b.DEFENDANT_NAME
from temp as a left join case5 as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit;
*Add defendant names to cases;
proc sql;  
create table case
as select a.*,b.DEFENDANT_NAME
from case as a left join temp as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit;  
data data.temp1;set case;run;
*Add TRI parent name;
data tri;set data.tri;keep FRSID PARENT_CO_NAME PARENT_CO_DB_NUM STANDARD_PARENT_CO_NAME;run;
proc sort data=tri nodupkey;by FRSID;run;
proc sql;  
create table case
as select a.*,b.PARENT_CO_NAME
from case as a left join tri as b
on a.REGISTRY_ID=b.FRSID; 
quit;
proc sort data=case nodupkey;by REGISTRY_ID CASE_NUMBER;run;
*Generate Parent Co name for matching;
data case;set case;
format ParentCo $70.;
if missing(PARENT_CO_NAME)=0 then ParentCo=PARENT_CO_NAME;
if missing(PARENT_CO_NAME)=1 then ParentCo=DEFENDANT_NAME;
run;
*Last date of cases;
data case6;set case6;
format date yymmdd10.;
date=input(trim(ACTUAL_DATE),MMDDYY10.);
run;
proc sort data=case6;by CASE_NUMBER descending date;run;
proc sort data=case6 nodupkey;by CASE_NUMBER ;run;
proc sql;  
create table case
as select a.*,b.date as LastDate
from case as a left join case6 as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit;  
data case;set case;
if missing(ParentCo)=0;*Non-missing parent company;
if year(LastDate)>=2000;*Cases ends after 2000;
run;
proc sort data=case nodupkey;by CASE_NUMBER;run;
/*Cases for matching;
proc export data=case OUTFILE= "D:\Dropbox\Hand.xlsx" dbms =xlsx replace;sheet="NameMatch";RUN;*/
*Cases with Parent firm name matches;
proc import out=match datafile="D:\Dropbox\EPA Data\Code and Data\Data\MatchedCase.csv" dbms=csv replace;run;
proc sql;  
create table case
as select a.*,b.permno,b.match
from case as a left join match as b
on a.CASE_NUMBER=b.CASE_NUMBER; 
quit; 
data case;set case;if match=1;run;*3360;
*unique match between facility and parent firm with permno;
data match;set case;keep REGISTRY_ID PERMCO PERMNO COMNAM;run;
proc sort data=match nodupkey;by REGISTRY_ID;run;*2374;
data data.facility;set match;run;
*Firm case data;
proc sort data=case nodupkey;by CASE_NUMBER;run;
data case;set case;
if ACTIVITY_TYPE_CODE='JDC' then Judicial=1;else Judicial=0;
keep CASE_NUMBER ACTIVITY_TYPE_CODE FED_PENALTY TOTAL_SEP COMPLIANCE_ACTION_COST PERMNO Judicial;
run;
data data.rawcase;set case;run;*3360 unique cases;



*FE&C RAW DATA: Inspection by firm month;
************************************************************************************************************************;
************************************************************************************************************************;
************************************************************************************************************************;
*Inspection data by facility-month;
proc import out=inspect datafile="D:\Dropbox\EPA Data\Code and Data\Data\Database\FE&C Data Files\ICIS_FEC_EPA_INSPECTIONS.csv" dbms=csv replace;GUESSINGROWS=1355;run;
data inspect;set inspect;
format idate mmddyy10.;
idate=input(trim(ACTUAL_BEGIN_DATE),MMDDYY10.);
iyear=year(idate);
if idate ne .;
if missing(REGISTRY_ID)=0;
format Inpsection_Month mmddyy10.;
Inpsection_Month=intnx('month',idate,0,'end');
run;
proc sort data=inspect nodupkey;by REGISTRY_ID Inpsection_Month;run;
proc sql;
create table inspect as
select *, n(iYear) as CountInspect
from inspect group by REGISTRY_ID, Inpsection_Month;
quit;
*Match facility with parent firm using matches from TRI and Case;
proc sql;  
create table inspect
as select a.*,b.Permno
from inspect as a left join data.facility as b
on a.REGISTRY_ID=b.REGISTRY_ID; 
quit; 
proc sql;  
create table inspect
as select a.*,b.permno as permno2
from inspect as a left join data.facility_tri as b 
on a.REGISTRY_ID=b.FRSID; 
quit; 
data inspect;set inspect;
if permno=. then permno=permno2;
run;
data inspect;set inspect;
if permno ne .;
run;
proc sort data=inspect nodupkey;by REGISTRY_ID Inpsection_Month;run;
*Indicator for inspection with financial review;
proc freq data=inspect;table COMP_MONITOR_TYPES;run;
data inspect;set inspect;
if find(COMP_MONITOR_TYPES,'Financial Record')=1| find(COMP_MONITOR_TYPES,'Inventory')=1|
find(COMP_MONITOR_TYPES,'Review')=1|find(COMP_MONITOR_TYPES,'Data')=1
|find(COMP_MONITOR_TYPES,'Full Evaluation')=1|find(COMP_MONITOR_TYPES,'112(r)(7) Inspection')=1
then FRIns=1;else FRIns=0;*Note 112r/7 involve CAA cases with financial consideration;
run;
proc sort data=inspect ;by permno Inpsection_Month;run;
data data.rawinspect_fac;set inspect;run;*10446 facility-month observation;
proc sql;
create table inspect as
select *, sum(CountInspect) as TotalInspect,max(FRIns) as FR_inspect
from inspect group by permno, Inpsection_Month;
quit;
proc sort data=inspect nodupkey;by permno Inpsection_Month;run;
data inspect;set inspect;
keep permno Inpsection_Month TotalInspect FR_inspect; 
run;
data data.rawinspect;set inspect;run;*6337 firm-month observation;

