**** s_cogs_.do: Creates deflated COGS and production data from COMPUSTAT for use in model free evidence by supplier and matched regression analysis by supplier.

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


* Merging with price deflators
use "Data/COMPUSTAT 20180608.dta", clear

	replace datacqtr = subinstr(datacqtr, "Q", "",.) 
	destring datacqtr, replace

	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

* If naics1==4 and procedef is missing, we use deflator of naics1=="3&4"
	gen NAICS="3&4" if naics1=="4" & mi(pricedef)
	merge m:1 datacqtr NAICS using "price_deflators.dta", keepusing(pricedef) update
	drop if _merge==2
	drop _merge

* If ind_price_deflator is missing, we use GDP deflators
	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)

* Keep duration to correspond to the supply chain dataset (including pre- and post-customer time periods)
	drop if datacqtr<20012 |datacqtr>20174

	destring sic, replace
    keep if (sic>=5200 & sic<=5999) | (sic>=5000 & sic<=5199) | (sic>=2000 & sic<=3999) | (sic>=1000 & sic<=1499)


	replace cogsq = cogsq*100/pricedef
	replace invtq = invtq*100/pricedef
	replace cusip=substr(cusip,1,8)

* Generate production series
	gen dataabsqtr = floor(datacqtr/10)*4+mod(datacqtr,10)
	sort gvkey dataabsqtr
	destring gvkey, replace
	xtset gvkey dataabsqtr
	gen prodq = cogsq+invtq-l.invtq

* Create 6-digit CUSIP
    gen cusip8 = cusip
    replace cusip = substr(cusip8, 1, 6)

* Prepare for saving
  preserve
	keep cusip datacqtr cogsq prodq gvkey cusip8

    * Remove duplicates, The forced drop affects a small number of observations - dropping 24 companies (out of 12,144, or 0.2%) totaling of 638 quarters (out of 403,734, or 0.16%). 
        duplicates drop
        unique cusip8
        unique cusip datacqtr
        duplicates drop cusip datacqtr, force
        unique cusip8
        unique cusip datacqtr
        count

	ren cusip c_cusip
	ren cogsq cus_cogsq
	ren prodq cus_prodq

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

* Save results
    compress
    save "compustat_c", replace
    
    ren c_cusip s_cusip
    ren cus_cogsq sup_cogsq
    ren cus_prodq sup_prodq
    save "compustat_s", replace
    
  restore
    compress
    count
    save "compustat_0608", replace


log close
