<jupyter_start><jupyter_text>The goal of this notebook is to generate a slew of employer-level characteristics given a UI wage records table. We will find these employer-level characteristics for all employers with at least five unique employees in Indiana for the 2017 calendar year (2017Q1-2017Q4). We will define each employer as a unique `uiacct` value in this dataset.Here are the statistics we will find:    - Total payroll    - Average earnings per employee    - Earnings per employee at the 75th percentile    - Earnings per employee at the 25th percentile    - Industry    - Number of full quarter employees    - Total payroll for full quarter employees    - Average earnings per full quarter employee    - Employment, Separation, and Hiring Growth Rates    Our final output from this notebook is a permanent table with employer-level information spanning 2017Q1-2017Q4 for each employer with at least 5 employees in Indiana that exists in its UI wage records.<jupyter_code># load packages
library(lubridate)
library(tidyverse)
library(DBI)
library(RPostgreSQL)
# connect to our database
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,dbname = "postgresql://stuffed.adrf.info/appliedda")<jupyter_output><empty_output><jupyter_text>There are some suspect `ssn` values that we will not include in this analysis. Therefore, we will create a temporary table of Indiana's UI wage records while subsetting the data to the year/quarter combinations we will need to generate this table.<jupyter_code># we need all quarters we want, plus 2 before and 1 after
qry = "
create temp table wages_2017 as
select *, format('%s-%s-1', year, quarter*3-2)::date as job_yr_q
from in_dwd.wage_by_employer
where (year = 2017 or (year=2016 and quarter in (3,4)) or (year=2018 and quarter=1)) and
    ssn not in REDACTED 
"
dbGetQuery(con, qry)<jupyter_output><empty_output><jupyter_text>First, we will create temporary tables of all the UI wage record information from `wages_2017` for five quarters: 2016Q4-2017Q4. We need to include information on the quarter prior to our desired start (2017Q1) since we need employment, separation, and hiring rates for 2016Q4 to calculate growth rates for these measures in 2017Q1.<jupyter_code>table<-c("q4_2016", "q1_2017","q2_2017","q3_2017","q4_2017")
year <- c(2016, 2017, 2017, 2017, 2017)
q <- c(4, 1, 2, 3, 4)
for(i in 1:5){
    qry = '
    create temp table "%s" as 
    select *
    from wages_2017
    where quarter = %d and year = %d and uiacct is not null
    '
    full_qry = sprintf(qry, table[i], q[i], year[i])
    dbExecute(con, full_qry)
}<jupyter_output><empty_output><jupyter_text>Then, we will add columns to track if each `uiacct`/`ssn` combination within a given quarter exists in the wage record table the quarter before and/or the quarter after. This will be important in tracking full-quarter employment, as well as hiring and separation numbers.<jupyter_code>new_cols <- c('pre_emp', 'post_emp')

for(col in new_cols){
    for(i in 1:5){
        qry='
        ALTER TABLE "%s" ADD COLUMN "%s" int
        '
        full_qry = sprintf(qry,table[i], col)
        dbExecute(con, full_qry)
    }
}<jupyter_output><empty_output><jupyter_text>After the `pre_emp` and `post_emp` columns are initialized in each of these temporary tables, we can set these as indicator variables if the `ssn`/`uiacct` combination that appeared in the UI wage records for the given year/quarter combination also existed in the previous and future quarter.<jupyter_code># order employment flags properly for 0-5 index below
preYr = c(2016, 2016, 2017, 2017, 2017)
preQ = c(3, 4, 1, 2, 3)

# loop through an integer list
# 0 is 4th lag (4 quarters ago)

for(i in 1:5){
    # update this quarter employment flag
    qry='
    UPDATE "%s" a SET pre_emp = 
        CASE WHEN b.wages is null THEN 0 ELSE 1 END
    FROM wages_2017 b
    WHERE b.year= %d AND b.quarter= %d --grab correct quarter
        AND a.ssn=b.ssn AND a.uiacct=b.uiacct --ensure same job
    '
    full_qry = sprintf(qry, table[i], preYr[i], preQ[i])
    dbExecute(con, full_qry)
    }
# see values of pre_emp
qry = "
select distinct(pre_emp)
from q4_2016
"
dbGetQuery(con, qry)
# order employment flags properly for 0-5 index below
postYr = c(2017, 2017, 2017, 2017, 2018)
postQ = c(1, 2, 3, 4, 1)

# loop through an integer list
# 0 is 4th lag (4 quarters ago)

for(i in 1:5){
    # update this quarter employment flag
    qry='
    UPDATE "%s" a SET post_emp = 
        CASE WHEN b.wages is NULL THEN 0 ELSE 1 END
    FROM wages_2017 b
    WHERE b.year= %d AND b.quarter= %d --grab correct quarter
        AND a.ssn=b.ssn AND a.uiacct=b.uiacct --ensure same job
    '
    full_qry = sprintf(qry, table[i], postYr[i], postQ[i])
    dbExecute(con, full_qry)
    }
# take a peek at one of the tables
qry <- "
select *
from q4_2016
limit 5
"
dbGetQuery(con, qry)<jupyter_output><empty_output><jupyter_text>Now that we have pre and post-quarter employment indicators for each `ssn`/`uiacct` combination, we can add hiring and separation indicators into these tables.<jupyter_code>new_cols <- c('sep', 'hire')

for(col in new_cols){
    for(i in 1:5){
        qry='
        ALTER TABLE "%s" ADD COLUMN "%s" int
        '
        full_qry = sprintf(qry,table[i], col)
        dbExecute(con, full_qry)
    }
}
for(i in 1:5){
    qry='
    UPDATE "%s" a SET sep = 
    CASE WHEN post_emp is null THEN 1 ELSE 0 END
    '
    full_qry = sprintf(qry,table[i])
    dbExecute(con, full_qry)
}
# look at different values of sep
qry = '
select count(*), sep 
from q4_2016 group by sep
'

dbGetQuery(con, qry)
for(i in 1:5){
    qry='
    UPDATE "%s" a SET hire = 
    CASE WHEN pre_emp is null THEN 1 ELSE 0 END
    '
    full_qry = sprintf(qry,table[i])
    dbExecute(con, full_qry)
}
# look at one of the tables again
qry = '
select * 
from q4_2016 
limit 5'
dbGetQuery(con, qry)<jupyter_output><empty_output><jupyter_text>Aggregate by EmployerAt this point, we have all the information we need to aggregate on the `uiacct` values. We will do these aggregations in separate steps, as they will require separate `WHERE` clauses. In the first, we will find values for all measures outside of the full-quarter employee-related ones.<jupyter_code>emp_tabs <- c("gen_q1", "gen_q2", "gen_q3", "gen_q4")
for(i in 1:4){    
    qry = '
    create temp table "%s" as
    select uiacct,  naics_3_digit, quarter, count(ssn) as num_employed,
    sum(wages)/count(ssn) as avg_earnings, sum(wages) as total_earnings,
    percentile_disc(0.25) within group (order by wages) as bottom_25_pctile,
    percentile_disc(0.75) within group (order by wages) as top_25_pctile,
    sum(hire) as num_hire, sum(sep) as num_sep
    from "%s"
    group by uiacct, naics_3_digit, quarter
    '
    full_qry = sprintf(qry, emp_tabs[i], table[i+1])
    dbGetQuery(con, full_qry)
    }
# see these stats aggregated by employer for q1
qry = "
select * from gen_q1 limit 5
"
dbGetQuery(con, qry)<jupyter_output><empty_output><jupyter_text>In a separate table, we can find all of the statistics related to full-quarter employment.<jupyter_code>full_tabs <- c("fq_q1", "fq_q2", "fq_q3", "fq_q4")
for(i in 1:4){    
    qry = '
    create temp table "%s" as
    select uiacct, naics_3_digit, quarter, count(distinct(ssn)) as full_num_employed, 
    sum(wages)/count(distinct(ssn)) as full_avg_earnings, sum(wages) as full_total_earnings
    from "%s"
    where post_emp = 1 and pre_emp = 1
    group by uiacct, naics_3_digit, quarter
    '
    full_qry = sprintf(qry, full_tabs[i], table[i+1])
    dbGetQuery(con, full_qry)
    }
# see a full quarter employment table
qry = "
select * from fq_q1 limit 5
"
dbGetQuery(con, qry)<jupyter_output><empty_output><jupyter_text>Finally, we need information on these employer's hiring, employment, and separation numbers for the prior year to calculate their growth rates.<jupyter_code>old_tabs <- c("pre_q1", "pre_q2", "pre_q3", "pre_q4")
for(i in 1:4){    
    qry = '
    create temp table "%s" as
    select uiacct,  naics_3_digit, quarter, count(ssn) as num_employed_pre, sum(hire) as num_hire_pre, 
    sum(sep) as num_sep_pre
    from "%s"
    group by uiacct, naics_3_digit, quarter
    '
    full_qry = sprintf(qry, old_tabs[i], table[i])
    dbGetQuery(con, full_qry)
    }
# see quarter before information
qry = "select * from pre_q1 limit 5"
dbGetQuery(con, qry)<jupyter_output><empty_output><jupyter_text>Now that we have all the information we need in three tables, we can join them together based on the `uiacct` and `naics_3_digit` values.<jupyter_code>tabs <- c("q1", "q2", "q3", "q4")
for(i in 1:4){
    qry = '
    create temp table "%s" as
    select a.*, 
    case 
        when b.full_num_employed is null then 0 
        else b.full_num_employed end as full_num_employed,
    b.full_avg_earnings, b.full_total_earnings
    from "%s" a
    left join "%s" b
    on a.uiacct = b.uiacct and a.quarter = b.quarter and a.naics_3_digit = b.naics_3_digit
    where a.num_employed >= 5
    '
    full_qry = sprintf(qry, tabs[i], emp_tabs[i], full_tabs[i])
    dbExecute(con, full_qry)
    }
# see joined full quarter and current quarter measures
qry = "select * from q1 limit 5"
dbGetQuery(con, qry)<jupyter_output><empty_output><jupyter_text>To calculate the hiring, separation, and employment growth rates, we will use the following function from Davis and Haltiwanger (1992) to calculate 1) employment growth rate: `emp_rate`; 2) separation growth rate: `sep_rate`; 3) hire growth rate: `hire_rate`.$$ g_{et}=\frac{2(x_{et} - x_{e,t-1})}{(x_{et} + x_{e,t-1})} $$In this function, $g_{et}$ represents employment/separation/hire growth rate of employer $e$ at time $t$. $x_{et}$ and $x_{e,t-1}$ are employer $e$'s employment/separation/hire at time $t$ and $t-1$, respectively. According to Davis and Haltiwanger (1992):"*This growth rate measure is symmetric about zero, and it lies in the closed interval [-2,2] with deaths (births) corresponding to the left (right) endpoint. A virtue of this measure is that it facilitates an integrated treatment of births, deaths, and continuing establishments in the empirical analysis.*"In other words, a firm with a $ g_{et} = 2$ is a new firm, while a firm with a $ g_{et} = -2$ is a a firm that exited the economy.    > Why do the two endpoints represent firms' deaths and births? Calculate the value of $g_{et}$ when $x_{et}=0$ and when $x_{e,t-1}=0$ and see what you get.In practice, we will apply this formula for every `uiacct` unless it experienced no hires or separations in the current and previous quarters, where instead of getting a divide by zero error, we will assign it to 0.<jupyter_code>final_table <- c("q1_2017_emp", "q2_2017_emp", "q3_2017_emp", "q4_2017_emp")
for(i in 1:4){
    qry = '
    create temp table "%s" as
    select a.uiacct, a.naics_3_digit, a.quarter, a.num_employed, a.avg_earnings, a.total_earnings, a.bottom_25_pctile,
    a.top_25_pctile, a.full_num_employed, a.full_avg_earnings, a.full_total_earnings,
        (2.0 * (a.num_employed - b.num_employed_pre))/(a.num_employed + b.num_employed_pre) as emp_rate,
    case 
        when a.num_hire = 0 and b.num_hire_pre = 0 then 0
        else (2.0 * (a.num_hire - b.num_hire_pre))/(a.num_hire + b.num_hire_pre) end as hire_rate, 
    case
        when a.num_sep = 0 and b.num_sep_pre = 0 then 0
        else (2.0 * (a.num_sep - b.num_sep_pre))/(a.num_sep + b.num_sep_pre) end as sep_rate
    from "%s" a
    left join "%s" b
    on a.uiacct = b.uiacct and a.naics_3_digit = b.naics_3_digit 
    '
    full_qry = sprintf(qry, final_table[i], tabs[i], old_tabs[i])
    dbExecute(con, full_qry)
    }<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10071201852963885, 'fraction_numerical': 0.03182637042120614, 'mean_word_length': 3.8983193277310924, 'pattern_counts': {'":': 0, '<': 58, '<?xml version=': 0, '>': 47, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '602034', 'license': 'CC0-1.0', 'n_tokens_mistral': 4336, 'n_tokens_neox': 3977, 'n_words': 1409, 'path': 'Notebooks/Supplemental/Create_Employer_Characteristics.ipynb', 'repo_name': 'Coleridge-Initiative/ada-tdc-2020', 'size': '11657'}