Economic Data on American FactFinder® |
 |
American FactFinder (AFF) is a database system that gives you access to the same Annual Survey of Manufactures (ASM) data that was once published in PDF format. AFF will allow you to search directly for the data you want, display data for all available sectors at once, and download the results for further manipulation.
Finding the Data |
 |
|
 |
|
Note: The following example uses Economic Census data.
The functions demonstrated are still applicable to Annual Economic Survey data.
Exercise:
Select data for one industry (fast food restaurants)
for all cities in a state (Ohio),
rank the list by sales volume, and export
the results for analysis in other software.
Under "Start here", select "Economic-Wide Key Statistics". This file includes all the economic census data that there is for counties and places, and selecting it saves multiple steps required if we were select a file under "Detailed Statistics".
Data are immediately displayed, albeit only the first 100 rows.
Since we want to look only for one industry, put your cursor over "Filter Rows" in the menu bar to reveal the drop-down menu, then, to filter rows by industry, click on "2002 NAICS ".
There are multiple ways to specify the industry. The easiest is to type in key words (or even a NAICS code) in the search box. Be sure to click the checkbox next to "Use synonyms" if you are not sure of the terminology used in NAICS. Click "Search".
In this case there is only one option presented; in other cases there might be several. Highlight the industry name "722211: Limited-service restaurants", then click "Add".
Once the industry appears in the Current Industry Selection box, click "Show Result".
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".
To get city data, in the drop-down menu for "Select a geographic type", click on "...Economic Place". Economic Place is AFF's terminology for the places for which data are presented in the 2002 Economic Census: incorporated places with 2,500 or more inhabitants, and the town and townships of 10,000 or more in selected states.
Select "Ohio" in the "Select a state" menu.
In the city menu, one could click on and add one or more specific places, but fortunately there is a shortcut for all places in the state. Highlight "All Economic Places" and click the "Add" button.
Since we don't want the U.S. total in our output, highlight "United States" in the "Current geography selections" box, and then click the "Remove" button. At this point, you could add in a state total or places from additional states by repeating the previous steps. Finally, select "Show result".
We want to rank the output by sales, but we need to make sure we are dealing with the right concept. Click the cryptic column heading "Sls, shps, rcpts, rev ($1,000)"
Since this confirms that "Sls, shps, rcpts, rev" really provides the sales figures we want, we can close this window. Definitions are available for any data item by clicking on the column heading, or for any industry by clicking on the industry code.
To sort cities by rank order of sales, hover your cursor over "Options" on the menu bar, and then click on "Sort Rows".
Pull down the menu next to "Sort by" and highlight "Sls, shps, rcpts, rev ($1,000)". Click the "Descending" radio button to its right. Finally, click the "Update" button.
This may be the display we were after. Note that the default display does not include FIPS codes for geographic areas. If we want, we can add them through the "Options" menu.
FactFinder does have limited ability to perform some calculations on numbers all in the same row, like sales per establishment, but it can't do calculations across rows, like adding up the top 5 cities in the state. For this we will need to download the data to our own spreadsheet program, but that is very easy. To download the data, put your cursor over "Print/Download" and select "Download".
Note: If you are using Internet Explorer and nothing happens at this point, click the link in the lower right of the Download screen (illustrated above) Using FactFinder with Windows XP Service Pack 2. It describes steps you can take so that Internet Explorer will let you download files. Other browsers, such as Mozilla Firefox, have not experienced these problems.
Accept the default selections to create a comma delimited (.csv) file, and click the "OK" button.
If your browser is set up to allow you to open a .csv file directly into your spreadsheet software, just click the "OK" button. Otherwise, select "Save to disk", select an appropriate directory, and type in a file name that will be meaningful to you. Then simply open the file in your spreadsheet program.
Note: If your browser is Internet Explorer and all of the data appear in Column A, you can parse the data into columns using steps outlined in the link on AFF's Download screen discussed above Using FactFinder with Windows XP Service Pack 2, but also summarized here: Highlight column A. From the Excel menu bar click on "Data." Select "Text to Columns." Select "Delimited." Select "Comma." Click "Finish."
Note on files downloaded from FactFinder.
.csv files downloaded from FactFinder are nicely designed to be opened in Excel, but they may present certain problems to other spreadsheets or database packages that import the data. Because numeric values are enclosed in quotes, software other than Excel may not allow calculations based on those character strings. In such cases, it may be useful to open the .csv file in Excel, change the format of data columns to numeric, and resave the file.
| Contents of file IBQDownload.csv
Sector 00: All sectors:
Geographic Area Series: Economy-Wide Key Statistics: 2002 |
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 035 for Cleveland becomes just 35. To reinstate leading zeroes in Excel, right-click on the appropriate column heading, select Format Cells, then select Custom under Category in the Number tab, and then enter into the "Type" box a string of zeroes equal to the number of characters in the code -- 2 for a state code, 3 for a county code, etc.
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 we had wanted to specify twenty-five specific cities in a dozen states rather than just one state, the repetition would be rather tedious.
