change in monthly CES emp by wage quantile

master.do

clear all 
set more off

* download CES data, if new
!wget -N https://download.bls.gov/pub/time.series/ce/ce.data.0.AllCESSeries
!wget -N https://download.bls.gov/pub/time.series/ce/ce.series
!wget -N https://download.bls.gov/pub/time.series/ce/ce.industry

* identify 3-digit industries
insheet using ce.industry, clear tab
keep industry_code naics_code industry_name
keep if length(naics_code) == 3
tempfile industry
save `industry'

* grab series info
insheet using ce.series, clear tab
keep series_id series_title industry_code data_type_code seasonal
tempfile titles
save `titles'

* clean up monthly CES data
insheet using ce.data.0.AllCESSeries, clear tab
keep year period value series_id
keep if year >= 2019
gen month = substr(period,2,2)
destring month, replace
* drop annual data
drop if month == 13
keep series_id year month value 
merge m:1 series_id using `titles', keep(3) nogenerate
merge m:1 industry_code using `industry', keep(3) nogenerate
order series_id series_title year month value*
compress
save ce_monthly.dta, replace

* create 2019 quantiles
use if (data_type_code == 3 | data_type_code == 1) & year == 2019 & seasonal == "U" using ce_monthly, clear
keep year month industry_name naics_code data_type_code value 
reshape wide value, i(year month naics_code) j(data_type_code)
collapse (mean) value1 value3 (first) industry_name, by(naics_code)
rename value3 ahe
rename value1 emp
drop if ahe == . 
gquantiles xtile = ahe [aw=emp], xtile nquantiles(3) 
gegen meanahe = mean(ahe) [aw=emp], by(xtile)
keep naics_code xtile meanahe
tempfile tiles 
save `tiles'

* merge in quantiles
use if data_type_code == 1 & year >= 2019 using ce_monthly, clear
keep value industry_name seasonal naics_code year month
rename value emp 
merge m:1 naics_code using `tiles', keep(3) nogenerate
collapse (sum) emp, by(seasonal year month xtile)
tempfile empdata 
save `empdata'
keep if year == 2019 
collapse (mean) emp_2019 = emp, by(seasonal xtile)
merge 1:m seasonal xtile using `empdata', assert(3) nogenerate 
gen demp_level = emp - emp_2019
gen demp_pct = emp / emp_2019 - 1
keep seasonal xtile year month demp* emp
reshape wide emp demp_level demp_pct, i(seasonal year month) j(xtile)
gen ym = ym(year, month)
format %tm ym
egen group = group(seasonal)
tsset group ym
forvalues i = 1/3 {
  gen Demp`i' = D.emp`i'
}

keep if ym == tm(2021m4)
keep Demp* seasonal
reshape long Demp, i(seasonal) j(xtile)

* figure formatting details
gen sDemp = string(Demp, "%4.0f")
replace sDemp = "+" + sDemp if Demp > 0

lab def quintile 1 "Bottom third" 2 "Middle third" 3 "Top third"
lab val xtile quintile

local mycolor1 68 1 84
local mycolor2 62 74 137
local mycolor3 49 104 142
local mycolor4 31 158 137
local mycolor5 109 205 89

* create figures
foreach group in S U {

  local graphcmd ""
  forvalues i = 1/3 {
    if `i' == 3 local endchar ""
    else local endchar "||"

    if `i' == 2 local mlabpos 6
    else local mlabpos 12

    local graphcmd `graphcmd' scatter Demp xtile if seasonal == "`group'" & xtile == `i', msymbol(none) mlabel(sDemp) mlabpos(`mlabpos') mlabcolor("`mycolor`i''") || bar Demp xtile if seasonal == "`group'" & xtile == `i', color("`mycolor`i''") barw(0.5) `endchar'
  }
  
  twoway `graphcmd' ///
    ylabel(-200(200)400, angle(0) gmin gmax) ///
    xlabel(1 2 3, valuelabel) ///
    xscale(r(0.5 3.5)) ///
    ytitle("Thousands of jobs", size(medsmall)) xtitle("") ///
    legend(off) ///
    title("Employment grew fastest in April in low-wage industries", size(large) color(black) bexpand justification(left)) ///
    subtitle("Change in employment between March and April 2021, by hourly wage percentiles", size(medsmall) bexpand justification(left) margin(b=3)) ///
    note("Source: CES. Hourly wage percentiles calculated from distribution of 2019 CES 3-digit NAICS hourly earnings & employment.", size(small) margin(t=2)) ///
    ysize(4) xsize(7) ///
    graphregion(color(white) margin(t=2 b=1))


  graph export bargraph_`group'.pdf, replace
  !convert -density 300 bargraph_`group'.pdf -quality 100 bargraph_`group'.png
  erase bargraph_`group'.pdf
}