clear all
import excel "Margins_After_Redefinitions_2007_2012_DET.xlsx", sheet("2007") cellrange(A5:E61094) firstrow
rename E ProVal
rename IndustryCode Industry
rename CommodityCode Commodity
save "IOUseDetail2007.dta"

clear all
import excel "Margins_After_Redefinitions_2007_2012_DET.xlsx", sheet("2012") cellrange(A5:E61135) firstrow
rename E ProVal
rename IndustryCode Industry
rename CommodityCode Commodity
save "IOUseDetail2012.dta"

do "Do-file to compute Upstreamness2007.do"
do "Do-file to compute Upstreamness2012.do"

* run do-file to compute upstreamness prior to this block of code
clear all
import excel "Mapping_NAICS_to_IO_Accounts.xlsx", sheet("NAICS Mapping") cellrange(A1:K410) firstrow
destring NAICS1, replace force
reshape long NAICS, i(IODetaillevel) j(NAICScounter)
drop if mi(NAICS)
rename IODetaillevel io_industry
rename B name
merge m:1 io_industry using upstreamness_by_industry2007.dta
keep if _merge==3
drop _merge
rename io_industry_name io_name_2007
rename upstreamness upstreamness2007
merge m:1 io_industry using upstreamness_by_industry2012.dta
keep if _merge==3
drop _merge
rename io_industry_name io_name_2012
rename upstreamness upstreamness2012
rename NAICS naics

bys naics: egen up2007=mean(upstreamness2007)
bys naics: egen up2012=mean(upstreamness2012)

duplicates drop naics up2007 up2012, force

drop upstreamness* 

gen naics5=naics
gen naics4=naics
gen naics3=naics
gen naics2=naics
gen naics1=naics

save "naics_io_upstreamness.dta", replace


