/* Reduce the number of variables in Compustat Funda to reduct the processing time*/
%let fundaVars = gvkey gvkey2 cik cik2 datadate fyear sic sich lt ibc at sale capx ib oancf prcc_f csho ceq ni oibdp oiadp dltt dlc fopt xrd dpc txdc esubc sppiv fopo csho tstk acominc fca aqc intan SSTK DLTIS DLCCH emp ppe ppegt dvp dvc dvt NAICSH act ch che lct dp oancf dlc;
run;
/* Add gvkey and cik that have the same format as historical HQ and Incorporation data */
data slapp.funda_clean (keep = &fundavars); 
set slapp.funda;
gvkey2 = input(gvkey, best12.) ;cik2 = input(cik, best12.) ;
run;
/* Fill missing end dates with Dec 31 2018 for historical HQ data */
data hq_change; set slapp.hq_change; if missing(enddate) then enddate=21549; format enddate begdate YYMMDDn8.;run;
/* Merge Compustat and historical HQ and Inc data */
	proc sql;
 create table funda_hq as select distinct a.*, b.ba_state as hq
	from slapp.funda_clean a left join hq_change b on a.cik2=b.cik and b.begdate<=a.datadate<b.enddate;
quit;
/* backfilling missing HQ*/
data t5; set funda_hq; run;
proc sort data=t5;
  by gvkey descending datadate;
run;
data t6;
  set t5;
  by gvkey;
  retain _z1;
  if first.gvkey then do;
                _z1 = hq;
                end;
                else do; 
                if hq = " " then do;
                 hq = _z1;
                end;
                else do;
                               _z1 = hq;
                                end;
end;run;
 data slapp.funda_hq; set t6; run;
/* Construct variables */
proc sort data=slapp.funda_hq nodupkey; by gvkey datadate; run;
data slapp.funda_hq; set slapp.funda_hq;debt=(dltt+dlc);
mve=prcc_f *csho;m2b=(mve)/ceq;leverage=(dltt+dlc)/at;if ni>=0 then loss=0;year=year(datadate);run;
/* Fill missing historical HQ with current ones from Compustat */
proc sql;
 create table a1 as select distinct a.*, b.state as cur_hq
	from slapp.funda_hq a left join slapp.comp_hqinc b on a.gvkey=b.gvkey and a.datadate=b.datadate;
quit;
data a1; set a1; if missing(hq) then hq=cur_hq;run;
/* Add SIC */
proc sql;
 create table a2 as select distinct a.*, b.sic
	from a1 a left join slapp.sic b on a.gvkey=b.gvkey and a.datadate=b.datadate;
quit;
/* Merge sample data and anti slapp enactment year */
proc sql;
 create table a3 as select distinct a.*,b.year as slappyear_hq
	from a2 a left join slapp.slapp b on a.hq=b.st;
quit;
/* Construct treatement (hq, inc) variables*/
data a4; set a3; if not missing(slappyear_hq) and not missing(hq) then treatstate_hq=1; if missing(slappyear_hq) and not missing(hq) then treatstate_hq=0;
if treatstate_hq=1 and year>=slappyear_hq then treat_hq=1;
if treatstate_hq=1 and year<slappyear_hq then treat_hq=0; 
if treatstate_hq=0  then treat_hq=0; 
if hq="MN" and year>=2017  then treat_hq=0; 
if hq="WA" and year>=2015  then treat_hq=0; 
run;
/* Merge with other data*/
proc sql;
 create table mergeddata1 as select distinct a.*,b.*
	from a4 a left join slapp.crash b on a.gvkey=b.gvkey and a.fyear=b.year;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*,b.*
	from mergeddata1 a left join slapp.returns b on a.gvkey=b.gvkey and a.fyear=b.year;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*,b.population, b.income_per_capita
	from mergeddata2 a left join slapp.statecontrols b on a.hq=b.abb_ and a.year=b.year;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*,b.*
	from mergeddata2 a left join slapp.ret b on a.gvkey=b.gvkey and a.fyear=b.year;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*,b.*
	from mergeddata2 a left join slapp.dturn b on a.gvkey=b.gvkey and a.fyear=b.year;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*,b.medest, b.value
	from mergeddata2 a left join slapp.actu_epsus4 b on a.gvkey=b.gvkey and a.datadate=b.pends;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*, b.at as at_l, b.ib as ib_l
	from mergeddata2 a left join mergeddata2 b on a.gvkey=b.gvkey and a.fyear-1=b.fyear;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*, b.at as at_ll, b.ib as ib_ll
	from mergeddata2 a left join mergeddata2 b on a.gvkey=b.gvkey and a.fyear-2=b.fyear;
quit;
data mergeddata2; set mergeddata2;
if year<2002 then ind=int(sich/10);if year>2001 then ind=int(naicsh/100);run;
proc sort data=mergeddata2 nodupkey; by gvkey fyear;run;
proc sql;
 create table mergeddata2 as select distinct a.*,b.skill
	from mergeddata2 a left join slapp.laborskill b on a.year=b.year and a.ind=b.ind;
quit;
data slapp.shortint; set slapp.shortint;year=year(SPLITADJDATE);month=month(SPLITADJDATE);run;
proc sort data=slapp.shortint; by gvkey year month;run;
data mergeddata2; set mergeddata2;
month=month(datadate);year=year(datadate);run;
proc sql;
 create table mergeddata2_2 as select distinct a.*,b.SHORTINTADJ, b.SHORTINT, b.year as year2, b.month as month2
	from mergeddata2 a left join slapp.shortint b on a.gvkey=b.gvkey and a.datadate-365<b.SPLITADJDATE<=a.datadate;
quit;
PROC SQL;
    CREATE TABLE shortint2 AS
   select gvkey, fyear, year2, month2, mean(SHORTINTADJ) as SHORTINTADJ, mean(SHORTINT) as SHORTINT
    FROM mergeddata2_2 GROUP BY gvkey, fyear, year2, month2 order by gvkey, fyear, year2, month2;
QUIT;
PROC SQL;
    CREATE TABLE shortint2 AS
   select gvkey, fyear, mean(SHORTINTADJ) as SHORTINTADJ, mean(SHORTINT) as SHORTINT
    FROM shortint2 GROUP BY gvkey, fyear order by gvkey, fyear;
QUIT;
proc sql;
 create table mergeddata2 as select distinct a.*,b.SHORTINTADJ, b.SHORTINT
	from mergeddata2 a left join shortint2 b on a.gvkey=b.gvkey and a.fyear=b.fyear;
quit;
proc sql;
create table mergeddata2 as
select a.*, b.lpermno as permno
from mergeddata2 as a LEFT JOIN slapp.ccmxpf_lnkhist_3_27 as b                  /*ccm_lnkhist is the linking table downloaded from CRSP/compustat merged*/
on a.gvkey=b.gvkey and b.linkprim in ('P', 'C') and
b.LINKTYPE in ('LU', 'LC') and a.datadate >= b.LINKDT and (a.datadate <= b.LINKENDDT or missing(b.LINKENDDT)) order by gvkey, datadate;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*,b.newstone_nonstd
	from mergeddata2 a left join slapp.sentiment b on a.permno=b.permno and a.fyear=b.year;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*,b.NewsCount_nonstd as newscountsum
	from mergeddata2 a left join slapp.sentiment2 b on a.permno=b.permno and a.fyear=b.year;
quit;
proc sql;
 create table mergeddata2 as select distinct a.*, b.*
	from mergeddata2 a left join slapp.nongaap b on a.gvkey=b.gvkey and a.fyear=b.year;
quit;

proc sort data=mergeddata2 nodupkey; by gvkey fyear;run;

/*Construct cohort level data*/
data mergeddata3; set mergeddata2;if not missing(hq);if missing(slappyear_hq) then slappyear_hq=2050;run;
data c1; set mergeddata3;if 1985<=year<=1997;treat_event=1992;if hq = "CA" or hq = "DE" or hq = "NY" then treatment=1;if missing(treatment) then treatment=0;if hq ne "CA" and hq ne "DE" and hq ne "NY" and slappyear_hq<=1997 then delete;cohort=1;if year>=1992 then post=1;if missing(post) then post=0;run;
data c2; set mergeddata3;if 1986<=year<=1998;treat_event=1993;if hq = "NV" then treatment=1;if missing(treatment) then treatment=0;if hq ne "NV" and slappyear_hq<=1998 then delete; cohort=2;if year>=1993 then post=1;if missing(post) then post=0;run;
data c3; set mergeddata3;if 1987<=year<=1999;treat_event=1994;if hq = "MA" or hq = "MN" or hq = "NE" then treatment=1;if missing(treatment) then treatment=0;if hq ne "MA" and hq ne "MN" and hq ne "NE" and slappyear_hq<=1999 then delete;cohort=3;if year>=1994 then post=1;if missing(post) then post=0;run;
data c4; set mergeddata3;if 1988<=year<=2000;treat_event=1995;if hq = "ME" or hq = "RI" then treatment=1;if missing(treatment) then treatment=0;if hq ne "ME" and hq ne"RI" and slappyear_hq<=2000 then delete;cohort=4;if year>=1995 then post=1;if missing(post) then post=0;run;
data c5; set mergeddata3;if 1989<=year<=2001;treat_event=1996;if hq = "GA" then treatment=1;if missing(treatment) then treatment=0;if hq ne "GA" and slappyear_hq<=2001 then delete;cohort=5; if year>=1996 then post=1;if missing(post) then post=0;run;
data c6; set mergeddata3;if 1990<=year<=2002;treat_event=1997;if hq = "TN" then treatment=1;if missing(treatment) then treatment=0;if hq ne "TN" and slappyear_hq<=2002 then delete;cohort=6; if year>=1997 then post=1;if missing(post) then post=0;run;
data c7; set mergeddata3;if 1991<=year<=2003;treat_event=1998;if hq = "IN" then treatment=1;if missing(treatment) then treatment=0;if hq ne "IN" and slappyear_hq<=2003 then delete;cohort=7; if year>=1998 then post=1;if missing(post) then post=0;run;
data c8; set mergeddata3;if 1992<=year<=2004;treat_event=1999;if hq = "LA" then treatment=1;if missing(treatment) then treatment=0;if hq ne "LA" and slappyear_hq<=2004 then delete;cohort=8; if year>=1999 then post=1;if missing(post) then post=0;run;
data c9; set mergeddata3;if 1993<=year<=2005;treat_event=2000;if hq = "FL" or hq = "PA" then treatment=1;if missing(treatment) then treatment=0;if hq ne "FL" and hq ne "PA" and slappyear_hq<=2005 then delete;cohort=9; if year>=2000 then post=1;if missing(post) then post=0;run;
data c10; set mergeddata3;if 1994<=year<=2006;treat_event=2001;if hq = "NM" or hq = "OR" or hq = "UT" then treatment=1;if missing(treatment) then treatment=0;if hq ne "NM" and hq ne "OR" and hq ne "UT" and slappyear_hq<=2006 then delete;cohort=10;if year>=2001 then post=1;if missing(post) then post=0;run;
data c11; set mergeddata3;if 1995<=year<=2007;treat_event=2002;if hq = "HI" then treatment=1;if missing(treatment) then treatment=0;if hq ne "HI" and slappyear_hq<=2007 then delete;cohort=11; if year>=2002 then post=1;if missing(post) then post=0;run;
data c12; set mergeddata3;if 1997<=year<=2009;treat_event=2004;if hq = "MD" or hq = "MO" then treatment=1;if missing(treatment) then treatment=0;if hq ne "MD" and hq ne "MO" and slappyear_hq<=2009 then delete;cohort=12; if year>=2004 then post=1;if missing(post) then post=0;run;
data c13; set mergeddata3;if 1998<=year<=2010;treat_event=2005;if hq = "AR" or hq = "VT" then treatment=1;if missing(treatment) then treatment=0;if hq ne "AR" and hq ne "VT" and slappyear_hq<=2010 then delete;cohort=13; if year>=2005 then post=1;if missing(post) then post=0;run;
data c14; set mergeddata3;if 1999<=year<=2011;treat_event=2006;if hq = "AZ" then treatment=1;if missing(treatment) then treatment=0;if hq ne "AZ" and slappyear_hq<=2011 then delete;cohort=14; if year>=2006 then post=1;if missing(post) then post=0;run;
data c15; set mergeddata3;if 2000<=year<=2012;treat_event=2007;if hq = "IL" or hq = "VA" then treatment=1;if missing(treatment) then treatment=0;if hq ne "IL" and hq ne "VA" and slappyear_hq<=2012 then delete;cohort=15; if year>=2007 then post=1;if missing(post) then post=0;run;
data c17; set mergeddata3;if 2003<=year<=2015;treat_event=2010;if hq = "WA" then treatment=1;if missing(treatment) then treatment=0;if hq ne "WA" and slappyear_hq<=2015 then delete;cohort=17; if year>=2010 then post=1;if missing(post) then post=0;run;
data c18; set mergeddata3;if 2004<=year<=2016;treat_event=2011;if hq = "DC" or hq = "TX" then treatment=1;if missing(treatment) then treatment=0;if hq ne "DC" and hq ne "TX" and slappyear_hq<=2016 then delete;cohort=18; if year>=2011 then post=1;if missing(post) then post=0;run;
data c19; set mergeddata3;if 2007<=year<=2019;treat_event=2014;if hq = "OK" then treatment=1;if missing(treatment) then treatment=0;if hq ne "OK" and slappyear_hq<=2019 then delete;cohort=19;if year>=2014 then post=1;if missing(post) then post=0;run;
data c20; set mergeddata3;if 2009<=year<=2021;treat_event=2016;if hq = "KS" then treatment=1;if missing(treatment) then treatment=0;if hq ne "KS" and slappyear_hq<=2021 then delete;cohort=20;if year>=2016 then post=1;if missing(post) then post=0;run;
data c21; set mergeddata3;if 2010<=year<=2022;treat_event=2017;if hq = "CT" then treatment=1;if missing(treatment) then treatment=0;if hq ne "CT" and slappyear_hq<=2022 then delete;cohort=21;if year>=2017 then post=1;if missing(post) then post=0;run;
 data combined; set c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c17 c18 c19 c20 c21;run;
data sample; set combined;post_treat=post*treatment;if post_treat=treat_hq then check=1;if missing(check) then delete;run;


