import pandas as pd

filepath = "~/Downloads/LM_EDGAR_10X_Header_1994_2018.csv"

if __name__ == "__main__":

    df = pd.read_csv(
        filepath,
        usecols=["cik", "file_date", "ba_state"],
        dtype={"cik": str},
        parse_dates=["file_date"],
    )
    # Some `ba_stata` codes are lowercase
    df["ba_state"] = df["ba_state"].str.upper()
    # Some  `ba_state` codes are not valid US states
    df = df[df["ba_state"].str.isalpha() & ~pd.isnull(df["ba_state"])]
    df.drop_duplicates().to_stata(
        "~/Downloads/historical_state_1994_2018.dta",
        write_index=False,
        convert_dates={"file_date": "td"},
    )

libname hs "~/historical_state";

/* Historical HQ state (1994 to 2018) from augmented 10-X header dataset */
proc import datafile="~/historical_state/historical_state_1994_2018.dta"
	out=historical_state_1994_2018 dbms=stata replace;
/* Historical HQ state (1969 to 2003) from Bai, Fairhurst and Serfling (2020 RFS) */
proc import datafile="~/historical_state/hist_headquarters_Bai_et_al.dta"
	out=hist_headquarters_Bai_et_al dbms=stata replace;

/* Build the post-1994 dataset using SEC filings */
proc sql;
create table funda as 
select gvkey, cik, datadate, fyear from comp.funda
where indfmt= 'INDL' and datafmt='STD' and popsrc='D' and consol='C'
and year(datadate) between 1994 and 2018
/* "For firms that change fiscal year within a calendar year, 
	we take the last reported date when extracting financial data. 
	This leaves us with one set of observations for each firm (gvkey) in each year." 
	-- Pelueger, Siriwardane and Sunderam (2020 QJE) */
group by gvkey, fyear having datadate=max(datadate);

create table firm_historical_state as 
select a.*, b.ba_state as state_sec label="State from SEC filings"
from funda as a left join historical_state as b 
on a.cik=b.cik and year(a.datadate)=year(b.file_date) and b.file_date<=a.datadate
group by a.gvkey, a.datadate
/* use the SEC filing closet to and before the Compustat datadate */
having b.file_date=max(b.file_date);

create table historical_state_1994_2018 as
select a.*, b.state as state_comp label="State from Compustat"
from firm_historical_state as a left join comp.company as b 
on a.gvkey=b.gvkey
order by a.gvkey, a.datadate;
quit;

/* Sanity check: no duplicated gvkey-fyear */
proc sort data=historical_state_1994_2018 nodupkey; by gvkey datadate; run;


proc sql;
create table hist_headquarters_Bai_et_al as 
select put(gvkeyn, z6.) as gvkey, fyear, state 
from hist_headquarters_Bai_et_al;
quit;

/* Stack together the two datasets */
data states; 
set hist_headquarters_Bai_et_al 
	historical_state_1994_2018(where=(fyear>2003) keep=gvkey fyear state:);
run;

proc sql;
create table hs.corrected_hist_state_1969_2018 as 
select *, coalesce(state, state_sec, state_comp) as corrected_state
from states where not missing(calculated corrected_state)
order by gvkey, fyear;
quit;

/* Sanity check: no duplicated gvkey-fyear */
proc sort data=hs.corrected_hist_state_1969_2018 nodupkey; by gvkey fyear; run;