options noerrorabend;

libname comp "E:\compensation";
libname crsp "E:\crsp";
libname link "E:\Linktable";
libname fund "E:\Compustat";
libname index "E:\index_constituent";
libname ibes "E:\ibes";

***********************************
******job hopping destination******
***********************************;
data comp;
set comp.annual_comp;
run;

* Find incidence of hopping;
proc sort data=comp nodupkey;
by execid year gvkey;
run;

proc sql;
create table hopping as select a.execid, a.year, a.gvkey, a.tdc1, a.ceoann, a.cfoann, a.coname, a.exec_fullname, b.gvkey as gvkey_new, b.year as year_new, b.tdc1 as tdc1_new, b.ceoann as ceoann_new, b.cfoann as cfoann_new, b.coname as coname_new
from comp as a, comp as b
where a.execid=b.execid and b.year-2<=a.year<=b.year and a.gvkey~=b.gvkey;
quit;

data hopping1;
set hopping;
gap=year_new-year;
pay_change=tdc1_new/tdc1-1;
if pay_change=. then pay_change=0;
run;

proc sql;
create table hopping0 as select a.*
from hopping1 as a, comp as b
where a.gap=0 and a.year=b.year+1 and a.gvkey=b.gvkey and a.execid=b.execid;
quit;

data hopping1;
set hopping1;
if gap>0;
run;

data hopping1;
set hopping0 hopping1;
run;

proc sort data=hopping1;
by execid gvkey descending year gap;
run;

data hopping2;
set hopping1;
by execid gvkey descending year gap;
if first.gvkey;
run;

*Network datasets;
PROC IMPORT OUT= network DATAFILE= "D:\Dropbox\Research\Network and compensation\Analysis_Network analysis software related\clusters.csv" 
            DBMS=CSV REPLACE;
GETNAMES=YES;
RUN;

proc sql;
create table network1 as select distinct a.cik, a.year, a.louvain, a.louvain_small, a.disclose, b.gvkey, c.sale, c.mkvalt, c.sic, c.prcc_f, c.csho, c.at, c.state, c.ceq, (c.ib/c.at) as roa, c.datadate, c.lpermno, c.gsector
from network as a, link.cik_gvkey as b, fund.ccmfunda as c
where a.cik=input(b.cik, 12.) and year(b.FNDATE)<=a.year<=year(b.LNDATE) and a.year=c.fyear and b.gvkey=c.gvkey;
quit;

data network2;
set network1;
if mkvalt=. then mkvalt=prcc_f*csho;
mtb=mkvalt/ceq;
drop prcc_f csho ceq;
run;

/* past 12-month return */
data msf;
set crsp.msf;
if not(missing(ret));
year=year(date);
keep permno date year ret;
run;

proc sql;
create table ret as select distinct a.*, b.ret as return, b.date as bdate
from msf as a, msf as b
where a.permno=b.permno and 0<=intck("month", b.date, a.date)<=11;
quit;

proc sort data=ret;
by permno date bdate;
run;

proc sql;
create table ret1 as select distinct a.permno, a.date, a.year, exp(sum(log(1+a.return)))-1 as ret
from ret as a
group by permno, date;
quit;

proc sql;
create table network3 as select distinct a.*, b.ret
from network2 as a left join ret1 as b
on year(a.datadate)=year(b.date) and month(a.datadate)=month(b.date) and a.lpermno=b.permno;
quit;

/* index membership */
proc sql;
create table network4 as select distinct a.*, b.indexcat as dow
from network3 as a left join index.dow_nasdaq as b
on b.from<=a.datadate and (a.datadate<=b.thru or b.thru=.) and a.gvkey=b.gvkey and b.conm="Dow Jones Industrials-30 Stk";
quit;

proc sql;
create table network5 as select distinct a.*, b.permno as sp500
from network4 as a left join index.dsp500list as b
on b.start<=a.datadate<=b.ending and a.lpermno=b.permno;
quit;

/* CEO-chair */
data ceo_chair;
set comp;
if ceoann="CEO";
ceo_chair=0;
if find(TITLEANN, 'chmn', 'i') + find(TITLEANN, 'chairman', 'i')>0 then ceo_chair=1;
keep gvkey year ceo_chair TITLEANN;
run;

proc sql;
create table network6 as select distinct a.*, b.ceo_chair
from network5 as a left join ceo_chair as b
on a.year=b.year and a.gvkey=b.gvkey;
quit;

/* additional ones (mainly from BLN2011) */
* segments;
proc sql;
create table segment as select distinct gvkey, datadate, sum(case when STYPE="BUSSEG" then 1 else 0 end) as bus_seg, sum(case when STYPE="GEOSEG" then 1 else 0 end) as geo_seg
from fund.segment
group by gvkey, datadate;
quit;

proc sql;
create table network7 as select distinct a.*, b.bus_seg, b.geo_seg
from network6 as a left join segment as b
on a.datadate=b.datadate and a.gvkey=b.gvkey;
quit;

* same consultant;
proc import out=consult datafile = "D:\Dropbox\Research\Network and compensation\20191111\Control_compconsultant.dta";
run;

proc sql;
create table network8 as select distinct a.*, b.compconsultantid
from network7 as a left join consult as b
on a.year=b.fyear and a.cik=input(b.cik, 12.);
quit;

* merge in ff48 (for industry return);
%include "D:\Dropbox\code help\SAS Macro\FamaFrenchIndustries-main\industries_ff48.sas";

%ind_ff48(dset=network8, outp=network9, sic=sic, bin_var=fama, ind_code=ind_code);

data network9;
set network9;
ff_ind=upcase(ff_ind);
drop ind_code fama:;
run;

proc sort data=network9 nodupkey;
by gvkey year;
run;

proc sql;
create table turnover as select distinct a.*, 
b.cik as cik_new, b.gvkey as gvkey_new, b.louvain as louvain_new, b.louvain_small as louvain_small_new, 
b.sale as sale_new, b.mkvalt as mkvalt_new, b.sic as sic_new, b.at as at_new, b.mtb as mtb_new, b.ret as ret_new, b.roa as roa_new, b.state as state_new, b.gsector as gsector_new, b.disclose as disclose_new, 
b.dow as dow_new, b.sp500 as sp500_new, b.ceo_chair as ceo_chair_new, b.bus_seg as bus_seg_new, b.geo_seg as geo_seg_new, b.compconsultantid as compconsultantid_new, b.ff_ind as ff_ind_new
from network9 as a, network9 as b
where a.year=b.year and a.disclose="True";
quit;

proc sql;
create table hopping3 as select distinct a.*, b.year as year_old, b.year_new, b.pay_change, b.gap, b.ceoann, b.cfoann, b.ceoann_new, b.cfoann_new, b.coname, b.coname_new, b.exec_fullname
from turnover as a left join hopping2 as b
on a.year+1=b.year and a.gvkey=b.gvkey and a.gvkey_new=b.gvkey_new;
quit;

proc sort data=hopping3;
by year gvkey gvkey_new descending ceoann descending ceoann_new;
run;

proc sort data=hopping3 nodupkey;
by year gvkey gvkey_new;
run;

data hopping4;
set hopping3;
hop=1;
same_sic3=0;
if pay_change=. then hop=0;
if floor(sic/10)=floor(sic_new/10) then same_sic3=1;
same_sic2=0;
if floor(sic/100)=floor(sic_new/100) then same_sic2=1;
same_size1=0;
if 0.5*mkvalt<=mkvalt_new<=2*mkvalt then same_size1=1;
same_size2=0;
if 0.5*sale<=sale_new<=2*sale then same_size2=1;
same_size3=0;
if 0.5*at<=at_new<=2*at then same_size3=1;
same_roa=0;
if 0.5*roa<=roa_new<=2*roa then same_roa=1;
same_lou=0;
if louvain=louvain_new then same_lou=1;
same_lou_small=0;
if louvain_small=louvain_small_new then same_lou_small=1;
same_state=0;
if state=state_new then same_state=1;
same_gic=0;
if gsector=gsector_new then same_gic=1;
same_dow=0;
if not(missing(dow)) and not(missing(dow_new)) then same_dow=1;
same_sp500=0;
if not(missing(sp500)) and not(missing(sp500_new)) then same_sp500=1;
same_nonsp500=0;
if missing(sp500) and missing(sp500_new) then same_nonsp500=1;
same_ceo_chair=0;
if ceo_chair=1 and ceo_chair_new=1 then same_ceo_chair=1;
same_ceo_nonchair=0;
if ceo_chair=0 and ceo_chair_new=0 then same_ceo_nonchair=1;
same_multi_bus=0;
if bus_seg>1 and bus_seg_new>1 then same_multi_bus=1;
same_single_bus=1;
if bus_seg>1 or bus_seg_new>1 then same_single_bus=0;
same_multi_geo=0;
if geo_seg>1 and geo_seg_new>1 then same_multi_geo=1;
same_single_geo=1;
if geo_seg>1 or geo_seg_new>1 then same_single_geo=0;
same_consult=0;
if compconsultantid=compconsultantid_new and not(missing(compconsultantid)) and not(missing(compconsultantid_new)) then same_consult=1;
drop state state_new dow dow_new sp500 sp500_new ceo_chair ceo_chair_new bus_seg bus_seg_new geo_seg geo_seg_new compconsultantid compconsultantid_new;
run;

* direct peer;
PROC IMPORT OUT= peer DATAFILE= "D:\Dropbox\Research\Network and compensation\20191111\network1212.xlsx" 
            DBMS=EXCEL REPLACE;
SHEET="Sheet1"; 
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

proc sql;
create table hopping5 as select distinct a.*, b.fyear as flag1
from hopping4 as a left join peer as b
on a.year=b.fyear and (a.cik=b.cik_own and a.cik_new=b.cik_peer) ;
quit;

proc sql;
create table hopping6 as select distinct a.*, b.fyear as flag0
from hopping5 as a left join peer as b
on a.year=b.fyear and (a.cik=b.cik_peer and a.cik_new=b.cik_own) ;
quit;

data hopping6;
set hopping6;
direct1=1;
if missing(flag1) then direct1=0;
direct0=1;
if missing(flag0) then direct0=0;
direct=max(direct1,direct0);
mutual=min(direct1,direct0);
drop flag0 flag1;
run;

* peer of peers;
proc sql;
create table peer1 as select distinct a.cik_own as cik1, b.cik_peer as cik2, a.fyear as flag11
from peer as a, peer as b
where a.fyear=b.fyear and a.cik_peer=b.cik_own;
quit;

proc sort data=peer1 nodupkey;
by flag11 cik1 cik2;
run;

proc sql;
create table peer2 as select distinct a.cik_own as cik1, b.cik_own as cik2, a.fyear as flag10
from peer as a, peer as b
where a.fyear=b.fyear and a.cik_peer=b.cik_peer;
quit;

proc sort data=peer2 nodupkey;
by flag10 cik1 cik2;
run;

proc sql;
create table peer3 as select distinct a.cik_peer as cik1, b.cik_peer as cik2, a.fyear as flag01
from peer as a, peer as b
where a.fyear=b.fyear and a.cik_own=b.cik_own;
quit;

proc sort data=peer3 nodupkey;
by flag01 cik1 cik2;
run;

*A->B->C;
proc sql;
create table hopping7 as select distinct a.*, b.flag11
from hopping6 as a left join peer1 as b
on a.year=b.flag11 and a.cik=b.cik1 and a.cik_new=b.cik2;
quit;

* A->B<-C;
proc sql;
create table hopping8 as select distinct a.*, b.flag10
from hopping7 as a left join peer2 as b
on a.year=b.flag10 and a.cik=b.cik1 and a.cik_new=b.cik2;
quit;

*A<-B<-C;
proc sql;
create table hopping9 as select distinct a.*, b.flag11 as flag00
from hopping8 as a left join peer1 as b
on a.year=b.flag11 and a.cik=b.cik2 and a.cik_new=b.cik1;
quit;

* A<-B->C;
proc sql;
create table hopping10 as select distinct a.*, b.flag01 as flag01
from hopping9 as a left join peer3 as b
on a.year=b.flag01 and a.cik=b.cik1 and a.cik_new=b.cik2;
quit;

* Hoberg and Phillips;
data hp2;
infile "E:\Industry\tnic2_data.txt" encoding="utf-8" delimiter = '09'x Missover Dsd lrecl=32767 firstobs=2;
input year gvkey1$ gvkey2$ score;
format gvkey1 $6. gvkey2 $6. score 10.6;
informat gvkey1 $6. gvkey2 $6. score 10.6;
if gvkey1=gvkey2 then delete;
gvkey1=cats(repeat('0',6-length(gvkey1)-1), gvkey1);
gvkey2=cats(repeat('0',6-length(gvkey2)-1), gvkey2);
run;

data hp3;
infile "E:\Industry\tnic3_data.txt" encoding="utf-8" delimiter = '09'x Missover Dsd lrecl=32767 firstobs=2;
input year gvkey1$ gvkey2$ score;
format gvkey1 $6. gvkey2 $6. score 10.6;
informat gvkey1 $6. gvkey2 $6. score 10.6;
if gvkey1=gvkey2 then delete;
gvkey1=cats(repeat('0',6-length(gvkey1)-1), gvkey1);
gvkey2=cats(repeat('0',6-length(gvkey2)-1), gvkey2);
run;

proc sql;
create table hopping11 as select distinct a.*, b.score as score2
from hopping10 as a left join hp2 as b
on a.year=b.year and a.gvkey=b.gvkey1 and a.gvkey_new=b.gvkey2;
quit;

proc sql;
create table hopping12 as select distinct a.*, b.score as score3
from hopping11 as a left join hp3 as b
on a.year=b.year and a.gvkey=b.gvkey1 and a.gvkey_new=b.gvkey2;
quit;

proc sql;
create table hopping13 as select distinct a.*, b.pay_change as history_hop
from hopping12 as a left join hopping2 as b
on a.year>=b.year and a.gvkey=b.gvkey and a.gvkey_new=b.gvkey_new;
quit;

proc sql;
create table hopping14 as select distinct a.*, b.pay_change as history_hop1
from hopping13 as a left join hopping2 as b
on a.year>=b.year and a.gvkey=b.gvkey_new and a.gvkey_new=b.gvkey;
quit;

* industry return correlation;
PROC IMPORT OUT=ret_ff48 DATAFILE= "E:\ff\ff48_vw.csv" 
            DBMS=CSV REPLACE;
GETNAMES=YES;
RUN;

proc transpose data=ret_ff48 out=return_ff48 name=ff48 prefix=ret;
by date;
run;

data return_ff48;
set return_ff48;
year=floor(date/10000);
if ret1=-99.99 then delete;
if ret1=-999 then delete;
if year>=2004;
run;

proc sql;
create table corr_ff48 as select distinct a.*, b.ff48 as ff48_peer, b.ret1 as ret1_peer
from return_ff48 as a, return_ff48 as b
where a.date=b.date;
quit;

proc sort data=corr_ff48;
by ff48 ff48_peer year;
run;

proc corr data=corr_ff48 noprint outp=corr_ff;
by ff48 ff48_peer year;
var ret1 ret1_peer;
run;

data corr_ff;
set corr_ff;
if _NAME_='ret1';
rename ret1_peer=ind_corr;
keep ff48 ff48_peer year ret1_peer;
run;

proc sql;
create table hopping15 as select distinct a.*, b.ind_corr
from hopping14 as a left join corr_ff as b
on a.ff_ind=upcase(b.ff48) and a.ff_ind_new=upcase(b.ff48_peer) and a.year=b.year;
quit;

* final cleanup;
data hopping_final;
set hopping15;
indirect11=1;
if missing(flag11) then indirect11=0;
indirect10=1;
if missing(flag10) then indirect10=0;
indirect01=1;
if missing(flag01) then indirect01=0;
indirect00=1;
if missing(flag00) then indirect00=0;
same_hp2=1;
if missing(score2) then same_hp2=0;
same_hp3=1;
if missing(score3) then same_hp3=0;
indirect=max(indirect11, indirect01, indirect10, indirect00);
flow_history=1;
if missing(history_hop) and missing(history_hop1) then flow_history=0;
drop score2 score3 flag11 flag10 flag01 flag00 history_hop;
run;

proc sort data=hopping_final nodupkey;
by year gvkey gvkey_new;
run;


*************************************
*****Adding additional variables*****
*************************************;
* Analyst peers;
proc sql;
create table analyst as select distinct a.*, b.gvkey
from ibes.analyst_detail as a, ibes.cibeslnk as b
where a.ticker=b.ticker and b.fdate<=a.anndats<=b.ldate;
quit;

data analyst;
set analyst;
if measure="EPS" and (FPI="6" or FPI="7");
year=year(anndats);
keep gvkey year analys;
run;

proc sort data=analyst nodupkey;
by gvkey year analys;
run;

proc sql;
create table analyst1 as select distinct a.*, count(a.gvkey) as cov_num
from analyst as a
group by gvkey, year;
quit;

proc sql;
create table analyst2 as select distinct a.gvkey, a.year, b.gvkey as gvkey1, count(b.analys)/a.cov_num as analyst_peer_frac
from analyst1 as a left join analyst1 as b
on a.year=b.year and a.analys=b.analys and a.gvkey~=b.gvkey
group by a.gvkey, a.year, b.gvkey;
quit;

proc rank data=analyst2 out=analyst3 ties=low descending;
by gvkey year;
var analyst_peer_frac;
ranks rank;
run;

proc sql;
create table hopping16 as select distinct a.*, b.rank as analyst_rank
from hopping_final as a left join analyst3 as b
on a.year=b.year and a.gvkey=b.gvkey and a.gvkey_new=b.gvkey1;
quit;

* Euclid distance;
%macro euclid(year);
	proc import datafile="D:\Dropbox\Research\Network and compensation\JAR revision\R1\Euclidean distance\pay&year.-Euc-C.xlsx" dbms=xlsx out=wide_C_&year replace;
	run;

	proc transpose data=wide_C_&year out=long_C_&year;
	by ID;
	run;

	proc import datafile="D:\Dropbox\Research\Network and compensation\JAR revision\R1\Euclidean distance\pay&year.-Euc-R.xlsx" dbms=xlsx out=wide_R_&year replace;
	run;

	proc transpose data=wide_R_&year out=long_R_&year;
	by ID;
	run;

	data long_C_&year;
	set long_C_&year (rename=(ID=CIK_own _LABEL_=CIK_peer COL1=euclid_distance_C));
	year=&year;
	drop _NAME_;
	run;

	data long_R_&year;
	set long_R_&year (rename=(ID=CIK_own _LABEL_=CIK_peer COL1=euclid_distance_R));
	year=&year;
	drop _NAME_;
	run;
%mend euclid;

%euclid(2006);
%euclid(2007);
%euclid(2008);
%euclid(2009);
%euclid(2010);
%euclid(2011);
%euclid(2012);
%euclid(2013);
%euclid(2014);
%euclid(2015);
%euclid(2016);
%euclid(2017);
%euclid(2018);

data euclid_distance_C;
set long_C_2006-long_C_2018;
cik_peer1=input(cik_peer, 12.);
run;

data euclid_distance_R;
set long_R_2006-long_R_2018;
cik_peer1=input(cik_peer, 12.);
run;

proc sql;
create table euclid_distance as select distinct a.*, b.euclid_distance_R
from euclid_distance_C as a left join euclid_distance_R as b
on a.year=b.year and a.cik_own=b.cik_own and a.cik_peer1=b.cik_peer1;
quit;

proc sql;
create table hopping17 as select distinct a.*, b.euclid_distance_C, b.euclid_distance_R
from hopping16 as a left join euclid_distance as b
on a.year=b.year and a.cik=b.cik_own and a.cik_new=b.cik_peer1;
quit;

/* merge in firm and peer total compensation */
data comp;
set comp;
keep gvkey year tdc1;
run;

proc sql;
create table hopping18 as select distinct a.*, b.tdc1
from hopping17 as a left join comp as b
on a.gvkey=b.gvkey and a.year=b.year;
quit;

proc sql;
create table hopping19 as select distinct a.*, b.tdc1 as tdc1_new
from hopping18 as a left join comp as b
on a.gvkey_new=b.gvkey and a.year=b.year;
quit;

proc sort data=hopping19 nodupkey;
by year gvkey gvkey_new;
run;
