**** 7_Change_Event_MacroImpact.do

capture log close
log using "7_Change_Event_MacroImpact_log.txt", text replace
set more off
clear all

capture erase 7_CustChgQ_tex.txt
capture erase 7_CustChgQ_tex.tex

capture erase 7_CustChg812Q_tex.txt
capture erase 7_CustChg812Q_tex.tex

	program outreg_CustChgQ_tex
        local keep_var1 = "L4sum_total L8sum_total L12sum_total"
        outreg2 using 7_CustChgQ_tex, tex(fragment) alpha(0.01, 0.05, 0.1) symbol(***, **, *) bracket dec(4) label ctitle(`1') keep(`keep_var1') sortvar(`keep_var1') fmt(f)
    end

	program outreg_CustChg812Q_tex
        local keep_var1 = "L4sum_total L8sum_total L12sum_total"
        outreg2 using 7_CustChg812Q_tex, tex(fragment) alpha(0.01, 0.05, 0.1) symbol(***, **, *) bracket dec(4) label ctitle(`1') keep(`keep_var1') sortvar(`keep_var1') fmt(f)
    end

**** Create 4-qtr DV measure 
    use "1_Variable_Prep.dta", clear
    
    sort gvkey_code year_qtr
    xtset gvkey_code year_qtr
    
    keep gvkey gvkey_code cusip datacqtr year_qtr naics sic cogsq invtq 
    order gvkey gvkey_code cusip datacqtr year_qtr naics sic cogsq invtq 
    
    replace datacqtr = subinstr(datacqtr, "Q", "",.) 
    destring datacqtr, replace
    gen year = floor(datacqtr/10)
    gen naics1 = substr(naics,1,1)
    gen naics2 = substr(naics,1,2)
    gen naics3 = substr(naics,1,3)
    gen naics4 = substr(naics,1,4)
    gen naics5 = substr(naics,1,5)
    merge m:1 datacqtr naics5 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics4 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics3 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics2 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics1 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    replace naics1="GDP" if mi(pricedef)
    merge m:1 datacqtr naics1 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    
    * merging shortest path distance from revere
    merge m:1 cusip datacqtr using "revere_measures.dta", keep(master match) nogen
    
    drop if missing(invtq) | invtq==0
    sort gvkey_code year_qtr
    xtset gvkey_code year_qtr
    
    gen cogsqd = cogsq*100/pricedef
    by gvkey_code: gen numobs = _N
    keep if numobs>=16
    
    gen biyear = floor(year/2)
    
    local vars "cogsqd"
    foreach var of local vars {	
        gen ld_`var' = ln(`var')-ln(l.`var')
        egen sd_`var' = sd(ld_`var'), by(gvkey_code biyear)
    }
    
    by gvkey_code:gen index=year_qtr-year_qtr[1]+1
    
    * rolling window
    rangestat (sd)ld_cogsqd,interval(index 0 3) by(gvkey)
    replace sd_cogsqd = ld_cogsqd_sd
    
    save "Working Files\cogs4_deflated.dta", replace

**** Create 8-qtr DV measure 
    use "1_Variable_Prep.dta", clear
    
    sort gvkey_code year_qtr
    xtset gvkey_code year_qtr
    
    keep gvkey gvkey_code cusip datacqtr year_qtr naics sic cogsq invtq 
    order gvkey gvkey_code cusip datacqtr year_qtr naics sic cogsq invtq 
    
    replace datacqtr = subinstr(datacqtr, "Q", "",.) 
    destring datacqtr, replace
    gen year = floor(datacqtr/10)
    gen naics1 = substr(naics,1,1)
    gen naics2 = substr(naics,1,2)
    gen naics3 = substr(naics,1,3)
    gen naics4 = substr(naics,1,4)
    gen naics5 = substr(naics,1,5)
    merge m:1 datacqtr naics5 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics4 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics3 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics2 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics1 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    replace naics1="GDP" if mi(pricedef)
    merge m:1 datacqtr naics1 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    
    * merging shortest path distance from revere
    merge m:1 cusip datacqtr using "revere_measures.dta", keep(master match) nogen
    
    drop if missing(invtq) | invtq==0
    sort gvkey_code year_qtr
    xtset gvkey_code year_qtr
    
    gen cogsqd = cogsq*100/pricedef
    by gvkey_code: gen numobs = _N
    keep if numobs>=16
    
    gen biyear = floor(year/2)
    
    local vars "cogsqd"
    foreach var of local vars {	
        gen ld_`var' = ln(`var')-ln(l.`var')
        egen sd_`var' = sd(ld_`var'), by(gvkey_code biyear)
    }
    
    by gvkey_code:gen index=year_qtr-year_qtr[1]+1
    
    * rolling window
    rangestat (sd)ld_cogsqd,interval(index 0 7) by(gvkey)
    replace sd_cogsqd = ld_cogsqd_sd
    
    save "Working Files\cogs8_deflated.dta", replace

**** Create 12-qtr DV measure 
    use "1_Variable_Prep.dta", clear
    
    sort gvkey_code year_qtr
    xtset gvkey_code year_qtr
    
    keep gvkey gvkey_code cusip datacqtr year_qtr naics sic cogsq invtq 
    order gvkey gvkey_code cusip datacqtr year_qtr naics sic cogsq invtq 
    
    replace datacqtr = subinstr(datacqtr, "Q", "",.) 
    destring datacqtr, replace
    gen year = floor(datacqtr/10)
    gen naics1 = substr(naics,1,1)
    gen naics2 = substr(naics,1,2)
    gen naics3 = substr(naics,1,3)
    gen naics4 = substr(naics,1,4)
    gen naics5 = substr(naics,1,5)
    merge m:1 datacqtr naics5 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics4 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics3 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics2 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    merge m:1 datacqtr naics1 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    replace naics1="GDP" if mi(pricedef)
    merge m:1 datacqtr naics1 using "price_deflators.dta", keepusing(pricedef) update
    drop if _merge==2
    drop _merge
    
    * merging shortest path distance from revere
    merge m:1 cusip datacqtr using "revere_measures.dta", keep(master match) nogen
    
    drop if missing(invtq) | invtq==0
    sort gvkey_code year_qtr
    xtset gvkey_code year_qtr
    
    gen cogsqd = cogsq*100/pricedef
    by gvkey_code: gen numobs = _N
    keep if numobs>=16
    
    gen biyear = floor(year/2)
    
    local vars "cogsqd"
    foreach var of local vars {	
        gen ld_`var' = ln(`var')-ln(l.`var')
        egen sd_`var' = sd(ld_`var'), by(gvkey_code biyear)
    }
    
    by gvkey_code:gen index=year_qtr-year_qtr[1]+1
    
    * rolling window
    rangestat (sd)ld_cogsqd,interval(index 0 11) by(gvkey)
    replace sd_cogsqd = ld_cogsqd_sd
    
    save "Working Files\cogs12_deflated.dta", replace

**** Generate annual and quarterly counts of customer change events
use "Revere_quarterly.dta", clear
    * Make data unique by s_cusip (using cusip6) c_cusip datacqtr
        keep s_cusip c_cusip datacqtr
        replace s_cusip = substr(s_cusip, 1, 6)
        compress
        duplicates drop
        unique s_cusip c_cusip datacqtr
        assert `r(N)' == `r(unique)'

    gen year = floor(datacqtr/10)
    gen qtr = datacqtr-year*10
    replace datacqtr = yq(year, qtr)
    format datacqtr %tq
    drop qtr
    tab year

    bysort c_cusip s_cusip (datacqtr): gen count = _n
    bysort c_cusip s_cusip: egen min = min(count)
    bysort c_cusip s_cusip: egen max = max(count)
    gen firstdate = cond(count == min, 1, 0)
    gen lastdate = cond(count == max, 1, 0)
    bysort s_cusip datacqtr: egen Added = sum(firstdate)
    bysort s_cusip datacqtr: egen Lost = sum(lastdate)

    drop count c_cusip firstdate lastdate min max
    duplicates drop

    unique s_cusip datacqtr
    rename s_cusip cusip
    rename datacqtr year_qtr

    preserve
        collapse (sum) sum_Added = Added sum_Lost = Lost , by(cusip year_qtr)
    * Save data that is unique by cusip year
        compress
        count
        save "Working Files\ChangeEventsQuarterly.dta", replace
    restore

    collapse (sum) sum_Added = Added sum_Lost = Lost , by(cusip year)

* Save data that is unique by cusip year
    compress
    count
    save "Working Files\ChangeEventsAnnual.dta", replace

***** Quarterly Analysis: 4 quarter DV
* Open file to get layers and DV
    use "Working Files\cogs4_deflated.dta", clear
    unique year_qtr cusip

* dist1 is missing for some quarters. Instead of N's annual code which arbitrarily drops obs with missing values, we must fill in the gaps for dist1
    bysort cusip year: egen modeDist1 = mode(dist1), minmode
    bysort cusip: egen modeDist2 = mode(dist1), minmode
    nmissing modeDist1 modeDist2 dist1
    replace dist1 = modeDist1 if (dist1==. | dist1 == 99) & modeDist1 != .
    replace dist1 = modeDist2 if (dist1==. | dist1 == 99) & modeDist2 != .
    nmissing dist1
    drop modeDist1 modeDist2
    
    keep cusip year_qtr ld_cogsqd_sd dist1

* Merge saved file from above to bring in count of customer change events
    merge m:1 cusip year_qtr using "Working Files\ChangeEventsQuarterly.dta"
    gen sum_total = (sum_Added + sum_Lost)/1000
    collapse (mean) mean_DV = ld_cogsqd_sd (median) med_DV = ld_cogsqd_sd (sum)  sum_Added sum_Lost sum_total, by(dist1 year_qtr)

* Generate statistics for paper on proportion of cust change events in layers 0-3. 
    tabstat sum_total, by(dist1)  statistics(n median p25 p75 mean sd min max)
    sum sum_total if dist1<=3, det
    display  `r(N)' * `r(mean)'
    sum sum_total if dist1>3  & dist1 <=9 & dist1 != ., det
    display  `r(N)' * `r(mean)'

* Regressions
    tsset dist1 year_qtr
    gen L4sum_total = L4.sum_total
    label variable L4sum_total "Total Change Events, Lag 4"
    gen L8sum_total = L8.sum_total
    label variable L8sum_total "Total Change Events, Lag 8"
    gen L12sum_total = L12.sum_total
    label variable L12sum_total "Total Change Events, Lag 12"

    reg mean_DV sum_total if dist1<=3, vce(cluster dist1)
    reg mean_DV sum_total i.dist1 if dist1<=3, vce(cluster dist1)

    reg mean_DV L4sum_total i.dist1 if dist1<=3, vce(cluster dist1)
        outreg_CustChgQ_tex
    tabstat L4sum_total if e(sample), by(dist1)  statistics(n median p25 p75 mean sd min max)

    reg mean_DV L8sum_total i.dist1 if dist1<=3, vce(cluster dist1)
        outreg_CustChgQ_tex

    reg mean_DV L12sum_total i.dist1 if dist1<=3, vce(cluster dist1)
        outreg_CustChgQ_tex

    tabstat L4sum_total  L8sum_total L12sum_total if e(sample), by(dist1)  statistics(n median p25 p75 mean sd min max)
    
***** Quarterly Analysis: 8 quarter DV
* Open file to get layers and DV
    use "Working Files\cogs8_deflated.dta", clear
    unique year_qtr cusip

* dist1 is missing for some quarters. Instead of N's annual code which arbitrarily drops obs with missing values, we must fill in the gaps for dist1
    bysort cusip year: egen modeDist1 = mode(dist1), minmode
    bysort cusip: egen modeDist2 = mode(dist1), minmode
    nmissing modeDist1 modeDist2 dist1
    replace dist1 = modeDist1 if (dist1==. | dist1 == 99) & modeDist1 != .
    replace dist1 = modeDist2 if (dist1==. | dist1 == 99) & modeDist2 != .
    nmissing dist1
    drop modeDist1 modeDist2
    
    keep cusip year_qtr sd_cogsqd dist1

* Merge saved file from above to bring in count of customer change events
    merge m:1 cusip year_qtr using "Working Files\ChangeEventsQuarterly.dta"
    gen sum_total = (sum_Added + sum_Lost)/1000
    collapse (mean) mean_DV = sd_cogsqd (median) med_DV = sd_cogsqd (sum)  sum_Added sum_Lost sum_total, by(dist1 year_qtr)

* Regressions
    tsset dist1 year_qtr
    gen L4sum_total = L4.sum_total
    label variable L4sum_total "Total Change Events, Lag 4"
    gen L8sum_total = L8.sum_total
    label variable L8sum_total "Total Change Events, Lag 8"
    gen L12sum_total = L12.sum_total
    label variable L12sum_total "Total Change Events, Lag 12"
    tabstat sum_total, by(dist1)  statistics(n median p25 p75 mean sd min max)

    reg mean_DV sum_total if dist1<=3, vce(cluster dist1)
    reg mean_DV sum_total i.dist1 if dist1<=3, vce(cluster dist1)

    reg mean_DV L4sum_total i.dist1 if dist1<=3, vce(cluster dist1)
        outreg_CustChg812Q_tex
    tabstat L4sum_total if e(sample), by(dist1)  statistics(n median p25 p75 mean sd min max)
    
    reg mean_DV L8sum_total i.dist1 if dist1<=3, vce(cluster dist1)
        outreg_CustChg812Q_tex

    reg mean_DV L12sum_total i.dist1 if dist1<=3, vce(cluster dist1)
        outreg_CustChg812Q_tex

    tabstat L4sum_total L8sum_total L12sum_total if e(sample), by(dist1)  statistics(n median p25 p75 mean sd min max)

***** Quarterly Analysis: 12 quarter DV
* Open file to get layers and DV
    use "Working Files\cogs12_deflated.dta", clear
    unique year_qtr cusip

* dist1 is missing for some quarters. Instead of N's annual code which arbitrarily drops obs with missing values, we must fill in the gaps for dist1
    bysort cusip year: egen modeDist1 = mode(dist1), minmode
    bysort cusip: egen modeDist2 = mode(dist1), minmode
    nmissing modeDist1 modeDist2 dist1
    replace dist1 = modeDist1 if (dist1==. | dist1 == 99) & modeDist1 != .
    replace dist1 = modeDist2 if (dist1==. | dist1 == 99) & modeDist2 != .
    nmissing dist1
    drop modeDist1 modeDist2
    
    keep cusip year_qtr ld_cogsqd_sd dist1

* Merge saved file from above to bring in count of customer change events
    merge m:1 cusip year_qtr using "Working Files\ChangeEventsQuarterly.dta"
    gen sum_total = (sum_Added + sum_Lost)/1000
    collapse (mean) mean_DV = ld_cogsqd_sd (median) med_DV = ld_cogsqd_sd (sum)  sum_Added sum_Lost sum_total, by(dist1 year_qtr)

* Regressions
    tsset dist1 year_qtr
    gen L4sum_total = L4.sum_total
    label variable L4sum_total "Total Change Events, Lag 4"
    gen L8sum_total = L8.sum_total
    label variable L8sum_total "Total Change Events, Lag 8"
    gen L12sum_total = L12.sum_total
    label variable L12sum_total "Total Change Events, Lag 12"
    tabstat sum_total, by(dist1)  statistics(n median p25 p75 mean sd min max)

    reg mean_DV sum_total if dist1<=3, vce(cluster dist1)
    reg mean_DV sum_total i.dist1 if dist1<=3, vce(cluster dist1)

    reg mean_DV L4sum_total i.dist1 if dist1<=3, vce(cluster dist1)
        outreg_CustChg812Q_tex
    tabstat L4sum_total if e(sample), by(dist1)  statistics(n median p25 p75 mean sd min max)

    reg mean_DV L8sum_total i.dist1 if dist1<=3, vce(cluster dist1)
        outreg_CustChg812Q_tex

    reg mean_DV L12sum_total i.dist1 if dist1<=3, vce(cluster dist1)
        outreg_CustChg812Q_tex

    tabstat L4sum_total L8sum_total L12sum_total if e(sample), by(dist1)  statistics(n median p25 p75 mean sd min max)

capture log close
