Documentation: | ACS 2009 (1-Year Estimates) |
you are here:
choose a survey
survey
document
chapter
Publisher: U.S. Census Bureau
Survey: ACS 2009 (1-Year Estimates)
Document: | ACS 2009-1yr Summary File: Technical Documentation |
citation: | Social Explorer; U.S. Census Bureau; American Community Survey 2009 Summary File: Technical Documentation. |
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 from the files on the ftp site http://www2.census.gov/acs2009_1yr/summaryfile/Seq_By_ST. Which files do you need? How do you read the files? You will need three files:
1. The data dictionary (merge_5_6.sas7bdat)
2. The zip file/data file /acs2009_1yr/summaryfile/Seq_By_ST//.zip (20091ak0003000.zip - e2009ak0003000.txt)
3. The geography file (g20091ak.txt)
Start with the data dictionary, merge_5_6.sas7bdat. Under the "Tblid" column, look for the value "B08406." You will see that the "Sequence Number" is "0003." This means that the data you looking for are in the data file "e2008ak0003000.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, "ak" is the state (Alaska), and "0001" is the sequence number (which contains the data for Table B08406). See the "File Naming Conventions" section in Chapter 2.
Then use the geography file for Alaska to determine the location within the state to which the data refer. The appropriate file is g20091ak.txt, where "g" means "geography", 2009 is the year, 1 is the period estimate (in this case, 1-year estimate), and "ak" is the state. (For each state, the geography file contains the lower-case FIPS State Code.)
When you open the data file, e20091ak0003000.txt, you will see the following comma-delimited fields on the first line:
ACSSF,2009e1,ak,000,0003,0000001,342551,275208,230130,45078,35960...
The first six fields - from 'ACSSF' to '0000001' - are identifiers.
A. The first field tells you that this is an ACS Summary File;
B. The second tells you that these data are one-year estimates for the year 2009 (notice the e before 2009 and the 1 at the end);
C. The third tells you the state (ak is Alaska);
D. The fourth is an iteration number;
E. The fifth is the sequence number,
F. The last is a logical record code (LOGRECNO). The LOGRECNO identifies the geographic area within a state.
The geography file, g20091ak.txt, defines the LOGRECNO. Each LOGRECNO 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. Notice that each state has its own geography file. For more information, see Chapter 2, Table 2.1.
The other fields in the data file, from the seventh on, are data values. Each field corresponds to the value of the 'line number' variable in the data dictionary. So field number seven (the 342551 value, after the sixth comma) corresponds to line number one, which is "Total." Field number eight (the 275208 value, after the seventh comma) refers to line number two, which is "Car, Truck, or Van." Field number nine (the 230130 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 this into a computer program using software such as SAS, you could translate the first nine fields of e20091ak0003000.txt as follows:
See formatted example of Table B08406 below:
1. The data dictionary (merge_5_6.sas7bdat)
2. The zip file/data file /acs2009_1yr/summaryfile/Seq_By_ST/
3. The geography file (g20091ak.txt)
Start with the data dictionary, merge_5_6.sas7bdat. Under the "Tblid" column, look for the value "B08406." You will see that the "Sequence Number" is "0003." This means that the data you looking for are in the data file "e2008ak0003000.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, "ak" is the state (Alaska), and "0001" is the sequence number (which contains the data for Table B08406). See the "File Naming Conventions" section in Chapter 2.
Then use the geography file for Alaska to determine the location within the state to which the data refer. The appropriate file is g20091ak.txt, where "g" means "geography", 2009 is the year, 1 is the period estimate (in this case, 1-year estimate), and "ak" is the state. (For each state, the geography file contains the lower-case FIPS State Code.)
When you open the data file, e20091ak0003000.txt, you will see the following comma-delimited fields on the first line:
ACSSF,2009e1,ak,000,0003,0000001,342551,275208,230130,45078,35960...
The first six fields - from 'ACSSF' to '0000001' - are identifiers.
A. The first field tells you that this is an ACS Summary File;
B. The second tells you that these data are one-year estimates for the year 2009 (notice the e before 2009 and the 1 at the end);
C. The third tells you the state (ak is Alaska);
D. The fourth is an iteration number;
E. The fifth is the sequence number,
F. The last is a logical record code (LOGRECNO). The LOGRECNO identifies the geographic area within a state.
The geography file, g20091ak.txt, defines the LOGRECNO. Each LOGRECNO 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. Notice that each state has its own geography file. For more information, see Chapter 2, Table 2.1.
The other fields in the data file, from the seventh on, are data values. Each field corresponds to the value of the 'line number' variable in the data dictionary. So field number seven (the 342551 value, after the sixth comma) corresponds to line number one, which is "Total." Field number eight (the 275208 value, after the seventh comma) refers to line number two, which is "Car, Truck, or Van." Field number nine (the 230130 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 this into a computer program using software such as SAS, you could translate the first nine fields of e20091ak0003000.txt as follows:
FILEID | FILE TYPE | STUSAB | CHARITER | SEQUENCE | LOGRECNO | Total | Car, Truck, or Van | Drove Alone |
---|---|---|---|---|---|---|---|---|
ACSSF | 2009e1 | ak | 000 | 0003 | 0000001 | 342551 | 275208 | 230130 |
ACSSF | 2009e1 | ak | 000 | 0003 | 0000010 | 157650 | 141673 | 120945 |
ACSSF | 2009e1 | ak | 000 | 0003 | 0000011 | . | . | . |
ACSSF | 2009e1 | ak | 000 | 0003 | 0000012 | . | . | . |
ACSSF | 2009e1 | ak | 000 | 0003 | 0000013 | 157650 | 141673 | 120945 |
ACSSF | 2009e1 | ak | 000 | 0003 | 0000017 | 157650 | 141673 | 120945 |
See formatted example of Table B08406 below:
Table ID | Line Number | Sequence Number | Table Title | Estimates | Margin of Error |
---|---|---|---|---|---|
B08406 | 003 | SEX OF WORKERS BY MEANS OF TRANSPORTATION TO WORK FOR WORKPLACE GEOGRAPHY | |||
B08406 | 003 | Universe: Workers 16 years and over | |||
B08406 | 1 | 003 | Total: | 342551 | +/-6,394 |
B08406 | 2 | 003 | Car, truck, or van: | 275208 | +/-6,304 |
B08406 | 3 | 003 | Drove alone | 230130 | +/-6,492 |
B08406 | 4 | 003 | Carpooled: | 45078 | +/-3,523 |
B08406 | 5 | 003 | In 2-person carpool | 35960 | +/-3,399 |
B08406 | 6 | 003 | In 3-person carpool | 5656 | +/-1,243 |
B08406 | 7 | 003 | In 4-or-more-person carpool | 3462 | +/-976 |
Here is an example of how to access the Summary Files for one table for all geographies from the ACS summary file.
Question: I am interested in downloading table B01001 for all published ACS geographies, how would I do this?
1. Go to Chapter 5 to locate sequence number for table B01001.
There are files that summarizes this information at:
http://www2.census.gov/acs2009_1yr/summaryfile/merge_5_6.xls
http://www2.census.gov/acs2009_1yr/summaryfile/merge_5_6.sas7bdat
2. Use the Summary File SAS Example Macros located at http://www2.census.gov/acs2009_1yr/summaryfile/UserTools/summary_file_example_macros.sas
Run the macro for %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
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 ran with the two digit state abbreviation. %AllSeqs
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=13 %to 13; - The 0 values will be filled in.
c) Within the do loop the following marcros will be executed:
I. %TablesBySeq; - This will give information about the whole 10 sequence, not just table B01001.
II. %ReadDataFile - Macro is called 2 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.
III. 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
Question: I am interested in downloading table B01001 for all published ACS geographies, how would I do this?
1. Go to Chapter 5 to locate sequence number for table B01001.
There are files that summarizes this information at:
http://www2.census.gov/acs2009_1yr/summaryfile/merge_5_6.xls
http://www2.census.gov/acs2009_1yr/summaryfile/merge_5_6.sas7bdat
2. Use the Summary File SAS Example Macros located at http://www2.census.gov/acs2009_1yr/summaryfile/UserTools/summary_file_example_macros.sas
Run the macro for %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 |
---|---|---|---|---|---|---|
01001 | 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 | |||
B25092 | MEDIAN SELECTED MONTHLY OWNER COSTS AS A PERCENTAGE OF HOUSEHOLD INCOME IN THE PAST 12 MONTHS | MEDIAN | Tenths |
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 ran with the two digit state abbreviation. %AllSeqs
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=13 %to 13; - The 0 values will be filled in.
c) Within the do loop the following marcros will be executed:
I. %TablesBySeq; - This will give information about the whole 10 sequence, not just table B01001.
II. %ReadDataFile - Macro is called 2 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.
III. 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