libname comp "E:/compustat";
libname industry "E:/industry";

proc import out= paypeer datafile = "D:\Dropbox\Research\Network and compensation\20191111\newpaypeer0616_1718.dta";
run;

proc sql;
create table paypeer1 as select distinct a.*, b.sic, b.sale, c.sic as sic_peer, c.sale as sale_peer
from paypeer as a left join comp.funda as b
on a.gvkey=b.gvkey and a.fyear=b.fyear+1
left join comp.funda as c
on a.fyear=c.fyear+1 and a.gvkey_peer=c.gvkey;
quit;

proc sort data=paypeer1 nodupkey;
by fyear gvkey gvkey_peer;
run;

data hp;
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;

proc sql;
create table paypeer2 as select distinct a.*, b.score
from paypeer1 as a left join hp as b
on a.gvkey=b.gvkey1 and a.gvkey_peer=b.gvkey2 and a.fyear=b.year+1;
quit;

proc sql;
create table paypeer3 as select distinct a.*, b.fyear as flag
from paypeer2 as a left join paypeer2 as b
on a.gvkey=b.gvkey_peer and a.gvkey_peer=b.gvkey and a.fyear=b.fyear;
quit;

data paypeer4;
set paypeer3;
same_sic2=0;
same_hp2=0;
same_size=0;
if floor(sic/100)=floor(sic_peer/100) then same_sic2=1;
if score~=. then same_hp2=1;
if sale_peer*0.5<=sale<=sale_peer*2 then same_size=1;
pay_dif=tdc1_peer-tdc1;
if flag=. then flag=0;
if flag~=0 then flag=1;
run;

proc sql;
create table summary as select distinct fyear, gvkey, sum(same_sic2)/count(same_sic2) as same_sic2, sum(same_hp2)/count(same_hp2) as same_hp2,
							   sum(same_size)/count(same_size) as same_size, median(pay_dif) as pay_dif, count(gvkey_peer) as benchmarking, sum(flag) as interlock
from paypeer4
group by fyear,gvkey;
quit;

