02 expo major system files
EXPO Major System Files
Note: The following file explanations are meant to be a brief overview. Follow each file’s hyperlink to access the File Record Layouts documentation for additional information on the file.
The Alpha Locator File is used to find an establishment's U-I account number from the legal or trade name. Jobs 001D, 001Q, 002D, and 050D (Build the Alpha Locator File) rebuild this file from data collected from the Micro File. The data are then accessed on a read-only basis by screen ES2S, which is able to affect a transfer to other CICS screens by selecting an account.
Two Crosswalk Files exist in the system. They could also be considered macro-to-micro mapping files, since they provide a list of all establishments that belong to specific county, ownership, and NAICS assignments. The County Crosswalk File shows the accounts in each individual macro cell (i.e., at the county level), whereas the Statewide Crosswalk File essentially ignores the county code, grouping all establishments in the same ownership and industry code together. Both files are used by both the ES2F firm list screen, and the ES2D (macro/micro inquiry/update) screen. The County Crosswalk File contains extra records showing the EIN-to-account number crosswalk values. These are used by the ES2S screen when searching for accounts by EIN. A host of jobs rebuild these files, including 001D, 001Q, 011D, 011S, 241D, 242D, and 051D (a stand-alone Crosswalk File rebuild job).
Up to nine Customized Micro Data Selection Files can be produced by Job 020D instead of generating output reports. This temporary file uses the ES2MICD copy member for its record layout. The output file name ('YBUscX.A145.J020D.DATAn' (for DMA States), or 'ES2.J020D.DATAn' (for non-DMA States)) uses a numeric digit ("n" in the names above) of from '1' to '9' to designate which of up to nine sets of parameters was used by the 020D job to create the file. Once Job 020D has completed its run, the output file can be downloaded (via FTP) to a PC for further processing. The same format of output file is used by Job 040D for its own variety of parameter application (with field offsets, lengths, ranges). Job 040D, however, uses "J040D" in the file name. For instance, the Oklahoma third data file for this job will be 'YBU40X.A145.J040D.DATA3'.
The Lookup File stores constant values that are used for editing and estimating. The tables in this file hold batch job parameters, NAICS codes, comment codes, county codes, county-zone combinations, ownership codes, state constants, and various limits and parameters that are required to perform the micro edits. Updates are sometimes required for this file when a new version of the EXPO software is released. Most records are maintained by the ES2L screen, though file lock utilities are handled through ES2I, batch job parameters are dealt with in ES2N, publication headers are maintained with ES2U, and Account Group headers are modified via ES2Z. All of these records are stored in the same Lookup File. The Lookup File is the most widely used file in EXPO/EARS.
Two indexed Macro Data Files are provided to permit aggregation of data at various groupings of counties and industry codes. The County-based Macro File contains both SIC (through 2001) and NAICS (back to 2000) totals for individual county-ownership-industry "cells." The Statewide Macro File combines all counties for statewide macro records at the ownership and SIC/NAICS level. Both files are accessed by the ES2D screen for rapid selection of desired macro totals and on-screen display. They are also used by macro reporting jobs such as 245A (Annual Summary Reports) and 246D (Customized Macro Rollup Reports). Quarters in the county-based Macro File can be rebuilt within Job 011D, Job 011S, Job 241D, Job 242D, and Job 246F. The Statewide Macro File is rebuilt from scratch by these jobs, merging records from the newly revised Macro File.
The Macro Edit File stores macro error codes identified by Job 242D (Run Integrated Edits and Rebuild Crosswalks), which is never used for any BLS deliverable. The data appear in county-ownership-NAICS sequence (except in QMSA States, for which the QMSA subdivides the file by QMSA code). In addition to Job 242D, it is also maintained by the ES2D screen's edit mode.
The Micro Omni (or Omnifarious) Auxiliary (MOA) File (formerly known as the Mailing-Other Address File, but is more global in its scope now) stores a host of record types to provide an abundance of auxiliary data connected to micro records. These include a third (mailing/other) address, contact person information, data disparity indicators (never used, other than setting the description portion of the field to the State Use field for States that have changed their U-I account number (MO and RI in 2017)), email and fax phone number data, and a website address, all for micro records. The usage of these record types is as follows.
If the U-I tax address, which is stored in the Micro File, is unsuitable for mailing MWR and Refiling Forms, a MOA can be entered on the main ES2C screen. The entered MOA does not overwrite the U-I address (which is immutable), stored in the Micro File, but rather creates a new address in the MOA File. MWR and refiling forms normally use the MOA in preference over the U-I tax address. The contact information, email address, fax phone number and website address can all be entered on the ES2C Geocode Screen. However, the data disparity data are not yet entered on any EXPO screen.
The Micro File is the primary micro data file. It contains all data for individual employers/establishments within the system except for the MOA File's various data records just described. Micro records include administrative information (i.e., non-quarterly, such as name, address, telephone number, etc.) on each establishment. In addition, it contains eight quarters of employment and wage data, industry, county, ownership codes, indicator flags, various processing indicators, and comment codes. The oldest quarter cannot be displayed on the ES2C screen. Single, multi-master, and worksite records all reside on this file. At the State's option, the file may contain non-covered accounts. This option makes non-covered firm data available for local use, though these data will not be reported to BLS (presenting a disparity in macro-level edits between State and BLS editing systems). Most data on the file may be maintained on-line. Almost all batch jobs in the micro portion of the EXPO system will read this file.
The Micro Edit File stores micro error codes identified by Jobs 001Q, 001D, 002D, 002F, 003D, 008D, 008S, 018D, 028D, 028S (Run Micro Edits on Back Quarters), 030D (Micro and Micro-Edit General Status Report), 052Q (Update File Counts for the ES2L Files Screen), and 242D. The sort sequence of this file is established each quarter in Job 001Q. The sort parameters determine both the order of the micro edit exception report and the sequence that the ES2E screen advances from record to record. Micro Edit records are selected within the integrated edit process for pairing with Macro Edit File records that have been flagged during the macro edits.
The Narrative Comment File stores narrative micro comments and notes up to 57 bytes in length that describe either micro economic events or analyst notes. These narrative comments/notes are entered through the ES2C screen for the current quarter, ES2E for current or prior quarter, or through the ES2Y screen for any quarters. Both analyst notes and comments can be entered for specific quarters. Perennial (permanent, non-quarter-specific) notes can also be entered on the ES2C or ES2E screen; on ES2Y, they are coded with '999' in the year/quarter field. Job 022D can print a report of micro narrative notes and comments.
The Enhanced Quarterly Unemployment Insurance (EQUI) report is the quarterly deliverable required by BLS to report establishment information. It contains administrative data (i.e., name, address, phone number, etc.), predecessor and successor accounts (which appear in separate records), and employment and wage data. It is a temporary file created by EXPO system programs and it is not directly maintainable by the user. It is produced by Job 011D. An EQUI-format file for a single quarter can also be produced for CES benchmarking with Job 021D. Job 011S is used as a supplemental run to the 011D job. Job 021F is used to produce a Federal EQUI file for hand-picked accounts and quarters in a subset file.
Three files are used with predecessor/successor transfer information. The Predecessor Successor Actual (PSA) File notes the actual predecessor(s) and successor(s) of an employer, along with the date the relationship was discovered, the date the transfer occurred (optional), and a 2-byte code to indicate the source of information used to make the assignment (e.g. "EX" for coming from the extract). Actual predecessors and successors are drawn from the pool of potential relationships found in the Predecessor Successor Potential (PSP) File. This file (an offshoot from the formerly named Employer Transfer File) shows employee counts for shared or matched U-I account numbers across quarters. Narratives of up to 145 characters in length, describing actual relationships, can be loaded into the Predecessor Successor Narrative/Notes (PSN) File. All of these files are maintained on-line with the ES2G screen. The potentials are drawn from Employee Transfer Transaction (ETT) records using the 062D job. Alternatively, this can be run with Job 062S (Add a Quarter Pair to Potential P/S File using Wage File), which runs against the Wage Data File that is produced from the match pair portion of the LEHD format file. To find the most probable potentials that could be actuals P/S relationships, it is best to run Job 063D (Add a Quarter Pair to Potential P/S File using Wage File). This job provides a "scoring" of P/S relationships on the Potentials file, and lists the highest scores on a report; these are the most likely actual relationships (based on employment level, names, addresses, etc.). Unfortunately, this job is currently using the Wage Summary File to obtain the employment level, rather than obtaining this information directly from the Micro File. The reason this is "unfortunate" is that not all States have the Wage Summary File populated; therefore, it is not possible to run this job for those States.
Up to nine Macro Roll-up Output Files can be generated by Job 246D as an alternative to report output. These have a dataset name of 'YBUscX.A145.J246D.FILEn' for DMA States, but one of 'ES2.ROLLUP.FILEn' or 'ES2.J246D.FILEn' for non-Service Center States. In all cases the "n" is a sequential number ('1' through '9') to identify the output file according to the parameter set used to generate the file. The machine-readable format (containing macro-level employment and wage data for various selections of county, ownership, industry, and year/quarter values) can be downloaded to a PC with a utility, such as FTP. There is also an ALMIS (America's Labor Market Information System) equivalent to this job run with Job 247D (Produce ALMIS Version of Macro File – No Data Sharing); everything else is separate for this run, including the file names which have "J247D" instead of "J246D." The parameters are also maintained separately in the ES2N screen.
The Roll-Up Parameters File contains job selection parameters specified in the ES2N screens for Jobs 020D, 040D, 246D and 247D. Though hundreds of parameter sets may exist on the file, the Jobs 020D, 040D, 246D and 247D will isolate nine or fewer of these for use in producing either a report or an output file.
Three micro-level transaction files are included in the system. There is an Administrative Transaction File, a MOA Transaction File, and a Quarterly Transaction File. These files contain records generated by update actions to the Micro File and MOA File through an extensive list of CICS screens and batch jobs. Administrative field changes are written to the Administrative Transaction File, while employment and wage changes and any other quarterly field changes are written to the Quarterly Transaction File. Mailing address, contact info, email and website addresses, and changes to the MOA File are carried in the MOA Transaction File. Each file includes audit information–the date, time, transaction type and either the User ID or batch program performing the change to the record. Change records include an image of the data both before and after the change occurred. Add and delete actions will double-up data for the quarterly transaction records, showing two quarters' data in one record. The ES2P screen views records in these files by merging the administrative, MOA, and quarterly data onto the same screen. Fields that are unaffected by the change will appear as underlined fields; this denotes that the values are being drawn from the original Micro File data, since they are unavailable in the transactions. On-line searches for transactions can be made by User ID, date, and U-I number. An Audit Trail Report can be printed from Job 016D. Older records can be removed with Job 005D. The size is limited to a six-month maximum by Job 001Q automatically.
The Wage Summary File is an optional file that can store summarized wage record counts and wage record total wages by U-I number. It is updated by Job 060D (Add/Replace a Quarter on the Wage Summary File), which requires a State-specific program to retrieve data from the State Wage Record File. This can be run as Job 060S (Add/Replace Quarter on Wage Summary File using Wage File) as a substitute, to get the Wage Record information from the LEHD file. The file is used by the third ES2C screen via the F11 key. It is also used in Job 061D (Perform Wage Summary Edits) to produce the wage summary edits.
Related Links
