rural urban employment

county2010_cbsa2010_geocorr14.csv

master.do

clear all
set more off

global qcewdata /data/qcew/stata/

* require existing github gist for publishing
global gistid 7d7aa4cf659419e63a022d751cec24d5

* state geocodes
import delimited using state_geocodes.csv, clear varnames(1)
rename state_name statename
rename state_abb stateabb
rename fips statefips
keep statename stateabb statefips
tempfile state_geocodes
save `state_geocodes'

* county 2010 to cbsa 2010 from Geocorr14
* source: http://mcdc.missouri.edu/websas/geocorr14.html
import delimited using county2010_cbsa2010_geocorr14.csv, clear varnames(1)
drop in 1
tempfile export
export delimited using `export'
import delimited using `export', clear varnames(1)
assert afact == 1
rename county countyfips
gen byte metrotype = .
replace metrotype = 1 if cbsatype10 == "Metro"
replace metrotype = 2 if cbsatype10 == "Micro"
replace metrotype = 0 if cbsatype10 == ""
assert metrotype ~= .
tempfile metrotype
save `metrotype'

* clean county QCEW data
local yearlist 1990(1)2016
local qcewvars area_fips year qtr agglvl_code month*_emplvl industry_code own_code disclosure_code total_qtrly_wages
* pull each year of county data
foreach year of numlist `yearlist' {
  unzipfile ${qcewdata}qcew_county_`year'.dta.zip, replace
  use `qcewvars' if agglvl_code == 70 | (agglvl_code == 71 & own_code == 5) using qcew_county_`year'.dta, clear
  tempfile qcew`year'
  save `qcew`year''
  erase qcew_county_`year'.dta
}
* append years
local counter = 0
foreach year of numlist `yearlist' {
	local counter = `counter' + 1
	if `counter' == 1 use `qcew`year'', clear
	else append using `qcew`year''
}

* time codes
rename qtr quarter
gen quarterdate = yq(year,quarter)
format %tq quarterdate

* drop data if not disclosed
drop if disclosure_code == "-" | disclosure_code == "N"
assert disclosure_code == ""

* create and clean geography codes
gen statefips = substr(area_fips,1,2)
destring statefips, replace
* drop PR, VI counties
drop if statefips == 72 | statefips == 78
gen countyfips = substr(area_fips,1,5)
* drop state-level data
drop if substr(countyfips,3,3) == "000"
* drop unassigned geography data
drop if substr(countyfips,3,.) == "999"
destring countyfips, replace

* calculate quarterly employment as mean of monthly employment
egen emp = rowmean(month*_emplvl)

* total wage bill
rename total_qtrly_wages wage

* industry codes
gen ind = ""
replace ind = "all" if agglvl_code == 70
replace ind = "pri" if agglvl_code == 71 & own_code == 5
assert ind ~= ""

* deal with some geography
* Shannon County became Oglala Lakota County in 2015; map back
* see https://www.cdc.gov/nchs/nvss/bridged_race/county_geography-_changes2015.pdf
assert emp == 0 if countyfips == 46102 & quarterdate < tq(2015q3)
assert emp == 0 if countyfips == 46113 & quarterdate > tq(2015q2)
drop if countyfips == 46102 & quarterdate < tq(2015q3)
drop if countyfips == 46113 & quarterdate > tq(2015q2)
replace countyfips = 46113 if countyfips == 46102
* https://www.census.gov/geo/reference/county-changes.html
* Wade Hampton Census Area became Kusilvak Census Area; map back
assert emp == 0 if countyfips == 2158 & quarterdate < tq(2015q3)
assert emp == 0 if countyfips == 2270 & quarterdate > tq(2015q2)
drop if countyfips == 2158 & quarterdate < tq(2015q3)
drop if countyfips == 2270 & quarterdate > tq(2015q2)
replace countyfips = 2270 if countyfips == 2158
* clifton forge added to alleghany county in mid-2001, go ahead and add previous data
* https://www.census.gov/geo/reference/county-changes.html
replace countyfips = 51005 if countyfips == 51560
collapse (sum) emp wage (first) year quarter statefips, by(countyfips quarterdate ind)

* merge metro identifiers
merge m:1 countyfips using `metrotype', keep(1 3)

* a few counties in Alaska are split into other counties
* all of these are for nonmetro counties
* some splits mix nonmetro and micro counties; so check this if you're using micro; we don't in what follows
* https://www.census.gov/geo/reference/county-changes.html
assert _merge == 3 if countyfips ~= 2201 & countyfips ~= 2232 & countyfips ~= 2280
replace metrotype = 0 if countyfips == 2201 | countyfips == 2232 | countyfips == 2280
replace _merge = 3 if countyfips == 2201 | countyfips == 2232 | countyfips == 2280
keep if _merge == 3
drop _merge
assert metrotype == 1 | metrotype == 2 | metrotype == 0

* reshape wide in industry
keep year quarter quarterdate countyfips emp wage statefips ind metrotype
reshape wide emp wage, i(countyfips year quarterdate) j(ind) string

* identify metro and nonmetro
gen byte metro = metrotype == 1
gen byte nonmetro = metrotype == 0 | metrotype == 2

* collapse to state-metro-year
foreach var of varlist metro nonmetro {
  foreach stat in emp wage {
    gen `stat'all`var' = `stat'all * `var'
    gen `stat'pri`var' = `stat'pri * `var'
  }
}
drop empall emppri wageall wagepri
collapse (sum) emp* wage* (first) year, by(statefips quarterdate)
collapse (mean) emp* wage*, by(statefips year)
merge m:1 statefips using `state_geocodes', assert(3) nogenerate keepusing(stateabb)

* convert wages to annual
foreach var of varlist wage* {
  replace `var' = `var' * 4
}
* calculate average wages per worker
foreach x in allmetro allnonmetro primetro prinonmetro {
  rename wage`x' totwage`x'
  gen avgwage`x' = totwage`x' / emp`x'
}

* export data
lab var empallmetro "Overall employment, metro areas"
lab var empprimetro "Private-sector employment, metro areas"
lab var empallnonmetro "Overall employment, non-metro areas"
lab var empprinonmetro "Private-sector employment, non-metro areas"
lab var avgwageallmetro "Wages per worker, overall, metro areas"
lab var avgwageprimetro "Wages per worker, private sector, metro areas"
lab var avgwageallnonmetro "Wages per worker, overall, non-metro areas"
lab var avgwageprinonmetro "Wages per worker, private sector, non-metro areas"
lab var totwageallmetro "Total wages, overall, metro areas"
lab var totwageprimetro "Total wages, private sector, metro areas"
lab var totwageallnonmetro "Total wages, overall, non-metro areas"
lab var totwageprinonmetro "Total wages, private sector, non-metro areas"
lab var statefips "State FIPS"
lab var stateabb "State abbreviation"
lab var year "Year"
sort stateabb year
order stateabb statefips year empallmetro empprimetro empallnonmetro empprinonmetro avgwageallmetro avgwageprimetro avgwageallnonmetro avgwageprinonmetro totwageallmetro totwageprimetro totwageallnonmetro totwageprinonmetro
export delimited using qcew_metro_state.csv, replace

!gist -u $gistid master.do qcew_metro_state.csv state_geocodes.csv county2010_cbsa2010_geocorr14.csv

qcew_metro_state.csv

state_geocodes.csv