ASCII data set information

A data set consists of one geographic identifier (Geo ID) file and thirty-nine data files. The Geo ID file should initially appear last in the data directory and will contain "geo" as part of its' filename. The Geo ID file is not a "header file" as it is linked horizontally with the data files, not placed on top of them vertically. Any data file used must be linked to the Geo ID file (on the unique key field LOGRECNO 1) because the data files do not contain any geographic identifiers. Each data file contains a different set of demographic data tables.

None of the files contain a header record (first record or row with field names). Microsoft Access ™ and SAS ™ templates and instructions are provided to assist in importing the ASCII text files into these programs. The Geo ID file is fixed width with no field delimiters while all thirty nine data files are variable length with comma field delimiters.

The field SUMLEV 2 in the Geo ID file identifies the summary level (area type) of each record. A combination of the geographic identifier codes for each element in the complete summary level description is used to identify the specific area being tabulated. 100% housing unit and population counts are contained in the Geo ID file.

Download the Summary File 1 template file [ZIP, 439 KB] (Access 97 format) provided here to get started. Next, open it in Microsoft Access and convert it to the current version of Access you are using if prompted. Next, follow the procedure(s) below.

Notes

Some data files are too large to import into Access 97. The file size limits for an Access database in versions 97, 2000 and 2002 are 1 GB, 2 GB and 2 GB respectively. You may get misleading error messages if the size of your Access database file reaches these limits. Link can be used instead of import to keep the size of an Access database small (see the Import text data files into Microsoft Access section).

The "uf?" file name extension much be changed to "txt" otherwise Access 2000 users will get an error message "Cannot Update. File or Object is Read Only." This can more easily be done, in some cases, from a DOS prompt (using the DOS rename command).

The procedures below were created using Access 97, however, the process is the same for all versions.

Select option to store data in a new table 4, then click on the "Next" or "Finish" button

Highlight updated table and then click on the Design button

Right click on field LOGRECNO and then select "Primary Key"

Click on save and then exit design view

Technical note for users of Access 2000 (or a later version): Make sure that the "First Row Contains Field Names" box is not checked during the import text process. Otherwise, the first record in the text data file will not be imported and the starting value of the LOGRECNO field will be 0000002. If this box is checked, uncheck it once. If you click this box more than once with the mouse, the change may not take and you will have to repeat the import text process.
What to do about import errors?

If you are unable to import the entire contents of a text data file in Access 2000, an error message will appear and an import error table will be created. This table will contain a record for each field and the record number that fails to properly import. Sort the import error table on the column containing the field names. Next, jot down the table numbers containing data items with errors. If P002004 appears, for example, you will want to change the field type for all data items whose names start with "P002" (this includes all data items in table P2).

Start the import process again and select the same text file and matching import specification. You will then see a list of fields in the bottom part of the window. Change field types from that screen for fields that didn't import correctly. This usually involves just changing the field type from Long Integer to Double. Next click on "Save As" to overwrite current version of the import specification. Select "in a new table" from the next screen. The new table will be created according to the new version of the import specification.

Download SAS programs [ZIP, 96 KB] or contact your local state data center (SDC) for an alternate version of the SAS ™ code. SPSS ™ code may also be available from your local SDC. The SAS programs convert the ASCII text files to SAS data sets. Light modifications such as changing the input and output file names and the directories used to store data may need to be made.

SF1GEO.SAS - Converts the Geo ID file.

SF1xx.SAS - Converts the matching data file (by number) and merges this with the SAS data set created by SF1geo.sas. There are thirty-nine of these SAS programs numbered from SF101.sas to SF139.sas.

If you get runtime errors ...

Some descriptions (enclosed within double quotes) in the LABEL statement of the SAS programs may contain HTML tags (these are enclosed by < and > characters). These tags can be removed. Also, should the length of a description exceed 256 characters, it will need to be shortened. The part of the description cut off in these rare cases can be placed in an additional comment line.

Remove the last character at the bottom of a program (after the quit; statement). It should appear as a hollow square in most editors.

This section assumes familiarity with operations in database management programs such as opening a data table and appending records to it as well as setting up a relationship between two data tables based on a common field.

1 The value of LOGRECNO has no other signifigance. There is a one to one correspondence
based on LOGRECNO between the Geo ID file and data files 01 - 11 and 37 - 39 only. Data files 12 - 36 are entirely made up of PCT
tables (population tables down to the whole census tract level).

2 Chapter 4 of the file documentation contains a code list for SUMLEV (summary level)
and a list of available geographic component codes for each summary level or area type (see the Footnote Section of Chapter 7 for
a code list for GEOCOMP). Chapter 4 includes separate charts for the state files and the national file.

Chapter 7 includes the record layout of the Geo ID file and a complete list of demographic data tables and data items. See
Appendix A for definitions of geographic terms. It is recommended that GIS users also see notes on using
boundary files.

3 The ASCII text data files will need to be saved if link is used. The database will contain
links to these external ASCII text data files which enable them to be processed and used like internal Access database tables.

4 If you are importing the geographic identifier file, select the last import specification
in the list that
comes up immediately after clicking the Specs button.

6 It is not necessary to click the Join Type button. The default join type will include
records for
values of LOGRECNO that appear in both linked files.

7 Setting SUMLEV="050" and GEOCOMP="00" selects county total records.

[PDF] or denotes a file in Adobe’s Portable Document Format. To view the file, you will need the Adobe® Reader® available free from Adobe.
This symbol indicates a link to a non-government web site. Our linking to these sites does not constitute an endorsement of any products, services or the information found on them. Once you link to another site you are subject to the policies of the new site.