DOI: 10.7910/dvn/dki9l4
title: American Community Survey (ACS)

description: &lt;h3 class="post-title entry-title" itemprop="name"&gt; analyze the american community survey (acs) with r and monetdb &lt;/h3&gt; experimental. think of the american community survey (acs) as the united states' census for off-years - the ones that don't end in zero. every year, one percent of all americans respond, making it the largest complex sample administered by the u.s. government (the decennial census has a much broader reach, but since it attempts to contact 100% of the population, it's not a sur
vey). the acs asks &lt;a href="http://www.census.gov/acs/www/guidance_for_data_users/guidance_main/"&gt;how people live&lt;/a&gt; and although the &lt;a href="http://www.census.gov/acs/www/methodology/questionnaire_archive/"&gt;questionnaire&lt;/a&gt; only includes about three hundred questions on &lt;a href="http://www.census.gov/acs/www/about_the_survey/american_community_survey/"&gt;demography, income, insurance&lt;/a&gt;, it's often accurate at sub-state geographies and - &lt;a href="http://www.census.gov/acs/www/guidance_for_dat
a_users/estimates/"&gt;depending how many years pooled&lt;/a&gt; - down to small counties. households are the sampling unit, and once a household gets selected for inclusion, all of its residents respond to the survey. this allows household-level data (like home ownership) to be collected more efficiently and lets researchers examine family structure. the census bureau runs and finances this behemoth, of course.&lt;br /&gt; &lt;br /&gt; the &lt;a href="http://www.census.gov/acs/www/data_documentation/data_via_ftp/"&gt;dow
nloadable american community survey&lt;/a&gt; ships as two distinct household-level and person-level comma-separated value (.csv) files. &lt;a href="http://stat.ethz.ch/R-manual/R-patched/library/base/html/merge.html"&gt;merging&lt;/a&gt; the two just &lt;a href="http://cps.ipums.org/cps-action/faq#ques11"&gt;rectangulates&lt;/a&gt; the data, since each person in the person-file has exactly one matching record in the household-file. for analyses of small, smaller, and microscopic geographic areas, choose one-, three-, or fiv
e-year pooled files. use as few pooled years as you can, unless you like sentences that start with, "over the period of 2006 - 2010, the average american ... [insert yer findings here]."
&lt;br /&gt; &lt;br /&gt; rather than processing the acs &lt;a href="http://www.census.gov/acs/www/data_documentation/public_use_microdata_sample/"&gt;public use microdata sample&lt;/a&gt; line-by-line, the r language brazenly reads everything into memory by default. to prevent overloading your computer, &lt;a href="http://www.stat.auckland.ac.nz/showperson?firstname=Thomas&amp;surname=Lumley"&gt;dr. thomas lumley&lt;/a&gt; wrote &lt;a href="http://sqlsurvey.r-forge.r-project.org/"&gt;the sqlsurvey package&lt;/a&gt; principally to deal with t
his ram-gobbling monster. if you're already familiar with syntax used for
&lt;a href="http://faculty.washington.edu/tlumley/survey/"&gt;the survey package&lt;/a&gt;, be patient and read the sqlsurvey examples carefully when something doesn't behave as you expect it to - some sqlsurvey commands require a different structure (i.e. &lt;a href="http://faculty.washington.edu/tlumley/survey/html/svyby.html"&gt;svyby&lt;/a&gt; gets called through &lt;a href="http://faculty.washington.edu/tlumley/survey/html/surveysummary.html"&gt;svymean&lt;/a&gt;) and others might not exist anytime soon (like &lt;a href="http://
faculty.washington.edu/tlumley/survey/html/svyolr.html"&gt;svyolr&lt;/a&gt;). gimme some good news: sqlsurvey uses ultra-fast monetdb (&lt;a href="http://www.asdfree.com/2013/03/column-store-r-or-how-i-learned-to-stop.html"&gt;click here for speed tests&lt;/a&gt;), so follow the &lt;a href="https://github.com/ajdamico/usgsd/blob/master/MonetDB/monetdb%20installation%20instructions.R"&gt;monetdb installation instructions&lt;/a&gt; before running this acs code. monetdb imports, writes, recodes data slowly, but reads it hyper-fast
. a magnificent trade-off: data exploration typically requires you to think, send an analysis command, think some more, send another query, repeat. importation scripts (especially the ones i've already written for you) can be left running overnight sans hand-holding.
&lt;br /&gt; &lt;br /&gt; the acs weights generalize to the whole united states population including &lt;a href="http://www.census.gov/acs/www/Downloads/data_documentation/GroupDefinitions/2011GQ_Definitions.pdf"&gt;individuals living in group quarters&lt;/a&gt;, but non-residential respondents get an &lt;a href="http://www.census.gov/acs/www/Downloads/data_documentation/GroupDefinitions/2011GQ_Definitions.pdf"&gt;abridged questionnaire&lt;/a&gt;, so most (not all) analysts exclude records with a &lt;a href="http://www.census.gov/acs
/www/Downloads/data_documentation/pums/Estimates/pums_estimates_11.lst"&gt;relp variable of 16 or 17
&lt;/a&gt; right off the bat. this new github repository contains four scripts:&lt;br /&gt; &lt;br /&gt; &lt;b&gt;2005-2011 - download all microdata.R&lt;/b&gt;&lt;br /&gt; &lt;ul&gt; &lt;li&gt;create the batch (.bat) file needed to initiate the monet database in the future&lt;/li&gt; &lt;li&gt;download, unzip, and import each file for every year and size specified by the user&lt;/li&gt; &lt;li&gt;create and save household- and merged/person-level replicate weight complex sample designs&lt;/li&gt; &lt;li&gt;create a well-documented block of code to re-initiate the monet
db server in the future&lt;b&gt; &lt;/b&gt;&lt;/li&gt; &lt;/ul&gt; &lt;br /&gt; &lt;i&gt;fair &lt;/i&gt;&lt;i&gt;warning: this full script takes a loooong time. run it friday afternoon, commune with nature for the weekend, and if you've got a fast processor and speedy internet connection, monday morning it should be ready for action. otherwise, either download only the years and sizes you need or - if you gotta have 'em all - run it, minimize it, and then don't disturb it for a week.&lt;/i&gt;&lt;br /&gt; &lt;br /&gt; &lt;b&gt;2011 single-year - analysis e
xamples.R&lt;/b&gt;&lt;br /&gt; &lt;ul&gt; &lt;li&gt;run the well-documented block of code to re-initiate the monetdb server &lt;/li&gt; &lt;li&gt;load the &lt;a href="http://astrostatistics.psu.edu/su07/R/html/base/html/load.html"&gt;r data file&lt;/a&gt; (.rda) containing the replicate weight designs for the single-year 2011 file&lt;/li&gt; &lt;li&gt;perform the standard repertoire of analysis examples, only this time using &lt;a href="http://sqlsurvey.r-forge.r-project.org/"&gt;sqlsurvey&lt;/a&gt; functions&lt;/li&gt; &lt;/ul&gt; &lt;b&gt;2011 single-year - variable reco
de example.R&lt;/b&gt;&lt;br /&gt; &lt;ul&gt; &lt;li&gt;run the well-documented block of code to re-initiate the monetdb server &lt;/li&gt; &lt;li&gt;copy the single-year 2011 table to maintain the pristine original&lt;/li&gt; &lt;li&gt;add a new age category variable by hand&lt;/li&gt; &lt;li&gt;add a new age category variable systematically&lt;/li&gt; &lt;li&gt;re-create then save the &lt;a href="http://sqlsurvey.r-forge.r-project.org/"&gt;sqlsurvey&lt;/a&gt; replicate weight complex sample design on this new table&lt;/li&gt; &lt;li&gt;close everything, then load everything
back up in a fresh instance of r&lt;/li&gt; &lt;li&gt;replicate &lt;a href="http://www.census.gov/acs/www/Downloads/data_documentation/pums/Estimates/pums_estimates_11.lst"&gt;a few of the census statistics&lt;/a&gt;. no muss, no fuss&lt;/li&gt; &lt;/ul&gt; &lt;b&gt;replicate census estimates - 2011.R&lt;/b&gt;&lt;br /&gt; &lt;ul&gt; &lt;li&gt;run the well-documented block of code to re-initiate the monetdb server &lt;/li&gt; &lt;li&gt;load the r data file (.rda) containing the replicate weight designs for the single-year 2011 file&lt;/li&gt; &lt;li&gt;match every nation
wide statistic on &lt;a href="http://www.census.gov/acs/www/Downloads/data_documentation/pums/Estimates/pums_estimates_11.lst"&gt;the census bureau's estimates page&lt;/a&gt;, using &lt;a href="http://sqlsurvey.r-forge.r-project.org/"&gt;sqlsurvey&lt;/a&gt; functions&lt;/li&gt; &lt;/ul&gt; &lt;br /&gt; &lt;br /&gt; &lt;a href="https://github.com/ajdamico/usgsd/tree/master/American%20Community%20Survey"&gt;click here to view these four scripts&lt;/a&gt;&lt;br /&gt; &lt;br /&gt; &lt;br /&gt; for more detail about the american community survey (acs), visit:&lt;br /&gt; &lt;
ul&gt; &lt;li&gt;the us census bureau's acs &lt;a href="http://www.census.gov/acs/www/"&gt;homepage&lt;/a&gt;&lt;/li&gt; &lt;li&gt;the american factfinder &lt;a href="http://factfinder2.census.gov/"&gt;homepage&lt;/a&gt;&lt;/li&gt; &lt;li&gt;the american community survey's &lt;a href="http://en.wikipedia.org/wiki/American_Community_Survey"&gt;wikipedia page&lt;/a&gt; &lt;/li&gt; &lt;li&gt;the census bureau's &lt;a href="https://askacs.census.gov/"&gt;acs frequently asked questions page&lt;/a&gt;&lt;/li&gt; &lt;/ul&gt; &lt;br /&gt; notes:&lt;br /&gt; &lt;br /&gt; if you're just looking for a couple data point
s, you ought to give the census bureau's &lt;a href="http://factfinder2.census.gov/"&gt;american factfinder&lt;/a&gt; a whirl. it's a table creator (&lt;a href="http://www.kaiseredu.org/Tutorials-and-Presentations/Conducting-Research-with-Online-Data-Query-Tools.aspx"&gt;click here to watch me blab about table creators&lt;/a&gt;), so it's easy-to-use but inflexible. here's a li'l tip: if you run a statistic using american factfinder and then the same statistic using these scripts, they will be close but won't match exa
ctly. &lt;a href="https://ask.census.gov/faq.php?id=5000&amp;faqId=911"&gt;it's not a mistake&lt;/a&gt;, and both are methodologically correct.&lt;br /&gt; &lt;br /&gt; every now and then, &lt;a href="http://www.psc.isr.umich.edu/pscinfoserv/?p=2683"&gt;grumpy lawmakers threaten to defund the acs&lt;/a&gt; because, well, it's expensive. use it or lose it.&lt;br /&gt; &lt;br /&gt; since &lt;a href="http://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes"&gt;data types in sql&lt;/a&gt; are not as plentiful as they are &lt;a href="http://www.sta
tmethods.net/input/datatypes.html"&gt;in the r language&lt;/a&gt;, the definition of a monet database-backed complex design object requires a cutoff be specified between the categorical variables and the linear ones. that cut point gets defined using the check.factors argument in the sqlsurvey() and sqlrepsurvey() function calls. check.factors defaults to ten, but can be raised or lowered as needed. here's how it works:&lt;br /&gt; &lt;ul&gt; &lt;li&gt;if the column would be a &lt;a href="http://stat.ethz.ch/R-manual/R-pat
ched/library/base/html/character.html"&gt;character string&lt;/a&gt; or &lt;a href="http://stat.ethz.ch/R-manual/R-patched/library/base/html/factor.html"&gt;factor&lt;/a&gt; inside an &lt;a href="http://www.r-tutor.com/r-introduction/data-frame"&gt;r data frame&lt;/a&gt;, the sql database stores it as a &lt;a href="http://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes"&gt;varchar&lt;/a&gt; column.&lt;/li&gt; &lt;li&gt;if the column would be &lt;a href="http://stat.ethz.ch/R-manual/R-patched/library/base/html/numeric.html"&gt;numeric&lt;/a&gt; o
r &lt;a href="http://stat.ethz.ch/R-manual/R-patched/library/base/html/integer.html"&gt;integer&lt;/a&gt; in an &lt;a href="http://www.r-tutor.com/r-introduction/data-frame"&gt;r data frame&lt;/a&gt;, but has fewer than eleven &lt;a href="http://stat.ethz.ch/R-manual/R-devel/library/base/html/unique.html"&gt;unique&lt;/a&gt; values, the sql database also stores it as a &lt;a href="http://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes"&gt;varchar&lt;/a&gt; column.&lt;/li&gt; &lt;li&gt;if the column would be &lt;a href="http://stat.ethz.ch/
R-manual/R-patched/library/base/html/numeric.html"&gt;numeric&lt;/a&gt; or &lt;a href="http://stat.ethz.ch/R-manual/R-patched/library/base/html/integer.html"&gt;integer&lt;/a&gt; in an &lt;a href="http://www.r-tutor.com/r-introduction/data-frame"&gt;r data frame&lt;/a&gt;, but has at least eleven &lt;a href="http://stat.ethz.ch/R-manual/R-devel/library/base/html/unique.html"&gt;unique&lt;/a&gt; values, the sql database stores it as a &lt;a href="http://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes"&gt;double&lt;/a&gt; (that's sql-spe
ak for numeric).&lt;/li&gt; &lt;/ul&gt; &lt;br /&gt; unless specified by the question's phrasing, most acs variables should be treated as point-in-time, as opposed to either &lt;i&gt;annualized&lt;/i&gt; or &lt;i&gt;ever during the year&lt;/i&gt;. this distinction is particularly important for health insurance coverage. think about these three statistics --&lt;br /&gt; &lt;ul&gt; &lt;li&gt;the number of americans who won't have health insurance at least once during this year&lt;/li&gt; &lt;li&gt;the number of americans without health insurance right now&lt;/li&gt; &lt;
li&gt;the number of americans who won't ever have health insurance during this year
&lt;/li&gt; &lt;/ul&gt; -- the number of americans without health insurance right now is the point-in-time variable, smaller than the &lt;i&gt;at least once&lt;/i&gt; number but larger than the &lt;i&gt;ever&lt;/i&gt; number.&lt;br /&gt; &lt;br /&gt; although the automated ftp download program for this data set only retrieves files back as far as 2005, a nationwide version of the american community survey has been conducted since 2000. i skipped those years for two reasons --&lt;br /&gt; &lt;ul&gt; &lt;li&gt;the sample size (the true strength of
the modern acs) wasn't very large on the older files (the 2004 and 2011 single-year person-level files are 54mb and 580mb, respectively). there's no reason to import these files into a monet database.
&lt;/li&gt; &lt;li&gt;the &lt;a href="http://usa.ipums.org/usa/repwt.shtml"&gt;replicate weight&lt;/a&gt;ed design wasn't implemented until 2005, so the creation of a complex sample survey object isn't possible. if you need to calculate standard errors for earlier years, you'll have to rely on a pita &lt;a href="http://www.census.gov/acs/www/Downloads/library/2010/2010_Fuller_01.pdf"&gt;generalized variance formula&lt;/a&gt; instead. evidence: the &lt;a href="http://www.census.gov/acs/www/Downloads/data_document
ation/pums/Estimates/pums_estimates_04.lst"&gt;published estimates prior to 2005
&lt;/a&gt; don't include error columns.&lt;/li&gt; &lt;/ul&gt; -- but if it's critical for you to analyze this early data, those tables should be small enough to read into memory with &lt;a href="http://stat.ethz.ch/R-manual/R-devel/library/utils/html/read.table.html"&gt;read.csv&lt;/a&gt;. use &lt;i&gt;wgtp&lt;/i&gt; for household-weighted and &lt;i&gt;pwgtp&lt;/i&gt; for person-weighted statistics with &lt;a href="http://math.furman.edu/~dcs/courses/math47/R/library/Hmisc/html/wtd.stats.html"&gt;wtd.mean and wtd.quantile&lt;/a&gt; functions in &lt;a href="ht
tp://cran.r-project.org/web/packages/Hmisc/index.html"&gt;the Hmisc package&lt;/a&gt;.&lt;br /&gt; &lt;br /&gt; confidential to sas, spss, stata, sudaan users: the decennial census is enshrined in our constitution. your statistical software isn't. time to transition to r. :D