American Community Survey 1-Year Summary File Sample SAS : Programs and Excel Templates
The American Community Survey Office has provided sample SAS programs and Excel Templates for each Summary File data file. SAS datasets or Excel files can be generated using the ASCII ACS Summary Files as input to these programs or using the Excel templates. All of these materials are located in a sub-directory named /UserTools.
Main Program to make all the 16,000+ SAS programs to read in each individual sequence - SF_All_Macro.sas
All 16,000+ SAS Programs: SF20091YR_SAS.zip
SF20091YR_SAS.zip - Contains 16,000+ sas programs, one for each summary file, which can be used to convert each estimate and margin of error summary file into a SAS Dataset, which include table stubs.
SF_All_Macro.sas - This is a detailed example SAS program containing SAS macros which accesses the three different type of Summary Files for one table for all geographies from the ACS summary file. It is recommended that you review this document before using this SAS program.
Basic Instructions for Using the Sample SAS Programs
Light modifications need to be made to the SAS programs in the zip file before using them. The SAS programs included in the sascode.zip have variable and value labels. Using SAS, a data user can open any of the programs, edit the libname and infile statements of the program, and modify the input, output file names and the directories being used to store data to reference the summary files after they have been copied onto the user's computer. Data users may also want to delete columns from output SAS data set not being used to reduce the data set size. After these modifications are made, these programs should be able to run using SAS. Users who do not have SAS are free to read these ACSII summary files into any program of their choice. All values in the ASCII summary files are in CSV-format (comma-separated values). Sample SAS Code showing how to combine the contents of the data files with the geographic file:
/* Add a libname statement referencing the directory being used */
/* Sort all four summary file files by LOGRECNO */
Excel Templates: SummaryFileXLS.zip - The Compressed file contains 176 individual XLS files, 1 per each Summary File Sequence. User can import the summary file (text file) into Excel to make the summary file more user friendly.
Importing Summary Files into Excel Template (Excel 2007)
1) 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 Sheet 'E' and 'M' have the same information; one sheet is for 'Estimates' and one sheet for 'Margin of Errors'. When the file is open in Excel 2007 it should appear as Figure 1-1 below.
2) Place your cursor in cell 'A3' and click on the 'Data' tab in the Excel tool bar. As shown in Figure 1-2 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.
4) The Step 1 of the Excel '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', you may click 'Finish' to import the file at this or choose 'Next' to format the excel columns to your liking; Example purposes click 'Next'.
A Pop up window will appear for to confirm cell 'A3' as the correct cell. Click 'OK'.
6) The summary file will be imported in Excel.
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