Documentation: ACS 2008 -- 2010 (3-Year Estimates)
you are here: choose a survey survey document chapter
Publisher: U.S. Census Bureau
Document: ACS 2010 3-Year Summary File: Technical Documentation
citation:
Social Explorer; U.S. Census Bureau; American Community Survey 2008-2010 Summary File: Technical Documentation.
ACS 2010 3-Year Summary File: Technical Documentation
Appendix D. Examples
D.1 Creating a Table
The following 5-year Summary File example is also applicable to the 1-year Summary File. Let's say that you want to create Table B08406, "Sex of Workers by Means of Transportation to Work for Workplace Geography," for the state of Alaska. Which files do you need? How do you read the files?
You will need three files:

  1. The Sequence_Number_and_Table_Number_Lookup.xls spreadsheet
  2. The zipped file 20095ak0003000.zip and the data file e20095ak0003000.txt
  3. The geography file (g20095ak.txt)
Start with the Sequence_Number_and_Table_Number_Lookup.xls spreadsheet. Under the "Tblid" column, look for the value "B08406". You will see that the "Sequence Number" is "0003." This means that the estimates you are looking for are in the data file "e20095ak0003000.txt". How do you know this is the right file? You know this from the name of the file: the "e" stands for estimate, "2009" is the year, "5" means that these are 5-year estimates, "ak" is the state (Alaska), and "0003" is the sequence number (which contains the data for Table B08406). Likewise, you need the "m20095ak0003000.txt" file for the margins of error. These two files will be found in a zipped file specifying that it is all geographies that are "not track (or) block groups". That is because we want the estimates and margins of error for the state level, Alaska. See Chapter 2.5 for more information.
When you open the estimate file, e20095ak0003000.txt, you will see the following comma- delimited fields on the first line:

ACSSF,2009e5,ak,000,0003,0000001,333471,266201,220762,45439,36132,5602,3705,...

The first six fields - from "ACSSF" to "0000001" - are identifiers:
  1. The first field tells you that this is an ACS Summary File;
  2. The second tells you that these data are five-year estimates for the year 2005-2009 (notice the "e" before "2009" and the "5" at the end);
  3. The third tells you the state, ie. "ak" is Alaska;
  4. The fourth is an iteration number;
  5. The fifth is the sequence number,
  6. The last is a logical record code, LOGRECNO. The LOGRECNO identifies the geographic area within a state.
These identifiers begin each new line in the estimate file, and the same holds true for the margin of error files. You can compare these identifiers with those in the respective margin of error file, m20095ak0003000.txt.

Then use the geography file for Alaska to determine the location within the state to which the data refer. The appropriate file is g20095ak.txt, where "g" stands for geography, "2009" is the year, "5" is the period estimate (in this case, 5-year estimate), and "ak" is the state. The geography file, g20095ak.txt, defines the LOGRECNO. Each LOGRECNO in this file specifies a geographic area pertaining to the state. For example, a LOGRECNO of "0000001" means the state of Alaska; a LOGRECNO of "0000002" means just the urban areas in Alaska; a LOGRECNO of "0000003" refers to just rural areas in Alaska. (Each state geography file also contains the lower-case FIPS State Code.) Please be aware that each state has its own geography file. For more information, see Chapter 2.4.

Glancing back at the Sequence Number and Table Number Lookup file, you will see that Table 08406 in sequence "0003" begins at the seventh position. From this point forward, for 51 fields (indicated on the same file), each field corresponds to the value of a "line number" in the table. So, field number seven, the 333471 value, corresponds to line number one, which is "Total". Field number eight, the 266201 value, refers to line number two, which is "Car, Truck, or Van." Field number nine, the 220762 value, corresponds to line number three, which is "Drove alone." This continues all the way up to line number 51, at which point Table B08406 ends.

Were you to read all these files into a computer program using software such as SAS, you could translate the first nine fields of e20095ak0003000.txt as follows:

Table B08406: Sex of Workers By Means of Transportation to Work for Workplace Geography

FILEID FILE TYPE STUSAB CHARITER SEQUENCE LOGRECNO Total Car, Truck, or Van Drove Alone
ACSSF 2009e5 ak 0 3 1 333471 266201 220762
ACSSF 2009e5 ak 0 3 13 2440 340 252
ACSSF 2009e5 ak 0 3 14 5266 1046 620
ACSSF 2009e5 ak 0 3 15 151263 135626 114936
ACSSF 2009e5 ak 0 3 16 6421 2455 1586

Merging the geography file, the table shell, and the estimate and margin of error files together creates an excerpt of Table B08406, shown below:

Table ID Line Number Sequence Number Table Title Estimates Margin of Error
B08406   3 SEX OF WORKERS BY MEANS OF TRANSPORTATION TO WORK FOR WORKPLACE GEOGRAPHY    
B08406   3 Universe: Workers 16 years and over    
B08406 1 3 Total: 333,471 +/-2,630
B08406 2 3 Car, truck, or van: 266,201 +/-2,589
B08406 3 3 Drove alone 220,762 +/-2,439
B08406 4 3 Carpooled: 45,439 +/-1,795
B08406 5 3 In 2-person carpool 36,132 +/-1,596


D.2 Creating a Table Using SAS
Here is an example of how to access the Summary Files for one table for all geographies from the ACS summary file. The following 5-year Summary File example is also applicable to the 1- year Summary File.

Question: I am interested in downloading table B01001 for all published ACS geographies, how would I do this?
  1. Go to the Sequence Number and Detailed Table Number Lookup File (Chapter 2.3) to locate sequence number for table B01001. There are Excel and SAS dataset versions of the file. They are available at www2.census.gov/acs2009_5yr/summaryfile

  2. Use the Summary File SAS Example Macros located at www2.census.gov/acs2009_5yr/summaryfile/UserTools/www2.census.gov/acs2009_5yr/summaryfile/UserTools/. Run the macro:
    % TableShell(B01001);
    This macro will provide metadata information on a given table, in this case B01001.
  3. The following SAS dataset will be created with information about table B01001:

    Table ID Sequence Number Line Number Start Position Total Cells in Table Total Cells in Sequence Table Title
    B01001 0013   7 49 CELLS   SEX BY AGE
    B01001 0013         Universe: Total population
    B01001 0013 1       Total:
    B01001 0013 2       Male:
    B01001 0013 3       Under 5 years
                 
    B01001 0013 47       75 to 79 years
    B01001 0013 48       80 to 84 years
    B01001 0013 49       85 years and over


    We can see that table B01001 is located in Sequence 0013; this applies to all published geographies.
  4. We can read into SAS all tables in the 0013 sequence by running the SAS Example Macros:
    %CallSt;
    This macro will run a do loop creating State two-digit abbreviations, which will allow a simple way to read the Summary Files into SAS for all geographies. Each time a valid two digit state abbreviation is created, the macro %AllSeqs is run with the two digit state abbreviation.
    The %Aiiseqs Macro performs the following tasks:
    A.Read the geographic header file - %AnyGeo Macro.
    B.There is a do loop to allow you to choose which sequences you would like to read in for example if you wanted sequence 0010 set the loop to be do x=i3 %to 13; - The 0 values will be filled in.
    C.Within the do loop the following macros will be executed:
    a.%TablesBySeq; -- This will give information about the whole 0003 sequence, not just table B01001.
    b.%ReadDataFiie - This macro is called two times once for each type of estimate. This macro will generate and run SAS code for each sequence specified in the do loop in step 2 and for each geography specified in the %Callst macro.
    c.Lastly, there is a merge statement that will merge together each of the three types of estimates and the geography header file by sequence number per geography.
  5. You now will have all tables in the 0013 sequence read into SAS in the following dataset names, if the code is not modified, in the work directory Sf0013.sas7bdat.