Only Records Meeting Certain Criteria

Example 5 - List mode: show a list of only those records meeting a set criterion

Other features illustrated: Advanced SQL

Background: You can have your table only show results for the records that meet your criteria. For example, if you want to see all the counties within a select number of states that have a population within a certain size, you can get those listed in a table and include additional information from the dataset.

Goals:

  1. Create a listing of all the counties in Alabama, Georgia, and Florida that have a population of less than 75,000 people.
  2. Show the actual population estimate and the median household income for those counties.

Steps: Goal 1 Create a listing of all the counties in Alabama, Georgia, and Florida that have a population of less than 75,000 people.

  1. Open the Ferrett Session File (or saved data basket) named sf3_bestcounty_tolive.fsf.
  2. In the data basket, delete the FIPS County variable
  3. Go to the step 1 tab. You will notice that there is a required variable - Selectable Geographies.
  4. Double click on this variable. This brings up the geography selection window. Select all the counties in Alabama, Florida, and Georgia.
  5. In the step 1 tab, go to the "Geographic Traits" topic listed under Summary File 3 and view all the variables.
  6. Sort the list by variable name and select the "NAME" and the "STUSAB" variables. These two variables will give us geographic names for our list.
  7. Select the "P001001" variable from the P1. Total Population topic in SF3.
  8. Go to the step 2 tab in order to set our population limit.
    • Click on the "Advanced Sql Option" button on the right side. This brings up the Advanced Sql Option window.
      Screenshot of Advanced Sql Option window
      Screenshot of Advanced Sql Option window (Click figure to Enlarge)
    • Insert text as shown in the highlighted portion of the image. This will limit the counties in our list to those with a population under 75,000.
    • Press the "Ok" button.
  9. "Make a Table" and close the green-bordered instruction window.
  10. Go to the "Options" menu and select "List Mode". When creating a table in "List Mode", you will always leave column 1 empty.
  11. Drag the "NAME" variable to column 2 (C2, R1).
  12. Drag the "STUSAB" variable to column 3 (C3, R1).
  13. "Go Get Data" and your result is a list of the county names and state abbreviations for all of the counties in the selected three states that have a population less than 75,000.

Steps: Goal 2 Show the actual population estimate and the median household income for those counties.

We can also view other variables in our list, such as the population and median household income.

  1. Turn off the List Mode - "Options" menu, select "List Mode".
  2. Drag the P001001 (population) variable into column 4 (C4,R1).
  3. Drag the P053001 (median income) variable into column 5 (C5,R1).
  4. "Go Get Data"