Doing cross tabs can create quick comparisons

Cross tabs and pivot tables are powerful data analysis tools that allow journalists to quickly reorganize and compare large datasets by rearranging fields into rows and columns. The article demonstrates how to use SQL queries in Visual FoxPro to create cross-tab comparisons of SBA disaster loan data by state and loan status.

By Jeff Porter, IRE/NICAR

Call it an art form - rearranging data in a useful way, comparing fields across columns instead of down long rows. The spreadsheet program Excel calls it a pivot table. Database managers often call it a cross tab. For Visual FoxPro users, it's a fast, quick step to compare hundreds of thousands of records instantly.

Take, for example, Small Business Administration disaster loan data. The loans themselves are the primary form of federal assistance for nonfarm, private-sector disaster losses. The loans help homeowners, renters, businesses of all sizes and nonprofit organizations finance their rebuilding. The loans, which are repaid to the Treasury, are provided at low-interest rates and have tailored repayment schedules befitting each borrower's financial capability.

To create a cross-tab comparison, the rules are simple.

You want three fields. Ideally, the first has the most potential variables. Using the SBA disaster loan data, we'll compare states and a few other geographic areas, such as territories.

In the second field, you want to limit the number of variables, technically, to 255. In practical terms, you want just a few variables. In the SBA disaster loan database, there's a field called CHGOFF, noting whether a business disaster loan is deemed uncollectible. It's either a YES or NO - just two possible variables and perfect for a cross-tab.

The third field is the data field, a total count or sum, for example, categorized by the two other fields. For this example, the count will be categorized as the status of each loan in each state.

For Visual FoxPro 5.0, here's the SQL to first list the borrowers' states, whether the loan was charged off, and the total number for each category by state. The query limits the type of loan to just businesses - coded as a capital B in the data not individuals, because the SBA won't tell the status of an individual's loan. In this example, the number of loans is limited to those since the beginning of 1990 to businesses: select borstate, chgoff, count (*) from disaster; where apprdate > {12/31/89) and type = 'B', group by 1, 2 The results would look like this:

THE Query

Borstate Chgoff Ltd

NO 1 AE NO 1 AK NO 68 AL NO 97 AL YES 2 AR NO 216 AZ NO 10 CA NO 150 CO NO 25 CT NO 20 DC NO 37 DE NO 4 FL NO 829 FL YES 4 GA NO 161

Helpful, but only slightly. Now, one could export results to Excel and create a pivot table to show the CHGOFF variables across the top of the spreadsheet and the states down the side, but Visual FoxPro can do it quicker. Just use one line of SQL in the software's command window: do _genxtab)

With this short command, you've created a new table called Xtab, created in the software's default directory. It should, though, be already open, so simply type

browse

And you should see this:

HE Xtab Borstate No Yes 28 4 AA 2 AE 8 AK 695 29 AL 1416 117 AP 4 AR 642 25 AS 179 41 AZ 340 38 CA 36787 4752

Better - at least one can read across and see in a glance how a particular state is doing. Note here, for example, that in California, 36,787 loans aren't in trouble, while 4,752 are categorized as uncollectible.

What's that as percentage? Again, one could export over to Excel; instead, though, one could run one last, quick query to show the result. For this example, the number of total loans is limited to 101 or more by state or territory, to leave out areas with a tiny number of loans that might skew a comparison: select borstate, no, yes, yes/(no+yes) pct from xtab; where no+yes > 100; order by 4 desc

The query creates a new field - the number of loans charged off, divided by the total number - and names it PCT, short for percentage. Also added: The last line tells FoxPro to sort the records by highest percentage to lowest, finding the states with the highest percentage of loan default. The answer:

Query Borstate No Yes Pct AS 179 41 0.1864 RI 881 18 0.1538 SC 1770 303 0.1462 GA 2007 289 0.1259 PR 5349 732 0.1204 FL 10345 1394 0.1187 CA 36787 4752 0.1144 HI 1666 207 0.1105 MO 1922 221 0.1031 IL 2074 237 0.1026 NH 132 15 0.1020 AZ 340 38 0.1005 LA 2367 251 0.0959 KS 642 61 0.0868 NM 139 13 0.0855 SD 718 67 0.0854 IN 410 38 0.0848

In the first record, AS stands for American Samoa, a U.S. territory with 19 percent of the loans defaulting during the time period. Those California numbers show up at No. 7 in this calculation, with 11 percent.

States, of course, make up a simple example. Reporters can filter and cross-tab by city, zip code, even type of business to compare and calculate, filtering before or after running the cross-tab command. The SBA disaster loan data table contains 731,352 records, far beyond Excel's pivot table capability.

Of course, even a well executed cross-tab doesn't relieve the reporter of reading the fine print about the data. The table lists the state of the borrower, not necessarily the location of the damaged property. The majority of the most recent loans, including those in the days after the Sept. 11 attacks, haven't had time to run into serious trouble.

Jeff Porter can be reached by e-mail jeff@nicar.org