Basic
Information
Parts of EXCEL Spreadsheet
Four EXCEL Columns in Reference
to One Data Source
Introduction to MASTGROUPS
Introduction to MASTDATA
Introduction to Flag_Reference
Introduction to
Footnote_Reference
Introduction to Unit_Reference
Introduction to Source
How do EXCEL Data Sources Relate?
Additional Detail Information
Windows XP File Download Settings
Data Download Screen
Additional Data File
Information
|
Part: |
Description: |
|
A, B, C, & D |
A, B, C, & D -
Four columns providing flag, datavalue, footnote1, and footnote2 data of
original AFN110197D data source. Each additional data source will require
four EXCEL columns, e.g. [EXCEL columns G, H, I, and J], or [EXCEL columns K, L, M, and
N], etc. |
|
A |
Flag column header -
e.g. AFN110197F - flag data stored in this column from rows 2 to 3197. |
|
B |
DataValue column header
- e.g. AFN110197D - data values stored in this column from rows 2 to 3197. |
|
C |
Footnote1 column
header - e.g. AFN110197N1 - footnote1 values stored in this column from rows
2 to 3197. |
|
D |
Footnote2 column
header - e.g. AFN110197N2 - footnote2 values stored in this column from rows
2 to 3197. |
|
Part: |
Description: |
|
E |
Column E header
"Areaname" located in cell A1 - Areaname values stored in EXCEL
cells A2 to A3197. |
|
F |
Microsoft standard
column references, e.g. A, B, C, D, etc. |
|
G |
Code column header -
FIPS State and County Code stored in this column from rows 2 to 3197. |
|
H |
Microsoft standard
row numbers, e.g. 1, 2, 3, 4, 5, ... |
|
I |
Sample of areaname
values - values refer to 1) UNITED STATES, 2) a state name, or 3) a county name
followed by comma and state abbreviation. |
|
J |
Sample of STCOU
values - text 5 positions - first two positions refer to state code and last
three positions refer to county code. It should be noted that all states have
"000" in their last three positions, e.g. "01000".
Moreover, the UNITED STATES code is always "00000". |
|
K |
Sample of FLAG values
- integer value of 0, 1, 2, 3, 4, 5, 6, 7 or 8. |
|
L |
Sample of Data values
- long integer format e.g., 545068, 6955, 50, etc. |
|
M |
Sample of Footnote1
values - text 4 positions - each unique footnote code has corresponding note
which is available in the MASTFOOT table. |
|
N |
Sample of Footnote2
values - text 4 positions - each unique footnote code has corresponding note which
is available in the MASTFOOT table. |
|
NOTE: |
More detail about data source (EXCEL column)
names is available at and more detail about table
(column) groups is available at III. Introduction to
MASTGROUPS |
II. Four EXCEL Columns in Reference to One Data Source
|
Part: |
Description: |
|
A |
The first three
characters make up a three-character abbreviation (acronym) for a particular
data group. In this case, "AFN" stands for "Accommodation and
Food Services - NAICS Only." All acronyms and their corresponding title
are available in the EXCEL file MASTGROUPS.xls. |
|
B |
The first three
digits (positions 4-6) makes up a subgroup-code of the group (positions 1-3).
There is no one pattern; each group has its own series of sub-groups, e.g.
110, 120, 130, 200, 210, etc. |
|
C |
The next three digits
(positions 7-9) represent the year of the data. Position 7 may be 1 or 2; 1
is for a year in the 20th century and 2 is for a year in the 21st century.
Positions 8-9 are the last two digits of a particular year. For example, 197
stands for 1997 and 207 stands for 2007. |
|
D |
The last character
"D" (position 10) stands for "data". However, data from
one data source file is loaded into four EXCEL columns; as an example, for the
data source AFN110197D, there would be four EXCEL columns AFN110197F, AFN110197D,
AFN110197N1, AFN110197N2 (used for Flag, DataValue, Footkey1, Footkey2 data) |
|
E |
This column is FLAG
data |
|
F |
This column is the
data value |
|
G |
This column is
FOOTKEY1 data |
|
H |
This column is
FOOTKEY2 data |
|
NOTE: |
More detail about data source (EXCEL column)
names and descriptions is available at IV.
Introduction to MASTDATA |
III. Introduction to MASTGROUPS
What is MASTGROUPS.xls ?
MASTGROUPS.xls is an EXCEL file that has two
columns KeyAbr and Descript. A three character abbreviation
(acronym) for its official title is listed under KeyAbr, and the
official title is listed under Descript.
Can I download
MASTGROUPS? What does MASTGROUPS data look like?
The MASTGROUPS data
information can be downloaded as an EXCEL file or as a ZIP file.
Listed below are examples of standard groups (subject to change each year).
KeyAbr - Descript
AFN - Accommodation and Food Services -
NAICS only
AGE - Age
AGN - Agriculture - NAICS
AGS - Agriculture - SIC
ANC - Ancestry
BNK - Banking
BPS - Building Permits
BZA - Business, Private Nonfarm - (U.S. Census Bureau) - Total
BZN - Business, Private Nonfarm - (U.S. Census Bureau) - NAICS
BZS - Business, Private Nonfarm - (U.S. Census Bureau) - SIC
CLF - Civilian Labor Force (Bureau of Labor Statistics)
CRM - Crime
EAN - Earnings, Total and Selected Industries (Bureau of Economic
Analysis) - NAICS
EAS - Earnings, Total and Selected Industries (Bureau of Economic
Analysis) - SIC
EDU - Education
ELE - Elections (CQ Press)
EMN - Employment (Bureau of Economic Analysis) - NAICS
EMS - Employment (Bureau of Economic Analysis) - SIC
FED - Federal Government
GAN - Government (Bureau of Economic Analysis) - Earnings - NAICS
GEE - Government (Bureau of Economic Analysis) - Earnings/Employment -
SIC
GEN - Government (Bureau of Economic Analysis) - Employment - NAICS
HEA - Health
HIS - Hispanic or Latino Population
HSD - Households
HSG - Housing
INC - Income, Money (U.S. Census Bureau)
IPE - Income and Poverty
LFE - Labor Force (U.S. Census Bureau)
LND - Land Area
LOG - Local Area Government
MAN - Manufactures - NAICS
MAS - Manufactures - SIC
NES - Nonemployer Statistics
PEN - Personal Income (Bureau of Economic Analysis) - NAICS
PIN - Personal Income (Bureau of Economic Analysis) - SIC
POP - Population - Total and Selected Characteristics
PPQ - Population - Group Quarters
PST - Population Estimates
PVY - Poverty
RHI - Race data
RTN - Retail Trade - NAICS
RTS - Retail Trade - SIC
SBO - Survey of Business Owners
SEX - Sex data
SPR - Social Programs - Social Security and Supplemental Security Income
(SSI)
VET - Veterans
VST - Vital Statistics - Births, Deaths, and Infant Deaths
WAT - Water Usage
WTN - Wholesale Trade - NAICS
What is MASTDATA.xls ?
MASTDATA.xls is an EXCEL file that has six
columns Item_id, Item_description, Unit_indicator, Decimal_indicator, US_Total,
and Source. To help identify what specific EXCEL data you are looking
for, the Item_Id identifies the table series (as discussed in Part II.)
What are Item_id and
Item_description ?
Item_Id AFN110197D refers to the EXCEL columns
(AFN110197F, AFN110197D, AFN110197N1, AFN110197N2). The title of AFN110197D is
provided in the Item_description column.
What are Unit_indicator,
Decimal_indicator, US_Total, and Source ?
Unit_indicator refers to the measurement units
applied to the data values (in a D column, e.g. AFN110197D); see section VII.
for more information. Decimal_indicator refers to number of decimal
places involved in data-value (e.g. with a decimal_indicator = 1 and with a
data-value = 102, this implies 10.2 is the actual value). The US_Total
is the sum total of all state and county values in a table. The Source
value specifies the provider of the data information.
Can I download MASTDATA?
What does MASTDATA data look like?
The MASTDATA data
information can be downloaded as an EXCEL file or as a ZIP file.
Listed below are examples of standard MASTDATA Item_id names and titles
(subject to change each year).
Item_Id: Item_Description: Unit_indicator Decimal_indicator US_Total Source
Item_Id Item_Description Unit_Indicator Decimal_Indicator US_Total Source
AFN110197D Accommodation and Food Services: total (NAICS 72) -
establishments with payroll
1997 ABS
0 545068 CENSUS
AFN110202D Accommodation and Food Services: total (NAICS 72) -
establishments with payroll
2002 ABS
0 565590 CENSUS
AFN120197D Accommodation and Food Services: total (NAICS 72) -
sales of establishments with payroll
1997 TH$
0 350399194 CENSUS
AFN120202D Accommodation and Food Services: total (NAICS 72) -
sales of establishments with payroll
2002 TH$ 0 449498718 CENSUS
AFN130197D Accommodation and Food Services: total (NAICS 72) -
annual payroll 1997
TH$
0 97007396 CENSUS
AFN130202D Accommodation and Food Services: total (NAICS 72) -
annual payroll
2002 TH$ 0 127554483 CENSUS
AFN140197D Accommodation and Food Services: total (NAICS 72) - paid
employees for pay period including March 12, 1997 ABS
0 9451226 CENSUS
AFN140202D Accommodation and Food Services: total (NAICS 72) - paid
employees for pay period including March 12, 2002 ABS
0 10120951 CENSUS
etc.....
V. Introduction to Flag_Reference.
What is
Flag_Reference.xls?
Flag_Reference.xls is an EXCEL file that has two
columns Id and Description.
What are Id and
Description ?
Excel Flag_Reference.xls
column, e.g. AFN110197F has an ID value of 0, 1, 2, 3, 4, 5, 6, 7, or 8. These
are the nine valid "flag" values. The Description field provides
an explanation of its corresponding "ID" value. However, the
Flag_Reference.xls file currently defines the flag values of 3, 4, 5, 6, 7, and
8.
Can I download
Flag_Reference data? What does Flag_Reference data look like?
The Footnote_Reference data
information can be downloaded as an EXCEL file or as a ZIP file. Listed below
are examples of standard Flag_Reference Id and Description (subject to change
each year).
ID: - DESCRIPTION:
3
- Data not enumerated, tabulated, or otherwise available separately.
4 - Figure withheld to avoid disclosure pertaining to a specific
organization or individual.
5 - Figure not applicable because column heading and stub line make entry
impossible, absurd, or meaningless.
6 - Figure does not meet publication standards for reasons other than
that covered by the ID 8 below.
7 - Entry would amount to less than half the unit of measurement shown.
8 - Base figure too small to meet statistical standards for reliability
of a derived figure.
etc......
VI. Introduction to Footnote_Reference.
What is
Footnote_Reference.xls?
Footnote_Reference.xls is
an EXCEL file that has two columns, Id and Description.
What are Id and
Description ?
Excel footnote columns,
e.g. AFN110197N1 and AFN110197N2 usually have default values of
"0000" in each cell. However, some cells have a non-zero footnote
value in their cell, e.g. 5002, 5016, etc. To help understand what these values
mean, you can use the Footnote_Reference.xls file. The Id field holds the
unique footnote (4 text characters) and the Description field provides a
definition of the footnote code.
Why are there two
footnote columns ?
In reference to each
Item_id in MASTDATA, e.g. AFN110197D, AFN110202D, etc, each of their records is
entitled to a maximum of two footnotes at the most. However, very few use two
footnotes. Most records (federal, state, county) only have one footnote or else
a default value of '0000'. Because it is possible to have two footnotes, this
is why each Item_id in MASTDATA has two Excel columns, e.g., AFN110197N1 and
AFN110197N2 for the Item_id of AFN110197D.
Can I download
Footnote_Reference data? What does Footnote_Reference data look like?
The Footnote_Reference data
information can be downloaded as an EXCEL file or as a ZIP file. Listed below
are examples of standard Footnote_Reference Id and Description (subject to
change each year).
ID: - DESCRIPTION:
0000
- (No Description)
5002 - Independent city of Bedford included with Bedford County; data not
available separately. **51515 incl. with 51019.
5003 - Independent city of Bristol included with Washington County; data not
available separately. **51520 incl. with 51191.
5004 - Independent cities of Buena Vista and Lexington included with Rockbridge
County; data not available separately.**51530 and 51678 incl. with 51163.
5005 - Independent city of Charlottesville included with Albemarle County; data
not available separately. **51540 incl. with 51003.
5006 - Independent cities of Clifton Forge and Covington included with
Alleghany County; data not available separately. **51560 and 51580 incl. with
51005.
5007 - Independent city of Colonial Heights included with Chesterfield County;
data not available separately. **51570 incl. with 51041.
5009 - Independent city of Covington included with Alleghany County; data not
available separately. **51580 incl. with 51005
5010 - Independent city of Danville included with Pittsylvania County; data not
available separately. **51590 incl. with 51143
etc......
VII. Introduction to Unit_Reference.
What is
Unit_Reference.xls?
Unit_Reference.xls is an
EXCEL file that has two columns, Id and Description.
What are Id and
Description ?
First, data values may
occur as a long integer or float (decimal) number. However, in reference to
different Item_ids, e.g. AFN110197D, POP110202D, WAT110202D, etc. data may vary
in reference to unit being used. The acronyms for the different units are available
in the column Id. The descriptive meaning of Id is stored in the column
Description.
Can I download
Unit_Reference data? What does Unit_Reference data look like?
The Unit_Reference data
information can be downloaded as an EXCEL file or as a ZIP file. Listed below
are examples of standard Unit_Reference Id and Description (subject to change
each year).
ID: - DESCRIPTION:
ABS
- Absolute Number
ACR - Acres
AVG - Average
DOL - Dollars
GLD - Gallons per Day
MGD - Millions of Gallons per Day
MIL - Millions
MIN - Minutes
ML$ - Millions of Dollars
PCT - Percent
RNK - Rank
RTE - Rate
SQM - Square Miles
SYM - Symbol
TH$ - Thousands of Dollars
THO - Thousands
YRS - Years
etc......
What is Source?
Source.xls is an EXCEL file
that has two columns Id and Description.
What are Id and
Description ?
First, Id values are
acronyms for the values in Description. Description provides the full-title of
an organization providing data. Both of these fields are text data. The Source values
also are used in the MASTDATA.xls file.
Can I download Source
data? What does Source data look like?
The Source data is located
in the Source.xls file and can be downloaded as an EXCEL file or as a ZIP file.
Listed below are examples of standard Source Id and Description (subject to
change each year).
ID: - DESCRIPTION:
AMA
- American Medical Association
BEA - Bureau of Economic Analysis
BLS - Bureau of Labor Statistics
CENSUS - U.S. Census Bureau
CMS - Centers of Medicare and Medicaid Services
CQI - CQ Press
FBI - Federal Bureau of Investigation
FDIC - Federal Deposit Insurance Corporation
NASS - National Agriculture Statistics Service
NCES - National Center of Educational Statistics
NCHS - National Center for Health Statistics
SSA - Social Security Administration
USGS - United States Geographical Survey
etc......
IX. How do EXCEL Data Souces Relate?
Sections I. and II. introduced the parts of an EXCEL
spreadsheet and how four EXCEL columns relate to one data source.
Section III. - Section VII. introduce the MASTGROUPS.xls, MASTDATA.xls,
Flag_Reference.xls, Unit_reference.xls,
and SOURCE.xls files. How do these files relate to each other? To help simplify the
relationship between an EXCEL data
sheet (e.g. AFN01.xls, BZA01.xls, POP01.xls, etc.) and the MASTGROUPS.xls, MASTDATA.xls,
Flag_Reference.xls,
Unit_reference.xls, and SOURCE.xls files, a diagram is available below, to
help illustrate their relationships.
X. Additional Detail Information
Where can I find
additional information on MASTDATA, MASTGROUPS, data type, file formats,
finding correct data file, finding corresponding EXCEL columns, etc.?
For
information on standard USA Counties data, Flag_reference, Footnote_reference,
Mastdata,
Mastgroups, Source, and Unit_Reference files,
please visit ==> Basic Data File Information
Where can I find information on data quality?
For comments on data
quality, see Appendix A - Source Notes and Explanations from the 2007
edition of the country and City Data Book Source
Notes and Explanations [PDF Format]
Where can I find additional information on CenStats - USA
Counties?
For further information
visit CenStats - USA
Counties TM
Where can I find additional information on Contact for
Additional Information?
For further information
send email to: ACSD.US.Data@Census.gov
XI. What Windows XP File Download Settings are needed?
Windows XP File Download Setting
1) Double-Click My Computer icon
2) Select Tools from Windows Toolbar
3) Select Folder Options...
4) Select File Types Tab
5) Under Registered File
Types, select XLS Microsoft EXCEL
6) Click Advanced button
7) Make sure the Confirm open after download box is checked
8) Click OK button
9) Click Close button
Top of Page: Go To Top
Source:
U.S. Census Bureau, Administrative and Customer Services Division, Electronic
Products Development Branch
