**** 1_Variable_Prep.do: merges all data and generates measures using that data. Prepares data for matching and regrassion analysis.

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

**** Start with COMPUSTAT data
	use "Data\COMPUSTAT 20180608.dta", clear
    merge 1:1 gvkey datadat datafqtr using "Data\COMPUSTATMarketValue.dta", nogen keep(master match)
    drop indfmt consol popsrc datafmt

*** Generate 2 digit SIC codes and the aggregate into SIC groups based on those codes
    gen sic2 = substr(sic,1,2)
	destring sic2, replace
	label variable sic2 "2 digit SIC code"

	* Create categorical variable capturing all 15 SIC2 categories
	* Agriculture
	gen byte sic2_c = 1 if sic2>=1 & sic2<=9
	* Mining 
	replace sic2_c = 2 if sic2>=10 & sic2<=14
	* Construction 
	replace sic2_c = 3 if sic2>=15 & sic2<=17	
	* Food and Tobacco
	replace sic2_c = 4 if sic2>=20 & sic2<=21
	* Textiles, Lumber and Furniture, Paper and Printing, Stone and Leather, Misc. Manuf. Industries 
	replace sic2_c = 5 if (sic2>=22 & sic2<=27) | (sic2>=31 & sic2<=32) | sic2==39
	* Chemicals and Petroleum
	replace sic2_c = 6 if sic2>=28 & sic2<=30
	* Primary Metals & Fabricated Metal Products
	replace sic2_c = 7 if sic2>=33 & sic2<=34
	* Industrial Machinery & Equipment
	replace sic2_c = 8 if sic2==35
	* Electronic & Other Electric Equipment
	replace sic2_c = 9 if sic2==36
	* Transportation Equipment
	replace sic2_c = 10 if sic2==37
	* Instruments & Related Products
	replace sic2_c = 11 if sic2==38
	* Transportation and utilities
	replace sic2_c = 12 if sic2>=40 & sic2<=49
	* Wholesale 
	replace sic2_c = 13 if sic2>=50 & sic2<=51
	* Retail 
	replace sic2_c = 14 if sic2>=52 & sic2<=59
	* Finance, Insurance, & Real Estate, Services, Public Administration, Non-Classifiables
	replace sic2_c = 15 if sic2>=60 & sic2<=99

	label variable sic2_c "Industry"
	label define sic2_lab 1 "Agriculture" 2 "Mining" 3 "Construction" 4 "Food and Tobacco" 5 "Textile, Lumber, Paper" 6 "Chemicals and Petroleum" 7 "Primary and Fab. Metals" 8 "Industrial Machinery" 9 "Electronics" 10 "Transportation Equipment" 11 "Instruments" 12 "Transport and Utilities" 13 "Wholesale" 14 "Retail" 15 "Services"
	label values sic2_c sic2_lab

	* Create categorical variable capturing  7 further coarsened SIC2  categories
	* Agriculture
	gen byte sic2_c2 = 1 if sic2>=1 & sic2<=9
	* Mining 
	replace sic2_c2 = 2 if sic2>=10 & sic2<=14
	* Construction 
	replace sic2_c2 = 3 if sic2>=15 & sic2<=17	
	* Food and Tobacco
	replace sic2_c2 = 4 if sic2>=20 & sic2<=21
	* Textiles, Lumber and Furniture, Paper and Printing, Stone and Leather, Misc. Manuf. Industries 
	replace sic2_c2 = 4 if (sic2>=22 & sic2<=27) | (sic2>=31 & sic2<=32) | sic2==39
	* Chemicals and Petroleum
	replace sic2_c2 = 4 if sic2>=28 & sic2<=30
	* Primary Metals & Fabricated Metal Products
	replace sic2_c2 = 7 if sic2>=33 & sic2<=34
	* Industrial Machinery & Equipment
	replace sic2_c2 = 7 if sic2==35
	* Electronic & Other Electric Equipment
	replace sic2_c2 = 7 if sic2==36
	* Transportation Equipment
	replace sic2_c2 = 7 if sic2==37
	* Instruments & Related Products
	replace sic2_c2 = 7 if sic2==38
	* Transportation and utilities
	replace sic2_c2 = 12 if sic2>=40 & sic2<=49
	* Wholesale 
	replace sic2_c2 = 13 if sic2>=50 & sic2<=51
	* Retail 
	replace sic2_c2 = 14 if sic2>=52 & sic2<=59
	* Finance, Insurance, & Real Estate, Services, Public Administration, Non-Classifiables
	replace sic2_c2 = 15 if sic2>=60 & sic2<=99
	
	label define sic2_lab2 1 "Agriculture" 2 "Mining" 3 "Construction" 4 "Non-Durable Mnf" 7 "Durable Mnf" 12 "Transport and Utilities" 13 "Wholesale" 14 "Retail" 15 "Services"
	label values sic2_c2 sic2_lab2

    tab sic2 if cusip == ""
    * Drop if cusip == "". These all occur in sic2=67. These are investment vehicles (REITS, ETF, royalty traders, etc). Since such investment vehicles will neither be a supplier or buyer, I drop them.
        drop if cusip == ""

*** Ensure that data is unique by firm-quarter
    * Use calendar reporting (datacqtr) rather than fiscal reporting (datafqtr) as the fromer matches up to actual calendar dates (fiscal reporting dates vary by company)
	* I adopt the calendar quarter convention in Compustat, namely Q1 Feb-Apr, Q2 May-Jul, Q3 Aug-Oct, Q4 Nov-Jan
    	* QQQ: datacqtr is missing for ~0.1% of the observations. Typically due to company changing its reporting dates, but care should be taken as this
    	*      sometimes results in multiple reports in a single quarter (move the report date from Nov to Dec for instance, but reports are 
    	*      released on both months in the year of the changeover). For now, drop the missing.
            count
            count if datacqtr == ""
        	drop if datacqtr == ""
    	gen year_qtr = quarterly(datacqtr, "YQ")
    	format year_qtr %tq	
        	label variable year_qtr "Data Yr & Qtr for Panel Analysis, Q1 Feb-Apr, Q2 May-Jul, Q3 Aug-Oct, Q4 Nov-Jan"
        order gvkey cusip year_qtr 
        
    * Get rid of any observations that prevent the COMPUSTAT data from being unique at gvkey year_qtr, provided there are not a lot of these. When I last checked (Oct 2017), there was 1 such observation.
        unique gvkey year_qtr
        duplicates tag gvkey year_qtr, gen(dup)
        count if dup > 0
        assert `r(N)' < 5
        duplicates drop gvkey year_qtr, force
        drop dup

*** Set Up Panel Data Structure
    * Set panel by gvkey and year_qtr
    	encode gvkey, gen(gvkey_code)
    	tsset gvkey_code year_qtr
    	decode(gvkey_code), generate(temp)
    	replace gvkey = temp if gvkey == ""
    	drop temp
    	label variable gvkey_code "gvkey code is used to time series set the data for taking lags"
    
    * Fill in cusip codes based on gvkey. This is used primarily to facilitate the merge with any CUSIP tagged data. 
        count if cusip == ""
        gsort gvkey -year_qtr
        by gvkey: replace cusip = cusip[_n-1] if gvkey == gvkey[_n-1] &  cusip == ""
        bysort gvkey ( year_qtr ): replace cusip = cusip[_n-1] if gvkey == gvkey[_n-1] & cusip == ""
        count if cusip == ""
        assert `r(N)' ==  0

**** Clean up Cusip
    	gen cusip9 = cusip
    	gen cusip8 = substr(cusip9, 1, 8)
    	replace cusip = substr(cusip9, 1, 6)

**** Create financial measures from Compustat data
	* Create lags for use in financial ratios
        sort gvkey_code year_qtr
    	local varlist = "oiadpq saleq cogsq invtq ppentq atq cshoq prccq ceqq ltq dlcq dlttq"
    	foreach var in `varlist' {
    		display "Working on `var'..."
    		gen `var'L1 = L1.`var'
    		gen `var'L2 = L2.`var'
    		gen `var'L3 = L3.`var'
    		gen `var'L4 = L4.`var'
    		gen `var'L5 = L5.`var'
        	egen `var'_avg14L = rowmean(`var'L1 `var'L2 `var'L3 `var'L4)
    		egen `var'_tot14L = rowtotal(`var'L1 `var'L2 `var'L3 `var'L4)
    		gen `var'_qpc = 2*(`var'L1 - `var'L2)/(`var'L1 + `var'L2)
    		gen `var'_ypc = 2*(`var'L1 - `var'L5)/(`var'L1 + `var'L5)
            label variable `var'L1 "1 quarter lag of `var'"
            label variable `var'L2 "2 quarter lag of `var'"
            label variable `var'L3 "3 quarter lag of `var'"
            label variable `var'L4 "4 quarter lag of `var'"
            label variable `var'L5 "5 quarter lag of `var'"
            label variable `var'_avg14L "4 quarter average of `var'"
            label variable `var'_tot14L "4 quarter total of `var'"
    		label variable `var'_qpc "Qtr over qtr pct chg in `var'"
    		label variable `var'_ypc "Year over year pct chg in qtrly `var'"
    	}

    	local varlist = "invtq ppentq atq"
    	foreach var in `varlist' {
    		gen `var'L6 = L6.`var'
            label variable `var'L6 "6 quarter lag of `var'"
        }

    * Measures of operational slack    		
    	gen DOI = 365 * (invtq_avg14L)/(cogsq_tot14L)
        replace DOI = invtq / cogsq if DOI == .
        replace DOI = 0 if invtq_avg14L == 0
    	label variable DOI "Days of Inventory = Ratio of (average of beginning and end of quarter inventory) to COGS"
    	
    * Capital Intensity, Gaur et al. 2005
    	gen CIq = (ppentq_tot14L)/(atq_tot14L)
    	label variable CIq "Sum of Net Fixed Assets over past year over Sum of Total Assets over past year"
        gen CIqL1 = (ppentqL1)/(atqL1)
        mvencode CIqL1, mv(0) o
    	label variable CIqL1 "Capital Intensity"
    	
    * Fixed Asset turnover (Sales divided by Plant Property and Equipment)
		gen FAT_1L = 2 * saleqL1 /  (ppentqL1 + ppentqL2) 
		gen FAT_2L = 2 * saleqL2 /  (ppentqL2 + ppentqL3)
		gen FAT_5L = 2 * saleqL5 /  (ppentqL5 + ppentqL6) 
		gen FAT_T4L = 4 * saleq_tot14L / (ppentq_tot14L) 
		gen FAT_12p = (FAT_1L - FAT_2L) / (abs(FAT_1L) + abs(FAT_2L))
		gen FAT_12pmod = (FAT_1L - FAT_2L) / abs(FAT_2L) if abs(FAT_2L) != 0
		gen FAT_15p = (FAT_1L - FAT_5L) / (abs(FAT_1L) + abs(FAT_5L))
        gen FAtoSales_T4L = (ppentq_tot14L) / (4 * saleq_tot14L)
        mvencode FAtoSales_T4L, mv(0) o
		label variable FAT_1L "Sales to PPE using 1 quarter of lagged data"
		label variable FAT_2L "Sales to PPE using 2 quarter lagged data"
		label variable FAT_5L "Sales to PPE using 5 quarter lagged data"
		label variable FAT_T4L "Sales to PPE using mean of 4 quarters of lagged data"
		label variable FAT_12p "Sales to PPE normalized rate of change from lag 2 to lag 1"
		label variable FAT_15p "Sales to PPE normalized rate of change from lag 5 to lag 1"
		label variable FAtoSales_T4L "Fixed Assets over Sales, avg 4Q"

    * Operating Margin (Operating Income divided by Sales)
		gen OM_1L = oiadpqL1 / saleqL1
		gen OM_2L = oiadpqL2 / saleqL2
		gen OM_5L = oiadpqL5 / saleqL5
		gen OM_T4L = oiadpq_tot14L / saleq_tot14L
		gen OM_12p = (OM_1L - OM_2L) / (abs(OM_1L) + abs(OM_2L))
		gen OM_12pmod = (OM_1L - OM_2L) / abs(OM_2L) if abs(OM_2L) != 0
		gen OM_15p = (OM_1L - OM_5L) / (abs(OM_1L) + abs(OM_5L))
		label variable OM_1L "Operating margin using 1 quarter lagged data"
		label variable OM_2L "Operating margin using 2 quarter lagged data"
		label variable OM_5L "Operating margin using 5 quarter lagged data"
		label variable OM_T4L "Operating margin using mean of 4 quarters of lagged data"
		label variable OM_12p "Operating margin normalized rate of change from lag 2 to lag 1"
		label variable OM_15p "Operating margin normalized rate of change from lag 5 to lag 1"

        * Market-to-Book Ratio (Market Value of Equity divided by Book Value of Equity)
        * Use lagged quarterly average values. For companies without historical data, use current period.
    		gen MBR = cshoq_avg14L * prccq_avg14L / ceqq_avg14L
            replace MBR = cshoq * prccq / ceqq if MBR == .
    		label variable MBR "Market-to-Book Ratio"
    
        * Log of lagged sales
            gen LogSaleqL1 = log(saleqL1+1)
        	gen LogSaleqL1_mz = LogSaleqL1
        	mvencode LogSaleqL1_mz, mv(0) override
        	gen byte LogSaleqL1_ms = LogSaleqL1_mz == 0 & LogSaleqL1 == .
    		label variable LogSaleqL1_mz "LogSaleqL1 winsorized to .01 with missing values coded to 0"
    		label variable LogSaleqL1_ms "Missing values coded to 0 in LogSaleqL1_mz"
    
    * Create variables with missing values set to zero.
    	local varlist1 = "OM_1L MBR" 
    	foreach var in `varlist1' {
    		display "Working on `var'..."
    		gen `var'_Scal = `var'
            winsor `var'_Scal, gen(`var'_Scalw) p(.01)
    		gen `var'_Scalw_mz = `var'_Scalw
    		mvencode `var'_Scalw_mz, mv(0) override
    		gen byte `var'_Scalw_ms = `var'_Scalw_mz == 0 & `var'_Scal == . 
    		label variable `var'_Scalw "`var'_Scal winsorized at .01"
    		label variable `var'_Scalw_mz "`var' winsorized to .01 with missing values coded to 0"
    		label variable `var'_Scalw_ms "Missing values coded to 0 in `var'_Scalw_mz"
    
    		* Drop vars that we won't need
    		 drop `var'_Scal `var'_Scalw
    	}
    
    * Size categorical variable (used to generate summary statistics table for writeup)
        gen int sales_c = 1 if saleq_tot14L < 150
        replace sales_c = 2 if saleq_tot14L >= 150 & saleq_tot14L < 1000
        replace sales_c = 3 if saleq_tot14L >= 1000 & saleq_tot14L < 5000
        replace sales_c = 4 if saleq_tot14L >= 5000 & saleq_tot14L != .
        replace sales_c = 5 if saleq_tot14L == . | saleq_tot14L  == 0
        label define sales_lab 1 "Current Year Sales < 150M" 2 "Sales >= $150M and < $1B" 3 "Sales >= $1B and < $5B" 4 "Sales >= $5B" 5 "Sales unknown"
    	label values sales_c sales_lab

**** Merge network based measures - this only needs to be run once, so I comment it out for future runs
    merge m:1 cusip year_qtr using "revere_measures.dta", keep(master match) keepusing(gics outdeg indeg deg dist1 dist2 ln_outdeg )gen(_revere)
	
**** Merge dependent variable using Jing's code - this only needs to be run once, so I comment it out for future runs
    preserve
        use "mf_add4.dta", clear
        gen treated4 = 1
        label variable treated4 "Supplier had valid customer addition in this quarter"
        gen sd_cogsDiff4 = sd2_cogs - sd1_cogs
        label variable sd_cogsDiff4 "Difference in 4 qtr sd_cogs between T+4 and T-1"
        rename s_cusip cusip

        gen year_qtr = jumpqtr
        format year_qtr %tq
        
        rename sd1_cogs sd1_cogs4
        rename sd2_cogs sd2_cogs4
        order gvkey cusip year_qtr treated4 sd_cogsDiff4 sd1_cogs4 sd2_cogs4
        keep gvkey cusip year_qtr treated4 sd_cogsDiff4 sd1_cogs4 sd2_cogs4
        rename gvkey gvkey_num
            
        unique gvkey year_qtr
        assert `r(N)' == `r(unique)'

        compress
        save "SD_Cogs4.dta", replace

        use "mf_add8.dta", clear
        gen treated8 = 1
        label variable treated8 "Supplier had valid customer addition in this quarter"
        gen sd_cogsDiff8 = sd2_cogs - sd1_cogs
        label variable sd_cogsDiff8 "Difference in 8 qtr sd_cogs between T+8 and T-1"
        rename s_cusip cusip

        gen year_qtr = jumpqtr
        format year_qtr %tq
        
        rename sd1_cogs sd1_cogs8
        rename sd2_cogs sd2_cogs8
        order gvkey cusip year_qtr treated8 sd_cogsDiff8 sd1_cogs8 sd2_cogs8
        keep gvkey cusip year_qtr treated8 sd_cogsDiff8 sd1_cogs8 sd2_cogs8
        rename gvkey gvkey_num
            
        unique gvkey year_qtr
        assert `r(N)' == `r(unique)'

        compress
        save "SD_Cogs8.dta", replace
        export excel using "Working Files\Model Free Graph.xlsx", firstrow(variables) replace


        use "mf_add12.dta", clear
        gen treated12 = 1
        label variable treated12 "Supplier had valid customer addition in this quarter"
        gen sd_cogsDiff12 = sd2_cogs - sd1_cogs
        label variable sd_cogsDiff12 "Difference in 12 qtr sd_cogs between T+12 and T-1"
        rename s_cusip cusip

        gen year_qtr = jumpqtr
        format year_qtr %tq
        
        rename sd1_cogs sd1_cogs12
        rename sd2_cogs sd2_cogs12
        order gvkey cusip year_qtr treated12 sd_cogsDiff12 sd1_cogs12 sd2_cogs12
        keep gvkey cusip year_qtr treated12 sd_cogsDiff12 sd1_cogs12 sd2_cogs12
        rename gvkey gvkey_num
            
        unique gvkey year_qtr
        assert `r(N)' == `r(unique)'

        compress
        save "SD_Cogs12.dta", replace

    * Create values for add-swap
            use "mf_add4swap4_4.dta", clear
            gen treated4as = 1
            label variable treated4as "Supplier had valid customer addition in this quarter with a swap in -4,4"
            gen sd_cogsDiff4as = sd2_cogs - sd1_cogs
            label variable sd_cogsDiff4as "Difference in 4 qtr sd_cogs between T+4 and T-1"
            rename s_cusip cusip
    
            gen year_qtr = jumpqtr
            format year_qtr %tq
            
            rename sd1_cogs sd1_cogs4as
            rename sd2_cogs sd2_cogs4as
            order gvkey cusip year_qtr treated4as sd_cogsDiff4as sd1_cogs4 sd2_cogs4
            keep gvkey cusip year_qtr treated4as sd_cogsDiff4as sd1_cogs4 sd2_cogs4
            rename gvkey gvkey_num
                
            unique gvkey year_qtr
            assert `r(N)' == `r(unique)'
    
            compress
            save "SD_Cogs4addswap.dta", replace


            use "mf_add8swap4_4.dta", clear
            gen treated8as = 1
            label variable treated8as "Supplier had valid customer addition in this quarter with a swap in -4,4"
            gen sd_cogsDiff8as = sd2_cogs - sd1_cogs
            label variable sd_cogsDiff8as "Difference in 8 qtr sd_cogs between T+8 and T-1"
            rename s_cusip cusip
    
            gen year_qtr = jumpqtr
            format year_qtr %tq
            
            rename sd1_cogs sd1_cogs8as
            rename sd2_cogs sd2_cogs8as
            order gvkey cusip year_qtr treated8as sd_cogsDiff8as sd1_cogs8 sd2_cogs8
            keep gvkey cusip year_qtr treated8as sd_cogsDiff8as sd1_cogs8 sd2_cogs8
            rename gvkey gvkey_num
                
            unique gvkey year_qtr
            assert `r(N)' == `r(unique)'
    
            compress
            save "SD_Cogs8addswap.dta", replace


            use "mf_add12swap4_4.dta", clear
            gen treated12as = 1
            label variable treated12as "Supplier had valid customer addition in this quarter with a swap in -4,4"
            gen sd_cogsDiff12as = sd2_cogs - sd1_cogs
            label variable sd_cogsDiff12as "Difference in 12 qtr sd_cogs between T+12 and T-1"
            rename s_cusip cusip
    
            gen year_qtr = jumpqtr
            format year_qtr %tq
            
            rename sd1_cogs sd1_cogs12as
            rename sd2_cogs sd2_cogs12as
            order gvkey cusip year_qtr treated12as sd_cogsDiff12as sd1_cogs12 sd2_cogs12
            keep gvkey cusip year_qtr treated12as sd_cogsDiff12as sd1_cogs12 sd2_cogs12
            rename gvkey gvkey_num
                
            unique gvkey year_qtr
            assert `r(N)' == `r(unique)'
    
            compress
            save "SD_Cogs12addswap.dta", replace


    * Create values for drop
            use "mf_drop4.dta", clear
            gen treated4drop = 1
            label variable treated4drop "Supplier had valid customer drop in this quarter"
            gen sd_cogsDiff4drop = sd2_cogs - sd1_cogs
            label variable sd_cogsDiff4drop "Difference in 4 qtr sd_cogs between T+4 and T-1"
            rename s_cusip cusip
    
            gen year_qtr = jumpqtr
            format year_qtr %tq
            
            rename sd1_cogs sd1_cogs4drop
            rename sd2_cogs sd2_cogs4drop
            order gvkey cusip year_qtr treated4drop sd_cogsDiff4drop sd1_cogs4 sd2_cogs4
            keep gvkey cusip year_qtr treated4drop sd_cogsDiff4drop sd1_cogs4 sd2_cogs4
            rename gvkey gvkey_num
                
            unique gvkey year_qtr
            assert `r(N)' == `r(unique)'
    
            compress
            save "SD_Cogs4drop.dta", replace


            use "mf_drop8.dta", clear
            gen treated8drop = 1
            label variable treated8drop "Supplier had valid customer drop in this quarter"
            gen sd_cogsDiff8drop = sd2_cogs - sd1_cogs
            label variable sd_cogsDiff8drop "Difference in 8 qtr sd_cogs between T+8 and T-1"
            rename s_cusip cusip
    
            gen year_qtr = jumpqtr
            format year_qtr %tq
            
            rename sd1_cogs sd1_cogs8drop
            rename sd2_cogs sd2_cogs8drop
            order gvkey cusip year_qtr treated8drop sd_cogsDiff8drop sd1_cogs8 sd2_cogs8
            keep gvkey cusip year_qtr treated8drop sd_cogsDiff8drop sd1_cogs8 sd2_cogs8
            rename gvkey gvkey_num
                
            unique gvkey year_qtr
            assert `r(N)' == `r(unique)'
    
            compress
            save "SD_Cogs8drop.dta", replace


            use "mf_drop12.dta", clear
            gen treated12drop = 1
            label variable treated12drop "Supplier had valid customer drop in this quarter"
            gen sd_cogsDiff12drop = sd2_cogs - sd1_cogs
            label variable sd_cogsDiff12drop "Difference in 12 qtr sd_cogs between T+12 and T-1"
            rename s_cusip cusip
    
            gen year_qtr = jumpqtr
            format year_qtr %tq
            
            rename sd1_cogs sd1_cogs12drop
            rename sd2_cogs sd2_cogs12drop
            order gvkey cusip year_qtr treated12drop sd_cogsDiff12drop sd1_cogs12 sd2_cogs12
            keep gvkey cusip year_qtr treated12drop sd_cogsDiff12drop sd1_cogs12 sd2_cogs12
            rename gvkey gvkey_num
                
            unique gvkey year_qtr
            assert `r(N)' == `r(unique)'
    
            compress
            save "SD_Cogs12drop.dta", replace
        restore
*
    
    * The data from Jing currently uses a numeric gvkey. unless that is changed, make the following update before and after the merge
        gen gvkey_num = gvkey
        destring gvkey_num, replace
        
    merge 1:1 gvkey_num year_qtr using "SD_Cogs4.dta", keep(master match) gen(_SDCogs4)
    merge 1:1 gvkey_num year_qtr using "SD_Cogs8.dta", keep(master match) gen(_SDCogs8)
    merge 1:1 gvkey_num year_qtr using "SD_Cogs12.dta", keep(master match) gen(_SDCogs12)

    merge 1:1 gvkey_num year_qtr using "SD_Cogs4addswap.dta", keep(master match) gen(_SDCogs4as)
    merge 1:1 gvkey_num year_qtr using "SD_Cogs8addswap.dta", keep(master match) gen(_SDCogs8as)
    merge 1:1 gvkey_num year_qtr using "SD_Cogs12addswap.dta", keep(master match) gen(_SDCogs12as)

    merge 1:1 gvkey_num year_qtr using "SD_Cogs4drop.dta", keep(master match) gen(_SDCogs4d)
    merge 1:1 gvkey_num year_qtr using "SD_Cogs8drop.dta", keep(master match) gen(_SDCogs8d)
    merge 1:1 gvkey_num year_qtr using "SD_Cogs12drop.dta", keep(master match) gen(_SDCogs12d)

        drop gvkey_num
    
    * Check model free
        ttest sd1_cogs4 == sd2_cogs4
        ttest sd1_cogs8 == sd2_cogs8
        ttest sd1_cogs12 == sd2_cogs12

        ttest sd1_cogs4as == sd2_cogs4as
        ttest sd1_cogs8as == sd2_cogs8as
        ttest sd1_cogs12as == sd2_cogs12as

        ttest sd1_cogs4drop == sd2_cogs4drop
        ttest sd1_cogs8drop == sd2_cogs8drop
        ttest sd1_cogs12drop == sd2_cogs12drop

**** Since we are using matching, we need the dependent variable for all possible match firms. The following code creates COGS variability measure for all firms
        unique cusip datacqtr
        rename datacqtr datacqtr_str
        gen datacqtr = quarterly(datacqtr_str, "YQ")
    	format datacqtr %tq        
        rename cusip s_cusip
        
        * The data from Jing currently uses a numeric gvkey. until that is fixed, make the following changes before and after the merge
            gen gvkey_str = gvkey
            destring gvkey, replace
            merge m:1 s_cusip datacqtr using "compustat_s_naics3-4.dta", keep(master using match) gen(_scogs)
            drop gvkey
            rename gvkey_str gvkey
            label variable gvkey "Global Company Key"
            order gvkey
        
            * Confirm that merge is not missing observations in the study period (approx 2003 - 2014) for naics 3 and 4
                gen naics1 = substr(naics,1,1)
                tab datacqtr if _scogs==1 & (naics1=="3" | naics1=="4")
                count if (year > 2002 & year < 2015) & _scogs==1 & (naics1=="3" | naics1=="4")
                assert `r(N)' ==  0
                drop datacqtr
        rename datacqtr_str datacqtr 
        rename s_cusip cusip

        preserve
            keep if naics1=="3" | naics1=="4"
            tsset gvkey_code year_qtr
            tsfill, full
            sort gvkey_code year_qtr
            gen lndiffcogsq = ln(sup_cogsq) - ln(L1.sup_cogsq)
            keep gvkey_code year_qtr lndiffcogsq
            tsegen sdcogs4All = rowsd(L(0/3).lndiffcogsq)
            drop lndiffcogsq
            * rolling sdcogs4All = r(sd), window(4) clear: summarize lndiffcogsq
            * drop start
            * rename end year_qtr
            sum sdcogs4All
            save "1_sdcogs4All.dta", replace
        restore

        preserve
            keep if naics1=="3" | naics1=="4"
            tsset gvkey_code year_qtr
            tsfill, full
            sort gvkey_code year_qtr
            gen lndiffcogsq = ln(sup_cogsq) - ln(L1.sup_cogsq)
            keep gvkey_code year_qtr lndiffcogsq
            tsegen sdcogs8All = rowsd(L(0/7).lndiffcogsq)
            drop lndiffcogsq
            * rolling sdcogs8All = r(sd), window(8) clear: summarize lndiffcogsq
            * drop start
            * rename end year_qtr
            sum sdcogs8All
            save "1_sdcogs8All.dta", replace
        restore

         preserve
            keep if naics1=="3" | naics1=="4"
            tsset gvkey_code year_qtr
            tsfill, full
            sort gvkey_code year_qtr
            gen lndiffcogsq = ln(sup_cogsq) - ln(L1.sup_cogsq)
            keep gvkey_code year_qtr lndiffcogsq
            tsegen sdcogs12All = rowsd(L(0/11).lndiffcogsq)
            drop lndiffcogsq
            * rolling sdcogs12All = r(sd), window(12) clear: summarize lndiffcogsq
            * drop start
            * rename end year_qtr
            sum sdcogs12All
            save "1_sdcogs12All.dta", replace
        restore

    merge 1:1 gvkey_code year_qtr using 1_sdcogs4All.dta, keep(master match) gen(_sdcogs4)
        sort gvkey_code year_qtr
        gen sdcogs4T_1 = L1.sdcogs4All
        gen sdcogs4T4 = F4.sdcogs4All
    
    merge 1:1 gvkey_code year_qtr using 1_sdcogs8All.dta, keep(master match) gen(_sdcogs8)
        sort gvkey_code year_qtr
        gen sdcogs8T_1 = L1.sdcogs8All
        gen sdcogs8T8 = F8.sdcogs8All

    merge 1:1 gvkey_code year_qtr using 1_sdcogs12All.dta, keep(master match) gen(_sdcogs12)
        sort gvkey_code year_qtr
        gen sdcogs12T_1 = L1.sdcogs12All
        gen sdcogs12T12 = F12.sdcogs12All

    * Combine data for control observations with data for treated observations
        replace sd_cogsDiff4 = sdcogs4T4 - sdcogs4T_1 if sd_cogsDiff4 == . & treated4 == .
        replace sd_cogsDiff8 = sdcogs8T8 - sdcogs8T_1 if sd_cogsDiff8 == . & treated8 == .
        replace sd_cogsDiff12 = sdcogs12T12 - sdcogs12T_1 if sd_cogsDiff12 == . & treated12 == .

        replace sd_cogsDiff4drop = sdcogs4T4 - sdcogs4T_1 if sd_cogsDiff4drop == . & treated4drop == .
        replace sd_cogsDiff8drop = sdcogs8T8 - sdcogs8T_1 if sd_cogsDiff8drop == . & treated8drop == .
        replace sd_cogsDiff12drop = sdcogs12T12 - sdcogs12T_1 if sd_cogsDiff12drop == . & treated12drop == .

        replace sd_cogsDiff4as = sdcogs4T4 - sdcogs4T_1 if sd_cogsDiff4as == . & treated4as == .
        replace sd_cogsDiff8as = sdcogs8T8 - sdcogs8T_1 if sd_cogsDiff8as == . & treated8as == .
        replace sd_cogsDiff12as = sdcogs12T12 - sdcogs12T_1 if sd_cogsDiff12as == . & treated12as == .

**** Update labels for clean output
        label variable OM_1L_Scalw_mz "Operating Margin (OM) Level"
        label variable MBR_Scalw_mz "Market-to-Book Ratio"
        label variable LogSaleqL1_mz "Log of lagged sales"
        label variable sd_cogsDiff4 "Change in COGS Std Dev, 4 qtrs"
        label variable sd_cogsDiff8 "Change in COGS Std Dev, 8 qtrs"
        label variable sd_cogsDiff12 "Change in COGS Std Dev, 12 qtrs"
        label variable DOI "Days of Inventory"
        label variable MBR "Market-to-Book Ratio"
        label variable ppentq_qpc "Pct Change in Fixed Assets"
        label variable ppentq_avg14L "Avg Fixed Assets 4Q Lag"
        
**** Generate a dummy variable to be used in the match process that identifies whether or not all of the variables in the regression are present.
**** We will only match to control observations that have all of the variables present.
        gen byte vars_present = (sd_cogsDiff4 !=. & DOI !=. & MBR !=. & ppentq_qpc !=. & LogSaleqL1_mz !=. & FAtoSales_T4L !=. & CIqL1 !=.)
        gen byte vars_present8 = (sd_cogsDiff8 !=. & DOI !=. & MBR !=. & ppentq_qpc !=. & LogSaleqL1_mz !=. & FAtoSales_T4L !=. & CIqL1 !=.)
        gen byte vars_present12 = (sd_cogsDiff12 !=. & DOI !=. & MBR !=. & ppentq_qpc !=. & LogSaleqL1_mz !=. & FAtoSales_T4L !=. & CIqL1 !=.)
        label variable vars_present "Variables used in the 4 qtr regression are all present"
        label variable vars_present8 "Variables used in the 8 qtr regression are all present"
        label variable vars_present12 "Variables used in the 12 qtr regression are all present"

        gen byte vars_present_drop = (sd_cogsDiff4drop !=. & DOI !=. & MBR !=. & ppentq_qpc !=. & LogSaleqL1_mz !=. & FAtoSales_T4L !=. & CIqL1 !=.)
        gen byte vars_present8_drop = (sd_cogsDiff8drop !=. & DOI !=. & MBR !=. & ppentq_qpc !=. & LogSaleqL1_mz !=. & FAtoSales_T4L !=. & CIqL1 !=.)
        gen byte vars_present12_drop = (sd_cogsDiff12drop !=. & DOI !=. & MBR !=. & ppentq_qpc !=. & LogSaleqL1_mz !=. & FAtoSales_T4L !=. & CIqL1 !=.)
        label variable vars_present_drop "Variables used in the 4 qtr regression are all present"
        label variable vars_present8_drop "Variables used in the 8 qtr regression are all present"
        label variable vars_present12_drop "Variables used in the 12 qtr regression are all present"

        gen byte vars_present_as = (sd_cogsDiff4as !=. & DOI !=. & MBR !=. & ppentq_qpc !=. & LogSaleqL1_mz !=. & FAtoSales_T4L !=. & CIqL1 !=.)
        gen byte vars_present8_as = (sd_cogsDiff8as !=. & DOI !=. & MBR !=. & ppentq_qpc !=. & LogSaleqL1_mz !=. & FAtoSales_T4L !=. & CIqL1 !=.)
        gen byte vars_present12_as = (sd_cogsDiff12as !=. & DOI !=. & MBR !=. & ppentq_qpc !=. & LogSaleqL1_mz !=. & FAtoSales_T4L !=. & CIqL1 !=.)
        label variable vars_present_as "Variables used in the 4 qtr regression are all present"
        label variable vars_present8_as "Variables used in the 8 qtr regression are all present"
        label variable vars_present12_as "Variables used in the 12 qtr regression are all present"

**** Save the results
	compress
	save "1_Variable_Prep.dta", replace

log close



