
clear all
set more off
cap log close
log using prep_cogs_deflated_deseason.txt, text replace 

use "..\1_Variable_Prep.dta" 

sort gvkey_code year_qtr
xtset gvkey_code year_qtr

keep  gvkey gvkey_code cusip datacqtr year_qtr  naics sic sic2_c* gics cogsq invtq saleq outdeg indeg deg dist1 dist2
order gvkey gvkey_code cusip datacqtr year_qtr  naics sic sic2_c* gics cogsq invtq saleq outdeg indeg deg dist1 dist2

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
replace naics1 = substr(naics,1,1)

* merge the c2c data
merge m:1 datacqtr gvkey using "c2c\compustat_c2c_computed.dta", keepusing(dpo)
drop if _merge == 2
drop _merge

*merging upstreamness measure (Antras et al., AER)
destring naics*, force replace
merge m:1 naics using  "..\Data\BEA_IO_Benchmark\naics_io_upstreamness.dta", keepusing(up*) 
drop if _merge==2
drop _merge

merge m:1 naics5 using "..\Data\BEA_IO_Benchmark\naics_io_upstreamness.dta", keepusing(up*) update
drop if _merge==2
drop _merge
merge m:1 naics4 using "..\Data\BEA_IO_Benchmark\naics_io_upstreamness.dta", keepusing(up*) update
drop if _merge==2
drop _merge
merge m:1 naics3 using "..\Data\BEA_IO_Benchmark\naics_io_upstreamness.dta", keepusing(up*) update
drop if _merge==2
drop _merge
merge m:1 naics2 using "..\Data\BEA_IO_Benchmark\naics_io_upstreamness.dta", keepusing(up*) update
drop if _merge==2
drop _merge

*make upstreamness zero based
replace up2007=up2007-1
replace up2012=up2012-1

drop if missing(invtq) | invtq==0
sort gvkey_code year_qtr
xtset gvkey_code year_qtr

gen prodq = cogsq+invtq-l.invtq

gen cogsqd = cogsq*100/pricedef
gen invtqd = invtq*100/pricedef
gen prodqd = cogsqd+invtqd-l.invtqd

label var cogsqd "Deflated COGSQ"
label var prodqd "Deflated PRODQ"


by gvkey_code: gen numobs = _N
keep if numobs>=16

local vars "cogsqd prodqd"
foreach var of local vars {	
    gen ld_`var' = ln(`var')-ln(l.`var')
}

//deseasonalization
gen qtr=datacqtr-floor(datacqtr/10)*10
tab qtr,gen(qdv)
*gen Regression coefficient of quarterly dummy variables for DV
preserve
statsby _b, by(gvkey_code) clear:reg ld_cogsqd qdv1 qdv2 qdv3
save cogsqr_all.dta,replace
restore
*gen Regression coefficient of quarterly dummy variables for Prod
preserve
statsby _b, by(gvkey_code) clear:reg ld_prodqd qdv1 qdv2 qdv3
save prodqr_all.dta,replace
restore

*generate residuals (deseasonolized values) for COGS
merge m:1 gvkey_code  using  cogsqr_all.dta, keep(master match) nogen
gen ld_cogsqdr=ld_cogsqd-qdv1*_b_qdv1-qdv2*_b_qdv2-qdv3*_b_qdv3-qdv4*_b_cons
drop _b_qdv1 _b_qdv2 _b_qdv3 _b_cons ld_cogsqd
rename ld_cogsqdr ld_cogsqd

*generate residuals (deseasonolized values) for Prod
merge m:1 gvkey_code  using  prodqr_all.dta, keep(master match) nogen
gen ld_prodqdr=ld_prodqd-qdv1*_b_qdv1-qdv2*_b_qdv2-qdv3*_b_qdv3-qdv4*_b_cons
drop _b_qdv1 _b_qdv2 _b_qdv3 _b_cons ld_prodqd
rename ld_prodqdr ld_prodqd
	

sort gvkey_code year_qtr	
by gvkey_code:gen index=year_qtr-year_qtr[1]+1

//rolling window estimation of DV and BWE
rangestat (sd)ld_prodqd,interval(index 0 7) by(gvkey)
rangestat (sd)ld_cogsqd,interval(index 0 7) by(gvkey)

rename ld_prodqd_sd sd_prodqd
rename ld_cogsqd_sd sd_cogsqd

//rolling window!!
rangestat (sd)ld_prodqd,interval(index 0 3) by(gvkey)
rangestat (sd)ld_cogsqd,interval(index 0 3) by(gvkey)

rename ld_prodqd_sd sd_prodqd4
rename ld_cogsqd_sd sd_cogsqd4

//rolling window!!
rangestat (sd)ld_prodqd,interval(index 0 11) by(gvkey)
rangestat (sd)ld_cogsqd,interval(index 0 11) by(gvkey)

rename ld_prodqd_sd sd_prodqd12
rename ld_cogsqd_sd sd_cogsqd12

//BWE/DV based on all data
local vars "cogsqd prodqd"
foreach var of local vars {	
    egen sd_`var'all = sd(ld_`var'), by(gvkey_code)
}
gen bweall = sd_prodqdall-sd_cogsqdall


	
* intrafirm bullwhip
gen bwed = sd_prodqd-sd_cogsqd
gen bwed4 = sd_prodqd4-sd_cogsqd4
gen bwed12 = sd_prodqd12-sd_cogsqd12

gen sic1=substr(sic,1,1)
gen sic2=substr(sic,1,2)
destring sic1 sic2, replace
gen lsaleq=ln(saleq)

sort gvkey_code datacqtr

label var year "Year"
label var dist1 "Layer"
label var dist2 "LayerAll"
gen dist2d = round(dist2)
label var dist2d "LayerAllD"
label var up2007 "Upstreamness"
label var lsaleq "Size"
label var bwed "BWE"
label var sd_cogsqd "DV"



//Keep subset corresponding to the supply network (Revere)
keep if datacqtr>=20032 & datacqtr<=20154
drop if missing(dist1)




//Extrapolate the Revere data for quarters when path to L0 does not exist in certain quarters, but exists in others.
replace dist1=. if dist1==99
replace dist2=. if dist2==99
egen dist11 = mode(dist1), minmode by(gvkey_code )
replace dist1=dist11 if mi(dist1)
egen dist21 = median(dist2),  by(gvkey_code )
replace dist2=dist21 if mi(dist2)
drop dist11 dist21
replace dist1=99 if missing(dist1)
replace dist2=99 if missing(dist2)

//Collapse quarterly data into annual
gsort gvkey_code -datacqtr
duplicates drop gvkey_code year, force 


//Tabulate composition of industries by Layer (for Table 2)
destring naics2, replace
destring naics3, replace
destring naics4, replace
gen dist4=.
replace dist4 = 0 if sic2>=52 & sic2<=59 //retail
replace dist4 = 1 if sic2>=50 & sic2<=51 //wholesale
replace dist4 = 2 if sic2>=20 & sic2<=39 //manuf
replace dist4 = 3 if sic2>=60 & sic2<=89 //services
replace dist4 = 4 if mi(dist4) //all others

label define dist4_lab 0 "Retail" 1 "Wholesale" 2 "Manufacturing" 3 "Services" 4 "Other"
label values dist4 dist4_lab
label var dist4 "LM"


//Keep only retail, whs, mnf
keep if naics2>=31 & naics2<=49


save "cogs_deflated_deseason.dta", replace

log close


