Dexter Xsample: Detailed Query Description
Query ID: PoorestCounties
SummaryAccesses a standard extract data set for the 2000 census, Summary File 3, with state and county-level data for the whole country (including Puerto Rico). It selects all counties that had a poverty rate of at least 25%, but excludes the county equivalents in PR. We chose the 25% threshold after looking at a prelinary extract and knowing that we wanted about 200 counties (we got 203). We keep 3 poverty count variables and the corrsponding percentages. Output is a plain text report file with custom title, subtitle and footnote.
Filetype accessed: sf32000x
Data Set accessed: usstcnty - chose because we wanted data for the entire U.S. with county level summaries.
Dexter features used:
Can be easily modified to: Raise or lower the poverty threshold for inclusion in the report. Keep more variables and output to a data set for subsequent analysis to see what other demographic indicators tend to go along with high rates of poverty (e.g. variables such as PctUrban, PctBlack, PctOver65, etc.)
Degree of Difficulty: 3 - moderate.
Annotated Dexter Query Form (As filled out to define this query) Section I: All we do in Section I is check that we wanted no Delimited File and that we wanted a Plain text report to be generated instead. Note that we could have (but did not) check the box at the bottom saying we wanted the report to be piped directly to our browser. That would eliminate the generation of the blue Output Menu screen with the link that takes us to the report file.
Section II: We have 3 conditions for our data filter.
Section III: We only choose a single identifier, County. This variable is stored as a FIPS county code but displays as the county name (using a SAS format code, $county.) If we wanted to see the FIPS code for the county in the report we could select GeoCode from the Identifiers list. On the Numerics side we know we want the number of poor persons and the poverty rate (percentage of persons who are poor). But while we are focusing on those 2 items we see that there are other variables on the data set that are measure of poverty as well and would therefore be of some interest for our report. The variables on the data set are grouped together by subject, so once we scrolled down the list and found the Poor and PctPoor items, the related items were nearby and easily selected. We chose the VeryPoor and NearlyPoor variables along with the corresponding percentages. Note (from the position of the scrollbar on the Numeric select window) that we had to scroll down almost 3/4 of the way through the list of around 400 items. This can be a little tedious if you are not familiar with the data set and how the variables are ordered. There is a tool that will assist you in finding variables from long lists (100 or more variables) labeled "Filter by Regular Expression" which appears just below the select list. This xsample does not feature that capability but if you would like to learn more about how it works click on the the Section III header to access the Online help for this section and then look for the Filter by regular expression Processing topic.
Section IV: As usual with this section the entries are pretty straightforward. The idea is to help your audience understand what they are looking at in the report by providing informative titles and footnotes. The Sort entry specifies that we want to have the report sorted by descending value of the variable pctpoor (the leading minus sign is used to indicate the descending sort).
Section V: In the b. Advanced Report Formatting Options subsection we check the box that says to use variable labels (instead of variable names) as column headers in the report. This is a very common thing to want to do. Nothing really very advanced about it, but you have to use this section to specify it.
Output Report: Not too many real famous or fmiliar counties (to most people, at least). Lots of areas with Indian reservations and rural southern areas. Starr, Texas (the largest of the counties shown in the top (or bottom, depending on POV) is in extreme southern Texas on the Mexican border.