libname crsp "E:\crsp";
libname comp "E:\Compustat";
libname exec "E:\compensation";

data msf;
set crsp.msf;
if not(missing(ret));
year=year(date);
mkvalt=prc*shrout;
keep permno date year ret mkvalt prc shrout;
run;

proc sort data=msf nodupkey;
by permno date;
run;

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

data funda;
set comp.ccmfunda_0428;
sic2=floor(sic/100);
if mkvalt=. then mkvalt=prcc_f*csho;
roa=ib/at;
keep gvkey lpermno datadate fyear fyrc sic2 naics sale mkvalt roa at;
run;

data comp;
set exec.Annual_comp;
if ceoann="CEO";
run;

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

data cpi;
set crsp.inflation;
run;

data market;
set crsp.market;
run;

/* Daniel, Li and Naveen */
data comp1;
set comp;
if year-year(becameceo)<2 and year(becameceo)~=. then delete;
if year>=year(LEFTOFC) and year(LEFTOFC)~=. then delete;
run;

proc sql;
create table ret1 as select distinct a.*, b.gvkey, b.sic2, c.tdc1, c.co_per_rol, c.year as fyear, d.ewretd
from msf as a, funda as b, comp1 as c, market as d
where 0<=intck("month", a.date,b.datadate)<=11 and b.fyear=c.year and a.permno=b.lpermno and b.gvkey=c.gvkey and a.date=d.date;
quit;

/*****************************************
Trim or winsorize macro
* byvar = none for no byvar;
* type  = delete/winsor (delete will trim, winsor will winsorize;
*dsetin = dataset to winsorize/trim;
*dsetout = dataset to output with winsorized/trimmed values;
*byvar = subsetting variables to winsorize/trim on;
****************************************/
  
%macro winsor(dsetin=, dsetout=, byvar=none, vars=, type=winsor, pctl=1 99);
  
%if &dsetout = %then %let dsetout = &dsetin;
     
%let varL=;
%let varH=;
%let xn=1;
  
%do %until ( %scan(&vars,&xn)= );
    %let token = %scan(&vars,&xn);
    %let varL = &varL &token.L;
    %let varH = &varH &token.H;
    %let xn=%EVAL(&xn + 1);
%end;
  
%let xn=%eval(&xn-1);
  
data xtemp;
    set &dsetin;
    run;
  
%if &byvar = none %then %do;
  
    data xtemp;
        set xtemp;
        xbyvar = 1;
        run;
  
    %let byvar = xbyvar;
  
%end;
  
proc sort data = xtemp;
    by &byvar;
    run;
  
proc univariate data = xtemp noprint;
    by &byvar;
    var &vars;
    output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H;
    run;
  
data &dsetout;
    merge xtemp xtemp_pctl;
    by &byvar;
    array trimvars{&xn} &vars;
    array trimvarl{&xn} &varL;
    array trimvarh{&xn} &varH;
  
    do xi = 1 to dim(trimvars);
  
        %if &type = winsor %then %do;
            if not missing(trimvars{xi}) then do;
              if (trimvars{xi} < trimvarl{xi}) then trimvars{xi} = trimvarl{xi};
              if (trimvars{xi} > trimvarh{xi}) then trimvars{xi} = trimvarh{xi};
            end;
        %end;
  
        %else %do;
            if not missing(trimvars{xi}) then do;
              if (trimvars{xi} < trimvarl{xi}) then delete;
              if (trimvars{xi} > trimvarh{xi}) then delete;
            end;
        %end;
  
    end;
    drop &varL &varH xbyvar xi;
    run;
  
%mend winsor;

%winsor(dsetin=ret1, dsetout=ret1_win, byvar=none, vars=ret, type=winsor, pctl=1 99);

proc sql;
create table ret2 as select distinct a.*, mean(a.ret) as ret_ind_ew
from ret1_win as a
group by sic2, date;
quit;

proc sort data=ret2 nodupkey;
by gvkey date;
run;

proc sort data=ret2;
by co_per_rol fyear;
run;

proc reg data=ret2 noprint;
by co_per_rol;
model ret=ret_ind_ew ewretd/ noint;
output out=ret3 p=luck r=skill;
quit;

proc sql;
create table luck as select a.co_per_rol, a.fyear, 12*mean(a.luck) as luck, 12*mean(a.skill) as skill
from ret3 as a
group by co_per_rol, fyear;
quit;

proc sql;
create table luck1 as select distinct a.*, var(b.luck) as luck_var, var(b.skill) as skill_var
from luck as a left join ret3 as b
on a.co_per_rol=b.co_per_rol and a.fyear-4<=b.fyear<=a.fyear
group by a.co_per_rol, a.fyear;
quit;

/* RPE Following Albuquerque(2009)*/
* Calculate rolling-window return;
proc sql;
create table ret4 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=ret4 nodupkey;
by permno date bdate;
run;

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

%winsor(dsetin=ret5, dsetout=ret5_win, byvar=none, vars=ret, type=winsor, pctl=1 99);

proc sql;
create table ret6 as select distinct a.*, b.gvkey, b.fyear, b.fyrc, b.sic2, c.cpiret
from ret5_win as a, funda as b, cpi as c
where a.year=year(c.caldt) and 0<=intck("month",b.datadate,a.date)<=11 and a.permno=b.lpermno and b.at>10;
quit;

proc sort data=ret6;
by sic2 date mkvalt;
run;

proc rank data=ret6 out=ret7 groups=4;
var mkvalt;
by sic2 date;
ranks rank;
run;

proc sql;
create table ret8 as select distinct a.*, b.gvkey as gvkey_peer, (1+a.ret)/(1+a.cpiret)-1 as ret_annual, (1+b.ret)/(1+b.cpiret)-1 as peer_return_annual, b.mkvalt as mkvalt_peer 
from ret7 as a, ret7 as b
where a.date=b.date and a.gvkey~=b.gvkey and a.sic2=b.sic2 and a.rank=b.rank and month(a.date)=a.fyrc;
quit;

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

proc sql;
create table ret9 as select distinct a.gvkey, a.fyear, a.ret_annual, mean(a.peer_return_annual) as peer_ret_annual, count(a.peer_return_annual) as num_peer
from ret8 as a
group by permno, date;
quit;

proc sql;
create table ret10 as select distinct a.*, b.gvkey as gvkey_peer, (1+a.ret)/(1+a.cpiret)-1 as ret_annual, (1+b.ret)/(1+b.cpiret)-1 as peer_return_annual, b.mkvalt as mkvalt_peer 
from ret7 as a, ret7 as b
where a.date=b.date and a.gvkey~=b.gvkey and a.sic2=b.sic2 and month(a.date)=a.fyrc;
quit;

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

proc sql;
create table ret11 as select distinct a.gvkey, a.fyear, a.ret_annual, mean(a.peer_return_annual) as peer_ret_annual, count(a.peer_return_annual) as num_peer
from ret10 as a
group by permno, date;
quit;

proc sql;
create table ret12 as select distinct a.*, b.peer_ret_annual as peer_ret_annual1, b.num_peer as num_peer1
from ret9 as a left join ret11 as b
on a.gvkey=b.gvkey and a.fyear=b.fyear;
quit;

data ret13;
set ret12;
if fyear>=1992;
if num_peer<=2 then peer_ret_annual=peer_ret_annual1;
if num_peer<=2 then num_peer=num_peer1;
if num_peer1<=2 then delete;
drop peer_ret_annual1 num_peer1;
run;

proc univariate data=ret13;
var ret_annual peer_ret_annual;
run;

proc reg data=ret13 noprint;
model ret_annual=peer_ret_annual;
output out=ret14 p=SystFirmPerf r=UnsystFirmPerf;
quit;

proc sql;
create table rpe as select a.*, b.co_per_rol,log(b.tdc1+1) as tdc
from ret14 as a left join comp as b
on a.gvkey=b.gvkey and a.fyear=b.year;
quit;

/* using Hoberg and Phillips industry */
data hp;
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 ret15 as select distinct a.*, b.gvkey as gvkey_peer, (1+a.ret)/(1+a.cpiret)-1 as ret_annual, (1+b.ret)/(1+b.cpiret)-1 as peer_return_annual, b.mkvalt as mkvalt_peer
from ret6 as a, ret6 as b, hp as c
where a.date=b.date and a.gvkey~=b.gvkey and a.gvkey=c.gvkey1 and b.gvkey=c.gvkey2 and month(a.date)=a.fyrc and a.fyear=c.year;
quit;

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

proc sql;
create table ret16 as select distinct a.gvkey, a.fyear as fyear, a.ret_annual, mean(a.peer_return_annual) as peer_ret_annual, count(a.peer_return_annual) as num_peer
from ret15 as a
group by permno, date;
quit;

data ret17;
set ret16;
if fyear>=1992;
if num_peer<=2 then delete;
run;

proc univariate data=ret17;
var ret_annual peer_ret_annual;
run;

proc reg data=ret17 noprint;
model ret_annual=peer_ret_annual;
output out=ret18 p=SystFirmPerf r=UnsystFirmPerf;
quit;


/* Add control variables in Albuquerque (2014); */
proc sql;
create table ret19 as select distinct a.permno, a.date, a.year, a.mkvalt, 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)<=23 and a.year>=2000;
quit;

proc sort data=ret19 nodupkey;
by permno date bdate;
run;

proc sql;
create table ret20 as select distinct a.*, b.gvkey, b.fyear, b.sic2
from ret19 as a, funda as b
where 0<=intck("month",b.datadate,a.date)<=11 and a.permno=b.lpermno and b.at>10 and month(a.date)=b.fyrc;
quit;

* industry returns;
proc sql;
create table msf1 as select distinct a.*, b.gvkey, b.fyear, b.sic2
from msf as a, funda as b
where 0<=intck("month",b.datadate,a.date)<=11 and a.permno=b.lpermno;
quit;

proc sort data=msf1;
by sic2 date mkvalt;
run;

proc rank data=msf1 out=msf2 groups=4;
var mkvalt;
by sic2 date;
ranks rank;
run;

proc sql;
create table ret_ind as select distinct a.*, mean(a.ret) as ret_ind_ew, count(a.ret) as peer_num
from msf2 as a
group by sic2, rank, date;
quit;

* merge in industry-size returns;
proc sql;
create table ret21 as select distinct a.*, b.rank
from ret20 as a, msf2 as b
where a.date=b.date and a.gvkey=b.gvkey;
quit;

proc sql;
create table ret22 as select distinct a.*, b.ret_ind_ew
from ret21 as a, ret_ind as b
where a.bdate=b.date and a.sic2=b.sic2 and a.rank=b.rank;
quit;

%winsor(dsetin=ret22, dsetout=ret22_win, byvar=none, vars=return, type=winsor, pctl=1 99);

* run regressions;
proc sort data=ret22_win nodupkey;
by gvkey date bdate;
run;

proc reg data=ret22_win outest=coef noprint;
by gvkey date;
model return = ret_ind_ew;
output out=resid r=residuals;
run;

proc sql;
create table resid1 as select distinct a.gvkey, a.fyear, a.date, var(a.residuals) as idio_var, count(a.residuals) as freq
from resid as a
group by gvkey, date;
quit;

proc sql;
create table control as select distinct a.*, b.ret_ind_ew as corr
from resid1 as a, coef as b
where a.gvkey=b.gvkey and a.date=b.date and a.freq>=12;
quit;

proc sort data=control nodupkey;
by gvkey date;
run;

* Combine RPE, pay-for-luck, and controls;
proc sql;
create table rpe1 as select distinct a.*, b.ret_annual as ret_annual_hp, b.peer_ret_annual as peer_ret_annual_hp, b.SystFirmPerf as SystFirmPerf_hp, b.UnsystFirmPerf as UnsystFirmPerf_hp
from rpe as a full join ret18 as b
on a.gvkey=b.gvkey and a.fyear=b.fyear;
quit;

proc sql;
create table rpe2 as select distinct a.*, b.luck, b.skill, b.luck_var, b.skill_var
from rpe1 as a full join luck1 as b
on a.co_per_rol=b.co_per_rol and a.fyear=b.fyear;
quit;

proc sql;
create table rpe3 as select distinct a.*, b.idio_var, b.corr
from rpe2 as a full join control as b
on a.gvkey=b.gvkey and a.fyear=b.fyear;
quit;

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