libname db "C:\Users\shaphanng.SMUSTF\OneDrive - Singapore Management University\Project\SLAPP\LNYZ\data\replication\hq";
proc import out=db.statehis datafile="C:\Users\shaphanng.SMUSTF\OneDrive - Singapore Management University\Project\SLAPP\LNYZ\data\replication\hq\LM_10-K_HeaderData.csv" dbms=csv replace; run;
data statehis;
set db.statehis;
if f_cik = cik;
f_fDATE1 = Input( Put( f_fDATE, 8.), Yymmdd10.);
conf_per_date = Input( Put( conf_per_rpt, 8.), Yymmdd10.);
format f_fDATE1 Date9.;
format conf_per_date Date9.;
fye_num = fye+0;
fyr = int(fye_num/100);
if fye = 'JUN' then fyr =6;
month = month(conf_per_date);
year_conf = year(conf_per_date);
*co, ct, nv, ny, pa, ut, wa;
if ba_state = "co" then ba_state = "CO";
if ba_state = "ct" then ba_state = "CT";
if ba_state = "nv" then ba_state = "NV";
if ba_state = "ny" then ba_state = "NY";
if ba_state = "pa" then ba_state = "PA";
if ba_state = "ut" then ba_state = "UT";
if ba_state = "wa" then ba_state = "WA";
*co, de, nv;
if state_of_incorp = "co" then state_of_incorp = "CO";
if state_of_incorp = "de" then state_of_incorp = "DE";
if state_of_incorp = "nv" then state_of_incorp = "NV";
run; *9996663;
proc sort data= statehis; by cik f_fdate1 conf_per_date; run;
* hq change;
data tmp1;
set statehis;
by cik f_fdate1;
if first.cik then _n = 1;
else _n+1;
keep cik f_ftype ba_state conf_per_date state_of_incorp _n sic_num f_fdate1;
run; *999663;
proc sql; *999663;
	create table tmp2 as select distinct
    a.*, b.state_of_incorp as incstate_1, b.ba_state as hqstate_1
    from tmp1 as a left join tmp1 as b
	on a.cik = b.cik and a._n = b._n+1;
	quit; *999663;
run;
data hq_change;
set tmp2;
if ba_state ^= hqstate_1;
run; *52410;
proc sort data= hq_change nodup; by cik f_fdate1; run;
data hq_change1;
set hq_change;
by cik f_fdate1;
if first.cik then _n1 = 1;
else _n1+1;
run; *52410;
proc sql; *52410;
	create table hq_change2 as select distinct
    a.cik, a.ba_state, a.f_fdate1 as begdate, b.f_fdate1 as enddate, a.conf_per_date
	from hq_change1 as a left join hq_change1 as b
	on a.cik = b.cik and a._n1 = b._n1-1;
	quit; *52410;
run;
proc sort data= hq_change2 nodup; by cik begdate; run;
data db.hq_change;
set hq_change2;
run;

