--------------------------------------------------------------------------------------------------------------------------- name: log: C:\Users\320491\Desktop\Final NBER\create_bartik_datafile.log log type: text opened on: 9 Jun 2019, 15:16:51 . local path_folder /// > "C:\Users\320491\Desktop\Final NBER" . . cd "`path_folder'" C:\Users\320491\Desktop\Final NBER . . tempfile temp_county2msa . import excel /// > "`path_folder'\rawData\CPI MSA to Census counties.xlsx", /// > sheet("Codes") firstrow clear . reshape long County, i(MSA) j(number) (note: j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33) Data wide -> long ----------------------------------------------------------------------------- Number of obs. 31 -> 1023 Number of variables 34 -> 3 j variable (33 values) -> number xij variables: County1 County2 ... County33 -> County ----------------------------------------------------------------------------- . drop number . drop if County=="" (725 observations deleted) . duplicates tag County, g(dup) Duplicates in terms of County . bysort County (MSA): gen first_dup = sum(dup)*dup== 1 . replace first_dup=first_dup+1 (298 real changes made) . reshape wide MSA, i(County) j(first_dup) (note: j = 1 2) Data long -> wide ----------------------------------------------------------------------------- Number of obs. 298 -> 264 Number of variables 4 -> 4 j variable (2 values) first_dup -> (dropped) xij variables: MSA -> MSA1 MSA2 ----------------------------------------------------------------------------- . . split County, p("_") variables created as string: County1 County2 . drop dup . rename County2 str_cty . rename County1 str_state . *Set DC to statewide code . replace str_cty="999" if str_state=="11" & str_cty=="1" (1 real change made) . save `temp_county2msa', replace (note: file C:\Users\320491\AppData\Local\Temp\ST_0f000001.tmp not found) file C:\Users\320491\AppData\Local\Temp\ST_0f000001.tmp saved . . import delimited "`path_folder'\rawData\cbp87co.txt", delimiters(",") clear (23 vars, 925439 obs) . g str_cty=string(fipscty) . g str_state=string(fipstate) . merge m:1 str_state str_cty using `temp_county2msa', /// > assert(master match) keep(match) Result # of obs. ----------------------------------------- not matched 0 matched 136,595 (_merge==3) ----------------------------------------- . . g emp_mp=emp . replace emp_mp=9.5 if empflag=="A" (36512 real changes made) . replace emp_mp=59.5 if empflag=="B" (20484 real changes made) . replace emp_mp=174.5 if empflag=="C" (7523 real changes made) . replace emp_mp=374.5 if empflag=="E" (3410 real changes made) . replace emp_mp=749.5 if empflag=="F" (1782 real changes made) . replace emp_mp=1749.5 if empflag=="G" (1031 real changes made) . replace emp_mp=3749.5 if empflag=="H" (343 real changes made) . replace emp_mp=7499.5 if empflag=="I" (171 real changes made) . replace emp_mp=17499.5 if empflag=="J" (51 real changes made) . replace emp_mp=37499.5 if empflag=="K" (11 real changes made) . replace emp_mp=74999.5 if empflag=="L" (2 real changes made) . . reshape long MSA, i(County sic) j(duplicate_county) (note: j = 1 2) Data wide -> long ----------------------------------------------------------------------------- Number of obs. 136595 -> 273190 Number of variables 30 -> 30 j variable (2 values) -> duplicate_county xij variables: MSA1 MSA2 -> MSA ----------------------------------------------------------------------------- . drop if MSA=="" (120301 observations deleted) . collapse (sum) emp emp_mp, by(MSA sic) . . egen double tot_emp= total(emp*(sic=="----")), by(MSA) . g emp_sh=emp/tot_emp . g emp_mp_sh=emp_mp/tot_emp . rename sic str_sic4 . save `temp_county2msa', replace file C:\Users\320491\AppData\Local\Temp\ST_0f000001.tmp saved . . tempfile temp_msa2msa . import excel /// > "`path_folder'\rawData\CPI MSA to Census CBP MSA.xlsx", /// > sheet("Codes") firstrow clear . . reshape long cbpMSA, i(MSA) j(number) (note: j = 1 2 3 4 5 6 7 8 9) Data wide -> long ----------------------------------------------------------------------------- Number of obs. 31 -> 279 Number of variables 10 -> 3 j variable (9 values) -> number xij variables: cbpMSA1 cbpMSA2 ... cbpMSA9 -> cbpMSA ----------------------------------------------------------------------------- . drop number . drop if cbpMSA==. (220 observations deleted) . duplicates tag cbpMSA, g(dup) Duplicates in terms of cbpMSA . bysort cbpMSA (MSA): gen first_dup = sum(dup)*dup== 1 . replace first_dup=first_dup+1 (59 real changes made) . reshape wide MSA, i(cbpMSA) j(first_dup) (note: j = 1 2) Data long -> wide ----------------------------------------------------------------------------- Number of obs. 59 -> 54 Number of variables 4 -> 4 j variable (2 values) first_dup -> (dropped) xij variables: MSA -> MSA1 MSA2 ----------------------------------------------------------------------------- . . drop dup . rename cbpMSA msa . save `temp_msa2msa', replace (note: file C:\Users\320491\AppData\Local\Temp\ST_0f000002.tmp not found) file C:\Users\320491\AppData\Local\Temp\ST_0f000002.tmp saved . . import delimited "`path_folder'\rawData\cbp93msa.txt", delimiters(",") clear (16 vars, 27900 obs) . merge m:1 msa using `temp_msa2msa', /// > assert(master match) keep(match) Result # of obs. ----------------------------------------- not matched 0 matched 4,860 (_merge==3) ----------------------------------------- . . g emp_mp=emp . replace emp_mp=9.5 if empflag=="A" (192 real changes made) . replace emp_mp=59.5 if empflag=="B" (169 real changes made) . replace emp_mp=174.5 if empflag=="C" (80 real changes made) . replace emp_mp=374.5 if empflag=="E" (73 real changes made) . replace emp_mp=749.5 if empflag=="F" (74 real changes made) . replace emp_mp=1749.5 if empflag=="G" (95 real changes made) . replace emp_mp=3749.5 if empflag=="H" (106 real changes made) . replace emp_mp=7499.5 if empflag=="I" (103 real changes made) . replace emp_mp=17499.5 if empflag=="J" (96 real changes made) . replace emp_mp=37499.5 if empflag=="K" (32 real changes made) . replace emp_mp=74999.5 if empflag=="L" (22 real changes made) . rename emp emp (all newnames==oldnames) . . reshape long MSA, i(msa sic) j(duplicate_county) (note: j = 1 2) Data wide -> long ----------------------------------------------------------------------------- Number of obs. 4860 -> 9720 Number of variables 20 -> 20 j variable (2 values) -> duplicate_county xij variables: MSA1 MSA2 -> MSA ----------------------------------------------------------------------------- . drop if MSA=="" (4410 observations deleted) . collapse (sum) emp emp_mp, by(MSA sic) . . egen double tot_emp= total(emp*(sic=="----")), by(MSA) . g emp_sh=emp/tot_emp . g emp_mp_sh=emp_mp/tot_emp . drop if ~strpos(sic, "00") (620 observations deleted) . g sic2=substr(sic,1,2) . rename sic2 str_sic2 . save `temp_msa2msa', replace file C:\Users\320491\AppData\Local\Temp\ST_0f000002.tmp saved . . use "`path_folder'\nekarda_ramey\govindustry-v2.3.dta", clear (Nekarda and Ramey (2010): Industry Evidence on the Effects of Gov't Spending) . *drop if year!=1987 . drop if year!=1992 (12905 observations deleted) . g str_sic4=string(sic4) . g str_sic2=string(sic2) . *merge 1:m str_sic4 using `temp_county2msa', keep(match) . collapse (sum) gtotal vshipio, by(str_sic2) . merge 1:m str_sic2 using `temp_msa2msa', keep(match) Result # of obs. ----------------------------------------- not matched 0 matched 620 (_merge==3) ----------------------------------------- . . g gshare = gtotal/vshipio . g gempshare = gshare*emp_sh . g gemp_mpshare = gshare*emp_mp_sh . collapse (sum) gempshare gemp_mpshare, by(MSA) . save `temp_county2msa', replace file C:\Users\320491\AppData\Local\Temp\ST_0f000001.tmp saved . import excel /// > "`path_folder'\concordance\CPI MSA to Unemployment (OMB) MSA.xlsx", /// > sheet("CPI codes") firstrow clear . rename CPIArea area_name . rename Code MSA . merge 1:1 MSA using `temp_county2msa', assert(master match) keep(match) Result # of obs. ----------------------------------------- not matched 0 matched 31 (_merge==3) ----------------------------------------- . drop _merge . . save "`path_folder'\gov_msa.dta", replace file C:\Users\320491\Desktop\Final NBER\gov_msa.dta saved . . log close name: log: C:\Users\320491\Desktop\Final NBER\create_bartik_datafile.log log type: text closed on: 9 Jun 2019, 15:17:08 ---------------------------------------------------------------------------------------------------------------------------