clear all version 12.0 capture log close set mem 1000m set more off **** +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *** **** "The Identification of Up- and Downstream Industries **** using Input-Output Tables and a Firm-level Application to Minority Shareholdings" **** Journal of Economics and Statistics, forthcoming **** +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *** **** +++++++++++++++++++++++++++++++++++++++++++ **** **** PREPARATION OF VERTICAL INDUSTRY RELATIONS **** +++++++++++++++++++++++++++++++++++++++++++ **** cd "path..." **** +++++++++++++++++++++++++++++++++++++++++++ **** *** Import input-output data: **** +++++++++++++++++++++++++++++++++++++++++++ **** *** For a detailed description of the symmetric input-output-tables provided by Eurostat see *** Eurostat (2008): Eurostat Manual of Supply, Use and Input-Output Tables, omline available at: *** http://ec.europa.eu/eurostat/documents/3859598/5902113/KS-RA-07-013-EN.PDF/b0b3d71e-3930-4442-94be-70b36cea9b39?version=1.0 *** Data available at: http://appsso.eurostat.ec.europa.eu/nui/show.do?dataset=naio_17_agg_60_r2&lang=en import excel "17_agg_60_r2.xlsx", firstrow destring y2011-y2008, replace gen seq = _n reshape long y, i(seq) j(year) drop unit *** Rename Variables: rename t_cols input_industry rename t_rows output_industry rename y flow rename geotime countryisocode label var seq "sequence number" label var year "Year" label var input_industry "Downstream Industry" label var output_industry "Upstream Industry" label var flow "Supply Volume (in EUR)" label var countryisocode "EU Aggregate" **** Sort Variables: order seq year output_industry input_industry countryisocode flow **** +++++++++++++++++++++++++++++++++++++++++++ **** *** Adjust industry codes: **** +++++++++++++++++++++++++++++++++++++++++++ **** keep if strpos(output_industry, "CPA_") & strpos(input_industry, "CPA_") drop if output_industry=="CPA_L68A" | input_industry=="CPA_L68A" replace output_industry="CPA_68" if output_industry=="CPA_L68B" replace input_industry="CPA_68" if input_industry=="CPA_L68B" ******************************************** foreach X of varlist output_industry input_industry{ replace `X'="05-09" if `X'=="CPA_B" } foreach X of varlist output_industry input_industry{ replace `X'="41-43" if `X'=="CPA_F" } foreach X of varlist output_industry input_industry{ replace `X'="55-56" if `X'=="CPA_I" } foreach X of varlist output_industry input_industry{ replace `X'="97-98" if `X'=="CPA_T" } foreach X of varlist output_industry input_industry{ replace `X'="99" if `X'=="CPA_U" } foreach X of varlist output_industry input_industry{ replace `X'="10-12" if `X'=="CPA_C10-C12" } foreach X of varlist output_industry input_industry{ replace `X'="13-15" if `X'=="CPA_C13-C15" } foreach X of varlist output_industry input_industry{ replace `X'="37-39" if `X'=="CPA_E37-E39" } foreach X of varlist output_industry input_industry{ replace `X'="80-82" if `X'=="CPA_N80-N82" } foreach X of varlist output_industry input_industry{ replace `X'="90-92" if `X'=="CPA_R90-R92" } foreach X of varlist output_industry input_industry{ replace `X'="31-32" if `X'=="CPA_C31_C32" } foreach X of varlist output_industry input_industry{ replace `X'="59-60" if `X'=="CPA_J59_J60" } foreach X of varlist output_industry input_industry{ replace `X'="62-63" if `X'=="CPA_J62_J63" } foreach X of varlist output_industry input_industry{ replace `X'="69-70" if `X'=="CPA_M69_M70" } foreach X of varlist output_industry input_industry{ replace `X'="74-75" if `X'=="CPA_M74_M75" } foreach X of varlist output_industry input_industry{ replace `X'="87-88" if `X'=="CPA_Q87_Q88" } ******************************************** gen h=1 if strpos(output_industry, "-") replace output_industry = substr(output_industry,-2,.) if h !=1 drop h gen h=1 if strpos(input_industry, "-") replace input_industry = substr(input_industry,-2,.) if h !=1 drop h drop if output_industry=="AL" | input_industry=="AL" **** +++++++++++++++++++++++++++++++++++++++++++ **** *** Adjust industry codes: **** +++++++++++++++++++++++++++++++++++++++++++ **** *** Calculate total sales by indstry and year: Alternative 1: egen output_industry_total =sum(flow), by(output_industry year countryisocode) *** Calculate total sales by indstry and year: Alternative 2: egen input_industry_total=sum(flow), by(input_industry year countryisocode) label var output_industry_total "total output of upstream industry" label var input_industry_total "total input of downstream industry" **** +++++++++++++++++++++++++++++++++++++++++++ **** *** Calculate fractions for each industry: **** +++++++++++++++++++++++++++++++++++++++++++ **** gen output_industry_fraction=flow/output_industry_total*100 gen input_industry_fraction= flow/input_industry_total*100 replace output_industry_fraction=0 if output_industry_fraction==. replace input_industry_fraction=0 if input_industry_fraction==. label var output_industry_fraction "Share of downstream industry at overall output of upstream industry" label var input_industry_fraction "Share of upstream industry at overall output of downstream industry" **** +++++++++++++++++++++++++++++++++++++++++++ **** *** Adjusting for non-profit industries: **** +++++++++++++++++++++++++++++++++++++++++++ **** drop if output_industry=="84" | input_industry=="84" drop if output_industry=="85" |input_industry=="85" drop if output_industry=="90-92" | input_industry=="90-92" drop if output_industry=="93" |input_industry=="93" drop if output_industry=="97-98" | input_industry=="97-98" drop if output_industry=="99" |input_industry=="99" **** +++++++++++++++++++++++++++++++++++++++++++ **** *** Splitting up of aggregated industries: *** (Applying this procedure, the separated industries are assumed to have the same linkages, respectively.) *** Also see section 3.1 of the paper. *** We split the pooled industries beforehand in order to identify every possible vertical link in the industry dataset. To do so we assume the pooled industries to be equal linked to other industries. *** This assumption is due to the lack of appropriate information about the accurate flow size of every single pooled industry. *** The following industries are affected by this assumption: Mining of coal, lignite, natural gas, metal ores (05-09); *** Construction and civil engineering (41-43); Accommodation and food service activities (55-56); Security activities, ** service to buildings and office administration (80-82); Manufacturing of furniture and other manufacturing (31-32); Manufacturing of food products, beverages, tobacco (10-12); *** manufacturing of textiles, wearing apparel and leather products (13-15); Sewerage and waste collection (37-39); *** Programming, television programming (59-60); Computer programming and information service (62-63); ** Accounting service and management consultancy activities (69-70); scientific and veterinary activities (74-75); residential care activities and social work (87-88). **** +++++++++++++++++++++++++++++++++++++++++++ **** preserve keep if output_industry=="05-09" | input_industry=="05-09" replace output_industry="05" if output_industry=="05-09" replace input_industry="05" if input_industry=="05-09" tempfile 05 save `05', replace restore preserve keep if output_industry=="05-09" | input_industry=="05-09" replace output_industry="06" if output_industry=="05-09" replace input_industry="06" if input_industry=="05-09" tempfile 06 save `06', replace restore preserve keep if output_industry=="05-09" | input_industry=="05-09" replace output_industry="07" if output_industry=="05-09" replace input_industry="07" if input_industry=="05-09" tempfile 07 save `07', replace restore preserve keep if output_industry=="05-09" | input_industry=="05-09" replace output_industry="08" if output_industry=="05-09" replace input_industry="08" if input_industry=="05-09" tempfile 08 save `08', replace restore preserve keep if output_industry=="05-09" | input_industry=="05-09" replace output_industry="09" if output_industry=="05-09" replace input_industry="09" if input_industry=="05-09" tempfile 09 save `09', replace restore append using `05' append using `06' append using `07' append using `08' append using `09' preserve keep if output_industry=="10-12" | input_industry=="10-12" replace output_industry="10" if output_industry=="10-12" replace input_industry="10" if input_industry=="10-12" tempfile 10 save `10', replace restore preserve keep if output_industry=="10-12" | input_industry=="10-12" replace output_industry="11" if output_industry=="10-12" replace input_industry="11" if input_industry=="10-12" tempfile 11 save `11', replace restore preserve keep if output_industry=="10-12" | input_industry=="10-12" replace output_industry="12" if output_industry=="10-12" replace input_industry="12" if input_industry=="10-12" tempfile 12 save `12', replace restore append using `10' append using `11' append using `12' preserve keep if output_industry=="13-15" | input_industry=="13-15" replace output_industry="13" if output_industry=="13-15" replace input_industry="13" if input_industry=="13-15" tempfile 13 save `13', replace restore preserve keep if output_industry=="13-15" | input_industry=="13-15" replace output_industry="14" if output_industry=="13-15" replace input_industry="14" if input_industry=="13-15" tempfile 14 save `14', replace restore preserve keep if output_industry=="13-15" | input_industry=="13-15" replace output_industry="15" if output_industry=="13-15" replace input_industry="15" if input_industry=="13-15" tempfile 15 save `15', replace restore append using `13' append using `14' append using `15' preserve keep if output_industry=="31-32" | input_industry=="31-32" replace output_industry="31" if output_industry=="31-32" replace input_industry="31" if input_industry=="31-32" tempfile 31 save `31', replace restore preserve keep if output_industry=="31-32" | input_industry=="31-32" replace output_industry="32" if output_industry=="31-32" replace input_industry="32" if input_industry=="31-32" tempfile 32 save `32', replace restore append using `31' append using `32' preserve keep if output_industry=="37-39" | input_industry=="37-39" replace output_industry="37" if output_industry=="37-39" replace input_industry="37" if input_industry=="37-39" tempfile 37 save `37', replace restore preserve keep if output_industry=="37-39" | input_industry=="37-39" replace output_industry="38" if output_industry=="37-39" replace input_industry="38" if input_industry=="37-39" tempfile 38 save `38', replace restore preserve keep if output_industry=="37-39" | input_industry=="37-39" replace output_industry="39" if output_industry=="37-39" replace input_industry="39" if input_industry=="37-39" tempfile 39 save `39', replace restore append using `37' append using `38' append using `39' preserve keep if output_industry=="41-43" | input_industry=="41-43" replace output_industry="41" if output_industry=="41-43" replace input_industry="41" if input_industry=="41-43" tempfile 41 save `41', replace restore preserve keep if output_industry=="41-43" | input_industry=="41-43" replace output_industry="42" if output_industry=="41-43" replace input_industry="42" if input_industry=="41-43" tempfile 42 save `42', replace restore preserve keep if output_industry=="41-43" | input_industry=="41-43" replace output_industry="43" if output_industry=="41-43" replace input_industry="43" if input_industry=="41-43" tempfile 43 save `43', replace restore append using `41' append using `42' append using `43' preserve keep if output_industry=="55-56" | input_industry=="55-56" replace output_industry="55" if output_industry=="55-56" replace input_industry="55" if input_industry=="55-56" tempfile 55 save `55', replace restore preserve keep if output_industry=="55-56" | input_industry=="55-56" replace output_industry="56" if output_industry=="55-56" replace input_industry="56" if input_industry=="55-56" tempfile 56 save `56', replace restore append using `55' append using `56' preserve keep if output_industry=="59-60" | input_industry=="59-60" replace output_industry="59" if output_industry=="59-60" replace input_industry="59" if input_industry=="59-60" tempfile 59 save `59', replace restore preserve keep if output_industry=="59-60" | input_industry=="59-60" replace output_industry="60" if output_industry=="59-60" replace input_industry="60" if input_industry=="59-60" tempfile 60 save `60', replace restore append using `59' append using `60' preserve keep if output_industry=="62-63" | input_industry=="62-63" replace output_industry="62" if output_industry=="62-63" replace input_industry="62" if input_industry=="62-63" tempfile 62 save `62', replace restore preserve keep if output_industry=="62-63" | input_industry=="62-63" replace output_industry="63" if output_industry=="62-63" replace input_industry="63" if input_industry=="62-63" tempfile 63 save `63', replace restore append using `62' append using `63' preserve keep if output_industry=="69-70" | input_industry=="69-70" replace output_industry="69" if output_industry=="69-70" replace input_industry="69" if input_industry=="69-70" tempfile 69 save `69', replace restore preserve keep if output_industry=="69-70" | input_industry=="69-70" replace output_industry="70" if output_industry=="69-70" replace input_industry="70" if input_industry=="69-70" tempfile 70 save `70', replace restore append using `69' append using `70' preserve keep if output_industry=="74-75" | input_industry=="74-75" replace output_industry="74" if output_industry=="74-75" replace input_industry="74" if input_industry=="74-75" tempfile 74 save `74', replace restore preserve keep if output_industry=="74-75" | input_industry=="74-75" replace output_industry="75" if output_industry=="74-75" replace input_industry="75" if input_industry=="74-75" tempfile 75 save `75', replace restore append using `74' append using `75' preserve keep if output_industry=="80-82" | input_industry=="80-82" replace output_industry="80" if output_industry=="80-82" replace input_industry="80" if input_industry=="80-82" tempfile 80 save `80', replace restore preserve keep if output_industry=="80-82" | input_industry=="80-82" replace output_industry="81" if output_industry=="80-82" replace input_industry="81" if input_industry=="80-82" tempfile 81 save `81', replace restore preserve keep if output_industry=="80-82" | input_industry=="80-82" replace output_industry="82" if output_industry=="80-82" replace input_industry="82" if input_industry=="80-82" tempfile 82 save `82', replace restore append using `80' append using `81' append using `82' preserve keep if output_industry=="87-88" | input_industry=="87-88" replace output_industry="87" if output_industry=="87-88" replace input_industry="87" if input_industry=="87-88" tempfile 87 save `87', replace restore preserve keep if output_industry=="87-88" | input_industry=="87-88" replace output_industry="88" if output_industry=="87-88" replace input_industry="88" if input_industry=="87-88" tempfile 88 save `88', replace restore append using `87' append using `88' **** +++++++++++++++++++++++++++++++++++++++++++ **** *** Drop aggregated industries: **** +++++++++++++++++++++++++++++++++++++++++++ **** drop if output_industry=="05-09" | input_industry=="05-09" drop if output_industry=="10-12" | input_industry=="10-12" drop if output_industry=="13-15" | input_industry=="13-15" drop if output_industry=="31-32" | input_industry=="31-32" drop if output_industry=="37-39" | input_industry=="37-39" drop if output_industry=="41-43" | input_industry=="41-43" drop if output_industry=="55-56" | input_industry=="55-56" drop if output_industry=="59-60" | input_industry=="59-60" drop if output_industry=="62-63" | input_industry=="62-63" drop if output_industry=="69-70" | input_industry=="69-70" drop if output_industry=="74-75" | input_industry=="74-75" drop if output_industry=="80-82" | input_industry=="80-82" drop if output_industry=="87-88" | input_industry=="87-88" **** +++++++++++++++++++++++++++++++++++++++++++ **** *** Merge nace description: **** +++++++++++++++++++++++++++++++++++++++++++ **** merge m:1 output_industry using nace_rev2_description drop input output tab _merge drop if _merge==2 drop _merge rename nace_description output_description label var output_description "Output NACE Rev.2 Code" *smerge input_industry using nace_rev2_description, keep (nace_description) merge m:1 input_industry using nace_rev2_description drop input output tab _merge drop if _merge==2 drop _merge rename nace_description input_description label var output_description "Input NACE Rev.2 Code" ******************************************** distinct output_industry input_industry keep year output_industry input_industry countryisocode flow output_industry_total input_industry_total output_industry_fraction input_industry_fraction output_description input_description save IO_final.dta, replace export delimited using IO_final.txt, replace exit