TECHNICAL NOTE ON TAXABLE WAGE MEASURES
IN TENNESSEE ADMINISTRATIVE DATA

Sara Spaziani | University of Warwick
Contact: Sara.Spaziani@warwick.ac.uk
Reference run: 29 September 2026

1. PURPOSE

This package documents an issue with the employer-reported taxable-wage
measure in the supplied Tennessee administrative data and provides Stata code
to reconstruct employer-year-quarter taxable wages from individual wage
records. The note discusses using the reconstructed measure for 2004–2015.
The findings apply to the supplied extracts and the analysis sample described
below. The package does not establish the administrative cause of the issue.

2. PACKAGE CONTENTS

Recommended distribution layout (retain the code's existing filenames):

  README.txt
  Technical_Note_TN.pdf
  Technical_Note_TN.docx                       [optional editable copy]
  code/
    technical_note_taxable_wages.do
  results/
    techincal_note.log
    techincalnote_tot_tax_wage.pdf             [Figure 1]
    technicalnote_tot_wage.pdf                 [Figure 2]
    technicalnote_tax_wage.pdf                 [Figure 3]

The spellings "techincal_note" and "techincalnote" are intentional here:
they reproduce the filenames used by the supplied do-file.

3. REQUIRED INPUT DATA

The restricted administrative data are not distributed with this package.
Obtain authorized access to the same extracts to reproduce the reference run.

Employer_Data_V1.txt
  Source: employer data supplied by TDLWD from QCEW.
  Imported rows: 12,045,233; year range shown in the log: 2002–2023.
  Variables used: bu_ui_employer_id, qtr_year, qtr_number, status,
  total_wage, tax_wage.

BU_UI_Employment.txt
  Source: individual wage records supplied by TDLWD.
  Imported rows: 306,436,346.
  Variables used: eid, ui_employer_id, wage_year, wage_quarter, wage_amount.
  The note reports coverage of 2000–2024.

4. SOFTWARE AND RUNNING THE CODE

Use Stata with sufficient memory and disk space for more than 306 million
input wage records, intermediate data, and temporary files. No user-written
Stata commands are invoked by the supplied do-file. 

Before running:

  a. Keep the supplied log and figures as reference outputs. Use separate
     output folders for a new run: the do-file uses "replace" and overwrites
     its log, saved sample, and figure PDFs.

  b. Edit the two "import delimited" paths to point to your input extracts.
     The supplied paths are Windows-specific:
       D:\U\Brown University\Employer_Data_V1.txt
       D:\U\Brown University\new source\BU_UI_Employment.txt

  c. Edit the "log using", "save", and three "graph export" paths to point
     to existing writable folders. The supplied output folders are:
       C:\Users\AGP2166\results
       C:\Users\AGP2166\data


The reference log opened at 06:20:31 and closed at 06:53:06 on
29 September 2026 (32 minutes 35 seconds). This is the recorded elapsed
interval, not a performance guarantee. No independent rerun is documented here.

5. WHAT THE SCRIPT DOES

Employer data
  Drops rows with a missing employer identifier and status == "2".
  Drops ALL rows belonging to duplicated employer-year-quarter keys; it
  does not retain one row from each duplicate group.
  Retains 10,492,995 employer-year-quarter observations (87.1133% of input).

Employee data
  Sums wage_amount within employee-employer-year-quarter before constructing
  taxable wages. This leaves 306,427,644 employee-employer-year-quarter rows.
  Orders each employee-employer-year's records by quarter and applies the
  annual base across successive appearances within that employer and year.
  The bases implemented by the code are:
    2009–2015: $9,000
    2016–2017: $8,000
    Other years in the input: $7,000
  Sums total and constructed taxable wages to employer-year-quarter,
  producing 11,015,554 observations.

Merge and analysis
  Matches the two files on bu_ui_employer_id, year, and quarter and keeps
  only matched observations. All reported comparisons and all three figures
  are calculated after this restriction.
  Computes correlations and absolute differences at employer-year-quarter
  level, then takes unweighted means across employers for each year-quarter
  to create the figures. Plot values are divided by 1,000.
  The correlations are not correlations between the plotted quarterly means.

6. OUTPUTS AND REFERENCE CHECKS

technical_note_sample.dta
  A restricted employer-year-quarter matched dataset with 8,802,019 rows.
  Includes total_wage and tax_wage from the employer file, wage_amount and
  tax_wage_w reconstructed from the wage file, and the merge indicator.
  It is saved before yq and diff are generated and before quarterly means
  are calculated. It is not a final replacement employer file: the code
  does not overwrite tax_wage with tax_wage_w for 2004–2015.
  Keep this data file in the authorized data environment; do not include it
  in a general documentation ZIP.

Merge checks
  Matched:             8,802,019
  Employee-file only:  2,213,535
  Employer-file only:  1,690,976
  Combined merge:     12,706,530
  The matched share of the combined merge population is 69.27%.

Equality check, 2004–2015
  5,064,136 matched observations; all 5,064,136 satisfy
  tax_wage == total_wage in the recorded check.
  This check does not cover unmatched employer records.

Correlations of tax_wage and tax_wage_w, 2016 onward
  Overall: 0.7862 (2,924,817 observations)
  Q1: 0.7771; Q2: 0.8540; Q3: 0.7122; Q4: 0.9466
  For 2016–2021: 0.8065 (2,518,340 observations)

Absolute differences, 2016 onward, in dollars
  Median: 0.000061
  75th percentile: 0.4000244
  95th percentile: 7,000.32
  Mean employer-reported taxable wage: 43,166.36

The three figure PDFs are reference outputs of the same run. Minor visual
styling can depend on the Stata version and active graph scheme.

7. INTERPRETATION AND REUSE

The diagnostics describe matched employer-year-quarter records, not every
record in either original extract. 

Keep the raw inputs and derived microdata subject to their existing access
restrictions. The documentation package does not grant data access or
redistribution rights. 
