For 2005-2009 ACS 5-year data, there are 295 Detailed Tables and all block group data not available on AFF. 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 www.census.gov/acs/www/data_documentation/summary_file.

Hardware and Software requirements for the application include:

• Microsoft Windows XP Professional Service Pack 3 or newer (a Mac version is being developed)
• Winzip version 14.5 or compatible
• 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 www2.census.gov/acs2009_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. SF20095YR_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.
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.

This tool requires that users first download the Summary File data and Excel Templates SummaryFileXLS.zip from www2.census.gov/acs2009_5yr/summaryfile/UserTools/. The zip file is a compressed file that contains individual Excel files and one geography file per Summary File Sequence. This tool is designed to be compatible with any version of Excel. Following are instructions on how to use this tool.

Open the appropriate Excel template for the Sequence ID you would like to import into Excel. The file will default to open on sheet "E". Both sheets "E" and "M" have the same information; one sheet is for "Estimates" and one sheet for "Margins of Error". When the file is open in Excel it should appear as below:



Place your cursor in cell A3 and click on the Data tab in the Excel tool bar. See below:



To import the Summary File text file into Excel click on From Text in the Get External Data section of the tool bar.



Step 1 of the Text Import Wizard will appear. Under Original data type choose Delimited, and then click Next.



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.


A Pop up window will appear to confirm cell A3 as the correct cell. Click OK.



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



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