Skip Main Navigation Skip To Navigation Content

TechTalk

You are here: Census.govTechTalk › Census 2000 Summary File 1 ASCII text data files
Skip top of page navigation

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 [PDF, 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 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.


[Back to Top]Deciding which data files to use

File documentation [PDF, 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 Camera: 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 3 Camera: Get External Data
  2. Select text file and click on the "Import" button Camera: Import
  3. Click on the "Advanced" button Camera: Advanced
  4. Click on the "Specs" button Camera: Specs
  5. Select matching import specification and click on the "Open" button Camera: Open
  6. Click on the "OK" button Camera: Ok
  7. Select option to store data in a new table 4, then click on the "Next" or "Finish" button Camera: Next
  8. Highlight updated table and then click on the Design button Camera: Design
  9. Right click on field LOGRECNO and then select "Primary Key" Camera: 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. Camera: Uncheck
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 Camera: relationships
  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 Camera: Add
  3. Drag the field LOGRECNO from one table on top of LOGRECNO in another table to link both tables Camera: Link 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 Camera: Create
  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 Camera: New
  2. Select query type (Simple Query is used in this example) Camera: Simple Query
  3. Select a table to be queried and one or more fields from it and repeat for each table Camera: Select table
  4. Select detail or summary data Camera: detail or summery
  5. Select modify query design and then click on the "Finish" button Camera: Finish
  6. Edit the contents of the Criteria box 7 for each field to be queried Camera: Criteria
  7. Click "Query" then "Run" from menu (in query design view) Camera: Run

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) Camera: Make Table Query
  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" Camera: 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 Link to a non-federal Web site (Google search)
SPSS Link to a non-federal Web site (Google search)


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

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.

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

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 PDF denotes a file in Adobe’s Portable Document Format. To view the file, you will need the Adobe® Reader® Off Site available free from Adobe. This symbol Off Site 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.
Source: U.S. Census Bureau | Administrative and Customer Services Division | (301) 763-7710 |  Last Revised: February 13, 2013