ASCII data set information
Deciding which data files to use
Using Microsoft Access (™)
Using SAS (™)
Using other database programs
Data file directory
File documentation [, 4.86 MB]
Census 2000 Data Products support
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.
File documentation [, 4.86 MB]
Chapter 2 - How to Use This File Figure 2-2 to identify the data files(s) that contain these tables.
Chapter 3 - Subject Locator in the file documentation to identify table numbers of interest.
Chapter 7 - Data Dictionary Table Matrix Section to see a complete list of data items contained in these tables. The complete table including the title, universe, all headings and data items is shown.
The steps above complete the data file selection process.
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.
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.
Importing text data files into Microsoft Access ™ (click camera to view screenshots)
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.
Linking tables in Microsoft Access ™ 5 (click camera to view snapshots)
Querying in Microsoft Access ™ (click camera to view snapshots)
Saving Results of Query to a Table (click camera to view snapshots)
Exporting Query Stored as a Table
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.
Modifying SAS programs to bring in records based on a condition
DATA census.cageo(where=(SUMLEV = "140"));
DATA census.cageo(where=((SUMLEV="050" OR SUMLEV = "140") AND GEOCOMP="00"));
Combining the contents of a data file with the geographic ID file
PROC SORT data=census.cageo; by LOGRECNO; RUN; PROC SORT data=census.ca00001; by LOGRECNO; RUN; DATA census.ca01geo; MERGE census.cageo census.ca00001; by LOGRECNO; RUN;
View contents of combined files
PROC CONTENTS data=census._all_ memtype=all; RUN;
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.
[Back to Top]Footnotes
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.
5 Two examples of linking multiple data files to the geographic file below.
Geographic identifier file->LOGRECNO to Data File 01->LOGRECNO and Data File 01->LOGRECNO to Data File 02->LOGRECNO
Geographic identifier file->LOGRECNO to Data File 01->LOGRECNO and Geographic identifier file->LOGRECNO to Data File 02->LOGRECNO