1997 Economic Census on American FactFinder® |
 |
| Quick Reports | Building a Query | Creating a Map | Limitations to AFF |
Building a Query |
|
Example: Select data for one industry for all counties in a southwestern four-state area, rank the list by sales volume, and export the results for analysis in other software.
|
Click on the Data Sets button in the left column of the FactFinder main page. |
1. Select the "Economic Censuses and Surveys" tab.
2. In the right-hand column, near the bottom, click on "List Data Sets - by sector".
3. In the Select-a-sector box, cursor down to Retail Trade, highlight it, and click the Go button.
4. Click on "Geographic Area Series: 1997"
5. Data are immediately displayed, albeit only the first 100 rows. Since we want to look only at retail trade totals, put your cursor over "Filter Rows" to reveal the drop-down menu, then click on "by Industry--1997 NAICS".
6. There are several ways to specify the industry. In the "Select a sector" box, pick "All Sectors (2-digit)".
7. Then highlight "44-45: Retail Trade" in the next box and click the "Add" button. Once our category shows up in the bottom box, click the "Show Result" button.
8. That gives us a one-line table, since the default for this data set is to initially show only U.S. level data. We know there is more here, so put your cursor over "Filter Rows" again, and select "by Geography".
9. In the drop-down menu for "Select a geographic type", click on "...County".
10. Select "California" in the "Select a state" menu, and then, when the screen repaints, select "All Counties" and click the "Add" button below the county menu.
| 11. Since we want all counties in a four-state area, repeat step 10 for Nevada, Arizona, and New Mexico. |
|
12. Since we don't want the U.S. total in our output, select "United States" in the "Current geography selections" box, and then click the "Remove" button. Finally, select "Show result".
13. If the data display is too wide for your computer monitor, or if you want to reduce the stacking in text columns, you may specify just the columns you want to see. Put your cursor over "Options' and click on "Select Columns".
14. Click on the check boxes for the NAICS code, both value (i.e., code) and meaning (i.e., text), to remove those items from the display. Also remove the checks from the 5 items from Annual Payroll on down. FIPS State and county codes may be useful in further manipulation of the data, so add check marks in the Value column next to those items. Finally, click the "Update" button at the top.
13. The display is in alphabetic sort by state and county. To sort counties in rank order of sales, put your cursor over "Options" and then click on "Sort Rows".
14. Pull down the menu next to "Sort by" and highlight "Sales ($1,000)". Click the "Descending" radio button to its right. Finally, click the "Update" button.
15. FactFinder's sorts D's (data withheld to avoid disclosure) as if they were the highest values. Most such values are small, but even if some were high, we wouldn't know where to put them in a ranking. To exclude the suppressed lines we need to "Filter Rows" and select "by Data Value".
16. Highlight "Sales ($1,000)" and click "Next".
17. With the pull-down menu, specify sales "between" and then enter "1000" (i.e., $1,000,000) as the lower bound and "9999999999" as the upper bound, then click "OK".
18. This is the display we were after. To download the data, put your cursor over "Print/Download" and select "Download".
19. Under some circumstances, you may want to select "All columns including data flag columns", particularly where you have not excluded alphabetic values from numeric fields and you are going to load the data into a database package where you need to be able to evaluate the flags. In so doing we would lose our column selections, including the state and county codes we added.
Retain the defaults and click "OK".
20. Select "Save this file to disk".
21. Select an appropriate directory for the resulting file, and type in a file name that will be meaningful to you. If you simply accept FactFinder's default name, each new downloaded file will copy over the last, which is probably not what you want.
The file you saved will not be pretty, but most spreadsheets or database packages can import it.
| Contents of file Top retail counties in CA-NV-AZ-NM.csv
Sector 44: Retail Trade: Geographic Area Series: 1997
"FIPS state code","FIPS County Code","Geographic Area Name","Number of establishments","Sales ($1,000)"
|
Because numeric values are enclosed in quotes, most software other than Excel will not allow calculations based on those character strings. A disadvantage of using Excel to manipulate output is that that software drops leading zeros in FIPS state, county, or other geographic area codes; for example, state code 06 becomes 6.
Note on Saving queries:
FactFinder not only lets you save output, it allows you to save the query itself, so that you can pick up where you left off in a future session. This is useful if your work must be interrupted and you want to avoid the consequences of FactFinder "timing out".
Simply select "Save Query" from the Print/Download menu, and specify a file name and location you can remember. The extension must be .sql. To load the query later, select "Load Query" from the same menu, locate the directory where you saved the .sql file, select and open it.
Saving a query is also useful if you have defined a complex query and want to be able to return to tweak it in the future, as in the specification of a long list of geographic areas. For example, if, in steps 10 and 11 above, we had wanted to specify all counties in all states rather than just four, the repetition of step 10 51 times would be rather tedious. We have actually created that query for you, and you can save counties.sql to your hard drive and try it yourself. The default industry selected is NAICS 44-45 Retail Trade, but this particular query was structured using the Economy-Wide Key Statistics File so that you may load the query and then change to any industry. For counties in rank order, excluding those with a value of "D", try counties-ranked.sql.
Ready to try buiding a query yourself? Click on the FactFinder icon.
Next topic: Creating a Map
