cls
use "..\Revere_quarterly.dta", clear 

replace s_cusip = substr(s_cusip,1,6)
replace c_cusip = substr(c_cusip,1,6)

rename s_cusip cusip
merge m:1 cusip datacqtr using "..\revere_measures.dta"
drop if _merge ==2
drop _merge


rename cusip s_cusip
rename outdeg s_outdeg
rename indeg s_indeg
rename deg s_deg
rename bc s_bc
rename dist1 s_dist1
rename dist2 s_dist2

rename c_cusip cusip
merge m:1 cusip datacqtr using "..\revere_measures.dta"
drop if _merge ==2
drop _merge
rename cusip c_cusip
rename outdeg c_outdeg
rename indeg c_indeg
rename deg c_deg
rename bc c_bc
rename dist1 c_dist1
rename dist2 c_dist2

replace c_dist1 = . if c_dist1 == 99
replace c_dist2 = . if c_dist2 == 99

label var s_dist1 "Layer"
drop if datacqtr == 20031

// % of customers in the same layer or one layer below
gen downstream = c_dist1<=s_dist1
bys datacqtr s_dist1: egen mdownstream = mean(downstream)
by datacqtr s_dist1: replace mdownstream = . if _n>1
sum mdownstream if s_dist1<=8 & s_dist1>=0 & datacqtr==20044
sum mdownstream if s_dist1<=8 & s_dist1>=0 & datacqtr==20094
sum mdownstream if s_dist1<=8 & s_dist1>=0 & datacqtr==20144


// % of customers in one layer below (strictly downstream)
gen sdownstream = c_dist1<s_dist1
bys datacqtr s_dist1: egen msdownstream = mean(sdownstream)
by datacqtr s_dist1: replace msdownstream = . if _n>1
sum msdownstream if s_dist1<=8 & s_dist1>=1 & datacqtr==20044
sum msdownstream if s_dist1<=8 & s_dist1>=1 & datacqtr==20094
sum msdownstream if s_dist1<=8 & s_dist1>=1 & datacqtr==20144


