Missouri Census Data Center

Dexter Query Detailed Description

Query ID: acsbtable1

Summary

The 2005 American Community Survey tables are stored in data directory ("filetype") acs2005. The tables are grouped by certain categories ("B" vs "C", with imputation, PR (Puetro Rico only) and other special tables grouped into separate data sets), as well as by "wave". See the acs2005 Readme file for details. In this example we assume that we know what table we want. It is table B17002, containing data on poverty ratios of persons. We want to retrieve this data in a csv file (so that we can load it into Excel and do postprocessing) and we want it for all states (without any geographic components).

Filetype accessed: acs2005 : American Community Survey data for 2005.

Data Set accessed: btables2 (because this is where we keep all the "B" tables released in Wave 2.) See detailed metadata for this data set.

Dexter features used: Nothing beyond the basics. But we do explain how to get around a glitch (bug?) in the IE browser (Version 6) so that we are able to scroll through the Tables select list.

Can be readily modified to:

  • Select a different table or tables (in the same btables2 subcollection).
  • Change the geography filter. Instead of states you could select counties or places or pumas, for example. Or you could select multiple geographic types within a specified state.

Degree of Difficulty: 3 - moderate. (the Dexter part is not very hard, but it takes some familiarity with this complex data collection to know where to find the available tables and determine which of the data sets in this directory contains the table desired.

Saved Query File: View It Run It (takes about 20 seconds)

Annotated Dexter Query Form (As filled out to define this query)

Section I: We accept the default. Output is to a delimited file in csv format. We want our data for postprocessing using Excel.


Section II: The filter specifies two conditions:
  1. The SumLev variable must have a value of 040, indicating a state level summary. If you are not familiar with these codes you can follow the link to detailed metadata at the top of the page and then click on the sumlev link in the Key variables section of the resulting metadata page.

  2. There are separate summaries for various geographic components of the states, in which we are not interested. So we specify the second condition that the geocomp variable have a value of 00, indicating that is is NOT a geographic component (but rather a summary for the entire geographic area.) You can find values for the geocomp codes the same way as explained above for the codes for the SumLev variable.

Screen image of dexter query form, part 2


Section III:

We select the single identifier AreaName from the Identifiers list on the left. You might also want to select State or Geocode if you wanted a FIPS code to go with the name of the state. You would definitely want to choose more and/or different identifiers if you were selecting more summary levels. You would want to keep either Name or Geocomp_name if you were not filtering out the geographic components.

This screen snapshot shows the browser window as it was initially displayed and after we chose our identifier(s). Note that the Tables select list on the right is not completely displayed; it is too wide to fit on our screen. We shall need to use the horizontal scroll bar in our Internet Explorer browser window to make the Tables select list fully visible, including its vertical scroll bar. We shall need to use the latter to scroll down to the table we wish to select.

Screen image of dexter query form, part 3, initial view

Here is what the screen looks like after we scroll right to see the full Tables select window with its vertical scroll bar. What you cannot see from the screen shot is what happens when you try to make use of that vertical scroll bar. If you are using Internet Explorer, Version 6 it does not work; somehow IE gets disoriented and loses track of where the scroll bar is at this point. (This problem appears to be fixed in IE, Version 7.)

Screen image of dexter query form, part 3, second view

The solution to this glitch is pretty simple but not intuitively obvious. You have to use the vertical scrollbar on the IE window (NOT the one on the TABLES select window) to go down the page so that the Tables window is no longer visible, and then scroll back up so that it is once again visible. At this point the scroll bar seems to always work. (At least this is the way it was in the spring of 2007; this problem may have disappeared by the time you read this, or maybe it is just my browser that behaves this way.) At any rate here is what the window looks like once you have a working scrollbar and can select the table you want:

Screen image of dexter query form, part 3, third view


Section IV and Section V: These sections are not used for this query. We use the Extract Data button at the bottom of Section III to submit the query.


Output

The query takes about 20 seconds to run because the data set being accessed is quite large. When the Output menu screen displays a message telling me that the extraction has completed I then click on the link to the Delimted File. Since my browser (IE) has been configured to automatically invoke Excel when I click on a file with a csv extension the next thing I see is this (not all rows shown):

Screen image of Excel spreadsheet created from the extract


Sharing these results with a client or colleague is rather easy. You can always save your work as an xls or csv file on your PC and then send it as an attachment to the person(s) with whom you want to share. The file is rather large and it will waste some resources doing it this way but it's familiar and easy. Another option you have is to take advantage of the fact that any output file you generate with Dexter remains on the mcdc server for 2 days and is publicly accessible. To get the URL for your output file right click on the Delimited File link on the Output Menu page and choose "Copy Shortcut" (stores the URL in the clipboard). Then go to your e-mail client and send a message to the person(s) with whom you want to share your results. As part of the message you can just paste the URL of the file that you just copied to the clipboard. For example, when we ran this xsample to create this page our output was saved at /tmpscratch/14MAY07_00007.dexter/xtract.csv . You can do the same thing with the Summary log file. These are temporary files and will remain on the server for approximately 48 hours from the time they are created (barring an emergency).


Exercises

  1. Modify the filter in Section II so that it selects only county level data and only for your state.

  2. Create an extract with 3 B tables of data for all Metropolitan Statistical Areas in the U.S.:
    • Modify the filter in Section II to select only Core Based Statistical Areas (CBSA's) which are also Metropolitan Statistical Areas (as opposed to Micropolitan Statistical Areas - you will want to include a condition to specify that the value of cbsatype is "metro".
    • Modify the Identifiers select list in Section III to keep the code associated with the CBSA as well as its name. Modify your Tables selection to keep the 3 tables B17001, B17002, and B17003.

  3. Generate a report showing the Median Household Income for all Metropolitan Statistical Areas in the U.S. The report should be in html format and should have a custom title and subtitle of your choosing. To get this you need to:
    • Modify Section I to indicate you do not want any delimited file and that you do want an html format report.
    • Modify Section II as per the previous exercise to select just MSA's using conditions on SumLev and cbsatype.
    • Assuming that you have looked at detailed metadata (at the American FactFinder site for ACS, for example) and have determined that the first cell in Base Table B07011 contains the Median Household Income for all households, modify Section III by using the "3rd way" to specify what variables you want to keep. In the text box provided type in the name of the 2 identifier variables to keep (the code and name of the area being summarized) as well as the variable containing the Median Household Income. You will need to know the variable naming conventions for items in a table.
    • In Section IV enter a value for the title and subtitle you would like to have for your report.
    • In Section V, subsection b, check the box that indicates you want to use variable labels as the column headers in your report. In subsection c code an entry to specify what label (and hence column heading) you want to assign to the variable corresponding to the first cell in table B07011. Also code an entry that specifies you want this variable to have a format code of dollar8. associated with it. If you have never ventured into the Advanced Options section before be sure to click on the section header which is a link to the online help; that is where you'll find explanations and examples of how to code your entries to specify the label and format code to associate with the median household income variable.