Author: John Blodgett, OSEDA/MCDC
Summary: In this example we demonstrate how to access the detailed Base tables data (these are the tables based upon the "summary files") from the American Community Survey. The example is based upon a real-life application that we did in 2010 for a public agency in Missouri with only very slight variations (to keep things relatively simple). Here is the problem statement/user request: We are interested in knowing approximately how many children in the state of Missouri are eligible for the WIC (Women, Infants and Children) program. To be eligible the child must be 5 years old or younger and have a poverty ratio of less than 1.85 (the same income criteria as used for the Free and Reduced Lunch program). We would love to have these data by county but we understand that if we want more current data from the ACS that we cannot get it for the many smaller counties in the state. We are therefore willing to take the data summarized at the state and PUMA levels. We understand that there is very current ACS data at the PUMA level released each year, and we are familiar with the strong relationship between counties and PUMAs in the state (see report link in Inputs: section, just below).
Data Set Accessed: acs2009.basetbls.ustabs17_20
Inputs: Dexter Query Form (live and modifiable!) | Postprocessing SAS program | MCDC report relating PUMAs and counties (for the entire US - use index to access Missouri part)
Outputs: Dexter Summary log | csv (comma-delimited) | html report | SAS data set | Postprocessing report (using SAS)
What You Need to Know (Broad, Common Knowledge)
You need to know the basics about the American Community Survey, which is no trivial thing for a total newbie. You also need to have some idea of what poverty ratios are about. As a practical matter, anyone who would propose the problem would need to have such familiarity. But others might want to do some reading in the Bureau's 2009 ACS Subject Definitions document starting on page 100 (related to Poverty Status).
A Brief Summary of What We Are Going to Do
Unlike earlier xsample modules in the 2010 series we are not going to take you through the detailed steps that we followed in order to extract the requested information using the Uexplore and Dexter access tools. At least not in the same way. We are replacing html text with video moduled that we hope will deliver the same information but hopefully in a somewhat easier to digest format. Here is a bare-bones outline of what you can expect to see in the videos: