| Documentation: | ACS 2011 (5-Year Estimates) |
you are here:
choose a survey
survey
document
chapter
Publisher: U.S. Census Bureau
Survey: ACS 2011 (5-Year Estimates)
| Document: | The 2007-2011 ACS 5-Year Summary File Technical Documentation |
| citation: | Social Explorer; U.S. Census Bureau; American Community Survey 2007-2011 Summary File: Technical Documentation. |
Chapter Contents
If only a few tables are needed, a useful tool is the Summary File Excel Retrieval Tool. If users have Excel 2007 or later, the tool is able to get tables from an entire sequence and from a summary level and component. Information on how to use Summary File Excel Retrieval Tool is located on the ACS Summary File page, at http://www.census.gov/acs/www/data_documentation/summary_file/
Hardware and Software requirements for the application include:
Hardware and Software requirements for the application include:
- Microsoft Windows XP Professional Service Pack 3 or newer
- WinZip version 14.5 or compatible
- 32 bit Office 2007 or newer
- DSL connection at 1.0 Mbps or greater is recommended
For users with SAS software, please visit the User Tools folder, http://www2.census.gov/acs2011_5yr/summaryfile/UserTools/ to download SAS programs. These programs allow users to load the Summary File data into SAS Datasets. The SAS programs serve as a reference for the users, and include variable labels, however modifications may be necessary before users can get the data. After the needed Summary File data have been downloaded, users should specify the locations in the library references and the infile statements.
1)SF20115YR_SAS.zip - Contains SAS programs, which can be used to convert each estimate and margin of error into SAS Datasets. The SAS datasets include table stubs. There is one individual SAS program for each sequence per state.
2)SF_All_Macro.sas - This is a detailed example SAS program containing SAS macros which access the ACS Summary File geography, estimate and margin of error data. It creates one table for all geographies from the ACS Summary File. Segments of the SAS codes can be used to convert geography files into SAS datasets.
1)SF20115YR_SAS.zip - Contains SAS programs, which can be used to convert each estimate and margin of error into SAS Datasets. The SAS datasets include table stubs. There is one individual SAS program for each sequence per state.
2)SF_All_Macro.sas - This is a detailed example SAS program containing SAS macros which access the ACS Summary File geography, estimate and margin of error data. It creates one table for all geographies from the ACS Summary File. Segments of the SAS codes can be used to convert geography files into SAS datasets.
This tool is another way data users can read the Summary File into Excel, and it is designed to be compatible with any version of the software. While this example uses the 2007-2009 ACS 3- year Summary File, the file layout is similar for the 2007-2011 ACS 5-year Summary File. To begin using this tool, users must first download the Summary File data, the Excel template file, and the Excel geography file.
The 2007-2009 ACS 3-year Summary File is available at http://www2.census.gov/acs2009_3yr/summaryfile/. In this example, we are accessing the data by selecting "2007-2009 ACSSF By State All Tables," then selecting "California All Geographies.zip." Once downloaded and unzipped to a local directory, this compressed file contains individual text files and one geography file per Summary File sequence. We are using Sequence file 1, so you need to open and save the corresponding estimate and margin of error text files (e20093ca0001000.txt and m20093ca0001000.txt).
Next, you need to download the Excel template file, SummaryFileXLS.zip, from http://www2.census.gov/acs2009_3yr/summaryfile/UserTools/. (Note: SummaryFileXLS.zip is named SummaryFileTemplates.zip for 2009-2011.) Once unzipped to a local directory, this compressed file contains an Excel file for each Summary File sequence. Again, we are using Sequence File 1, so you need to open Seq1.xls.
Finally, you need to download and save the Excel geography file, mini_geofile.xls, from http://www2.census.gov/acs2009_3yr/summaryfile/UserTools/Geography/ to the same local directory. Each worksheet in the mini_geofile.xls represents geographies that are within a state. Note that the "US" worksheet only contains summary levels that can cross state boundaries. "US" is not the data at the national summary level.
We will walk through an example using sequence 1 for the state of California:
1) When the template file is open in Excel it should appear as below:
2) Place your cursor in cell A3 and click on the Data tab in the Excel tool bar. See below:
3) To import the Summary File text file into Excel click on From Text in the Get External Data section of the tool bar. In this example, we are opening the estimate file for California (e20093ca0001000.txt).
4) Step 1 of the Text Import Wizard will appear. Under Original data type choose Delimited, and then click Next.
5) Step 2 of the Excel Text Import Wizard will appear. Under Delimiters choose Comma. Users may click Finish to import the file or choose Next to format the Excel columns.
6) A Pop up window will appear to confirm cell A3 as the correct cell. Click OK.
7) The summary file will be imported in Excel as below:
Row 1 - Contains a unique identifier of Table ID and Line Number with a "_" between them
Row 2 - Contains the associated metadata for each unique Identifier Row 3 - Is the first Row of the imported data
Row 3 - Is the first Row of the imported data
Column A - Is a constant value of "ACSSF" (stands for ACS Summary File)
Column B - Contains the associated metadata for each unique Identifier
Column C - Is the first Row of the imported data
8) Read in the estimates and margins of error for each sequence needed. For example, here is the screenshot of the estimates for sequence 1 (the screenshots are for illustration purposes only and may not reflect the current data):
9) Next, you need to pad zeroes for the logical record number LOGRECNO.Add a column next to LOGRECNO. To do this, Highlight column G, then Right Click and click Insert.
a. For cells G1 and G2, put in LOGRECNO as the label.
b. Highlight cell G3 and enter the formula =REPT("0",7-LEN(F3))&F3, then hit Enter.
c. Copy and paste the formula in cell G3 down column G to the last row of the data.
10) Add geographies by using common merged keys.
a. To add geographies, insert two extra columns next to the padded LOGRECNO column G, and label them GEOID and Geography Name. Open the Excel geography file mini_geofile.xls and keep both Seq1.xls and mini_geofile.xls files open.
b. Add GEOID by using LOGRECNO as the common merged key from both Seq1.xls and ca.xls. Highlight cell H3 and enter the formula: =VLOOKUP(G3,[mini_geofile.xls]ca!B:C,2,0)
c. Add geography names by using GEOID as the common merged key from both Seq1.xls and ca.xls. Highlight cell I3 and enter the formula: =VLOOKUP(H3,[mini_geofile.xls]ca!C:D,2,0)
d. Highlight both cells H3 and I3 and right click to copy the formulas.
e. Copy and paste the formulas down the H3 and I3 columns to the last row of the data.
10) Repeat steps 1 through 10 to obtain the margin of errors for the same sequence for California.
The 2007-2009 ACS 3-year Summary File is available at http://www2.census.gov/acs2009_3yr/summaryfile/. In this example, we are accessing the data by selecting "2007-2009 ACSSF By State All Tables," then selecting "California All Geographies.zip." Once downloaded and unzipped to a local directory, this compressed file contains individual text files and one geography file per Summary File sequence. We are using Sequence file 1, so you need to open and save the corresponding estimate and margin of error text files (e20093ca0001000.txt and m20093ca0001000.txt).
Next, you need to download the Excel template file, SummaryFileXLS.zip, from http://www2.census.gov/acs2009_3yr/summaryfile/UserTools/. (Note: SummaryFileXLS.zip is named SummaryFileTemplates.zip for 2009-2011.) Once unzipped to a local directory, this compressed file contains an Excel file for each Summary File sequence. Again, we are using Sequence File 1, so you need to open Seq1.xls.
Finally, you need to download and save the Excel geography file, mini_geofile.xls, from http://www2.census.gov/acs2009_3yr/summaryfile/UserTools/Geography/ to the same local directory. Each worksheet in the mini_geofile.xls represents geographies that are within a state. Note that the "US" worksheet only contains summary levels that can cross state boundaries. "US" is not the data at the national summary level.
We will walk through an example using sequence 1 for the state of California:
1) When the template file is open in Excel it should appear as below:
2) Place your cursor in cell A3 and click on the Data tab in the Excel tool bar. See below:
3) To import the Summary File text file into Excel click on From Text in the Get External Data section of the tool bar. In this example, we are opening the estimate file for California (e20093ca0001000.txt).
4) Step 1 of the Text Import Wizard will appear. Under Original data type choose Delimited, and then click Next.
5) Step 2 of the Excel Text Import Wizard will appear. Under Delimiters choose Comma. Users may click Finish to import the file or choose Next to format the Excel columns.
6) A Pop up window will appear to confirm cell A3 as the correct cell. Click OK.
7) The summary file will be imported in Excel as below:
Row 1 - Contains a unique identifier of Table ID and Line Number with a "_" between them
Row 2 - Contains the associated metadata for each unique Identifier Row 3 - Is the first Row of the imported data
Row 3 - Is the first Row of the imported data
Column A - Is a constant value of "ACSSF" (stands for ACS Summary File)
Column B - Contains the associated metadata for each unique Identifier
Column C - Is the first Row of the imported data
8) Read in the estimates and margins of error for each sequence needed. For example, here is the screenshot of the estimates for sequence 1 (the screenshots are for illustration purposes only and may not reflect the current data):
9) Next, you need to pad zeroes for the logical record number LOGRECNO.Add a column next to LOGRECNO. To do this, Highlight column G, then Right Click and click Insert.
a. For cells G1 and G2, put in LOGRECNO as the label.
b. Highlight cell G3 and enter the formula =REPT("0",7-LEN(F3))&F3, then hit Enter.
c. Copy and paste the formula in cell G3 down column G to the last row of the data.
10) Add geographies by using common merged keys.
a. To add geographies, insert two extra columns next to the padded LOGRECNO column G, and label them GEOID and Geography Name. Open the Excel geography file mini_geofile.xls and keep both Seq1.xls and mini_geofile.xls files open.
b. Add GEOID by using LOGRECNO as the common merged key from both Seq1.xls and ca.xls. Highlight cell H3 and enter the formula: =VLOOKUP(G3,[mini_geofile.xls]ca!B:C,2,0)
c. Add geography names by using GEOID as the common merged key from both Seq1.xls and ca.xls. Highlight cell I3 and enter the formula: =VLOOKUP(H3,[mini_geofile.xls]ca!C:D,2,0)
d. Highlight both cells H3 and I3 and right click to copy the formulas.
e. Copy and paste the formulas down the H3 and I3 columns to the last row of the data.
10) Repeat steps 1 through 10 to obtain the margin of errors for the same sequence for California.
