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

Data from the summary file can be accessed using Data Ferrett, a software application developed by the Census Bureau for disseminating large amounts of data. Users who have accessed the Public Use Microdata Sample (PUMS) files are likely already familiar with Data Ferrett. As with the Summary File Excel Retrieval Tool, the Data Ferrett approach is similar to American Factfinder. Users only need to know what detailed table they want; no reference to file organization of the ACS Summary File is required (e.g., "sequence number"). This is an ideal application if you would like to download a large number of geographic areas for a small number of tables at one time-for example, the age by sex table for all tracts in the country. There are some limitations to the Data Ferrett application. There is a maximum of 67,000 geographic areas with up to 250 table lines in the extract. There can be no more than about 500 table lines in any single extract, and no more than 4 detailed tables in a single extract. Also, for the initial release of the application, only some of the summary levels are available. Please see the Data Ferrett documentation for the specific summary levels. For a tutorial on how to use and access this application to retrieve Summary File data, please visit dataferrett.census.gov/acs/acs2005-2009sf_readme.html. Documentation of the important features and limitations of the initial release of the 2005-2009 ACS 5-Year Summary File in Data Ferrett can also be found there. For general information about the Data Ferrett tool, consult their home page at dataferrett.census.gov/index.html.

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

1) Download estimates and margins of error zipped files from the browser Internet Explorer and unzip to a local directory.
2) Download Excel geography files to the same local directory. Geography files are in http://www2.census.gov/acs2009_5yr/summaryfile/UserTools/Geography. For example for California, download ca.xls, and for summary levels that can cross state boundaries, download us.xls. Note that US geographies (us.xls) and US zipped estimates and margins of error files are for those summary levels that can cross state boundaries. They are not the data at the national summary level.
3) Download the Summary File Excel Import Tool from http://www2.census.gov/acs2009_5yr/ summaryfile/UserTools/2005-2009_SummaryFileXLS.zip. Unzip the file to the same local directory.
4) Follow the instructions in Section 3.3 to read in the data by the sequence. For example, here is the screenshot for sequence 1 (the screenshots are for illustration purposes only and may not reflect the current 5-year data):

5) Next, pad 0s for logical record number LOGRECNO.

a. Add a column next to LOGRECNO. To do this, Highlight column G, then Right Click and click Insert.



b. For cells G1 and G2, put in LOGRECNO as the label.



c. Highlight cell G3 and enter the formula =REPT("0",7-LEN(F3))&F3, then hit Enter.



d. Copy and paste the formula in cell G3 down column G to the last row of the data.




6) 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 geography ca.xls file and keep both Seq1.xls and ca.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,[ca.xls]Sheet1!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,[ca.xls]Sheet1!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.