Translating Zip codes into cities

In a recent workshop, a participant asked how to deal with his Zip code data. Every time he did a survey, he was manually grouping the codes into regions for analysis. If you're in a similar situation, here's a better approach.

First, you need to be able to merge two pieces of data: your survey responses and the Zip code information. This means you'll need to add some extra fields to your questionnaire to handle the city/state/CBSA information. In a Web form, you can use hidden fields to stash the information. On a paper form you can add them to your survey software or database after you print the questionnaire.

If you're using Web, CATI or CAPI surveys, your software may include the ability to pipe in or look-up information while you're collecting responses. This is the easiest approach, as everything is taken care of at once with no extra steps for the merge.

For paper surveys or when your software doesn't support piping, you can do a second import pass. In your import dialog, you want to find settings for keyed/posting/updating/merged imports. Test this carefully on a temporary database (not your real survey data), as these imports can have quirks such as updating just the first instance of a Zip code it finds instead of all the records.

Whichever way you go, remember the keying or look-up is for a literal match. Make sure you're using a Zip or Zip+4 consistently between your questionnaire and Zip code data source. Likewise, make sure that other values, such as Canadian postal codes are handled gracefully.

The second piece is a data source. If you search for "Zip Code database" or "CBSA database" you'll find plenty of sources. Or you can just try Zip Code Download, which has an excellent range of offerings. In addition to different levels of detail, they also have one-time or subscription pricing, which means that if you just need city names for a one-time project it's going to run you just $20. Their license also allows for use on a Web server for piping or look-ups.

Need a Hand?

A little help can add a lot of polish—or just save hours and headaches:

(206) 399-2344 Download VCard LinkedIn Profile

The course was very well received. Ann in one word is phenomenal. Please thank her again for all her hard work and of course patience. Amazing woman.

Marian Slobodian
Statistics Canada