TechTalk

Census 2000 Summary File 1 ASCII text data files

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

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 ¹) 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 ² 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.

---

[Back to Top]Deciding which data files to use

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.

---

[Back to Top]Using Microsoft Access ^™

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.

Importing text data files into Microsoft Access ^™ (click camera to view screenshots)

1. Click "File", "Get External Data", then "Import" or "Link" from menu ³
2. Select text file and click on the "Import" button
3. Click on the "Advanced" button
4. Click on the "Specs" button
5. Select matching import specification and click on the "Open" button
6. Click on the "OK" button
7. Select option to store data in a new table ⁴, then click on the "Next" or "Finish" button
8. Highlight updated table and then click on the Design button
9. Right click on field LOGRECNO and then select "Primary Key"
10. 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.

Linking tables in Microsoft Access ^{™ 5} (click camera to view snapshots)

1. Click "Tools" then "Relationships" from menu or click on the "Relationships" button
2. Select a table to be linked and then click on the "Add" button. Repeat for each table and then click on "Close" when finished
3. Drag the field LOGRECNO from one table on top of LOGRECNO in another table to link both tables Note that each table must already be indexed on a
4. common key field (LOGRECNO in this case).
5. Click on the "Create" button ⁶
6. Click "File" "Save" and then "File", "Close" from menu when finished.

Querying in Microsoft Access ^™ (click camera to view snapshots)

1. Click on the "Query" tab, then click on the "New" button
2. Select query type (Simple Query is used in this example)
3. Select a table to be queried and one or more fields from it and repeat for each table
4. Select detail or summary data
5. Select modify query design and then click on the "Finish" button
6. Edit the contents of the Criteria box ⁷ for each field to be queried
7. Click "Query" then "Run" from menu (in query design view)

Saving Results of Query to a Table (click camera to view snapshots)

1. Click "Query" then "Make Table Query" from menu (in query design view)
2. Enter name for table and then click on the "OK" button.
3. Re-run query by clicking "Query" then "Run" from menu (in query design view).

Exporting Query Stored as a Table

1. Click the tables tab.
2. Right click on table
3. Select "Save As", then "Export"

---

[Back to Top]Using SAS ^™

1. 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.
2. SF1GEO.SAS - Converts the Geo ID file.
3. 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.

Modifying SAS programs to bring in records based on a condition

DATA census.cageo;

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;

---

[Back to Top]Using other database programs

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.

FoxPro format layouts [ZIP, 105 KB]
Oracle (Google search)
SPSS (Google search)

---

[Back to Top]Footnotes

¹ 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).

² 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.

³ 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.

⁴ 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.

⁵ 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

⁶ 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.

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