topic Re: How to standardize U.S. cities variable in SAS in SAS Programming

How to standardize U.S. cities variable in SAS

sasjoker — Wed, 05 Jun 2013 18:06:46 GMT

I have a list of city names in the U.S. They are typed in by end-users. A lot of them contains minor typos. See below

Is there an internal list of U.S. city names in the SAS that I can compare my city name list to the correct list (maybe by using COMPLEV???) in order to minimize distance and then

standardize city names. Alternatively, is there a function that transform the first column into code or standard city names so that I can do proc geocode on them?

I tried to manually correct these, almost killed myself...

Thanks a lot, guys!

Chicago,	Chicago
Chicao	Chicago
Chichago	Chicago
Cincinatti	CINCINNATI
Cincinnati	CINCINNATI
Cincinnatti	CINCINNATI
Cinncinati	CINCINNATI

Re: How to standardize U.S. cities variable in SAS

Reeza — Wed, 05 Jun 2013 18:50:17 GMT

There's the maps.uscity table that has City Names, and lat/long.

But city names aren't unique so you need to be careful,

proc freq data=maps.uscity noprint;

table city/out=city_names;

run;

proc sort data=city_names; by descending count ;

run;

Re: How to standardize U.S. cities variable in SAS

Astounding — Wed, 05 Jun 2013 19:35:43 GMT

Another consideration: Many city names are similar. For example, if you see the misspelling "Parris", there are many legitimate city names that could match it: Paris, Perris, Parrish. Would you like a computer to make the decision for you?

One viable approach would be to take all the corrections that you so studiously made already and automate them. Apply the same changes you made to the next batch of data. Then see which spellings still don't match a legitimate city and augment your list.

Good luck.

Re: How to standardize U.S. cities variable in SAS

ballardw — Wed, 05 Jun 2013 19:41:25 GMT

There should be a ZIP code data set SASHelp.Zipcode that has city and state information including some "alternate" city names associated. If by any chance your data also contains zip codes the function ZIPCity may be of interest to you.

Good luck.

Re: How to standardize U.S. cities variable in SAS

sasjoker — Wed, 05 Jun 2013 21:11:46 GMT

Unfortunately, the zip code variable is not available. But I also have state variable I can use to triangulate. Could I ask in this case, what would be a better solution? Thank you, guys -

Re: How to standardize U.S. cities variable in SAS

ballardw — Wed, 05 Jun 2013 22:27:36 GMT

My general approach would be to find the matches by state on one or more of the city variables in the ZIP code data set first. For the ones that don't match I would start by examining the difference between the (distinct) cities in the data with ZIP code set looking for the minimum COMPGED or COMPLEV and/ or maximimum COMPARE function results.

Re: How to standardize U.S. cities variable in SAS

sasjoker — Thu, 06 Jun 2013 18:54:52 GMT

Thank you for your help -

Re: How to standardize U.S. cities variable in SAS

sasjoker — Thu, 06 Jun 2013 18:56:13 GMT

That is a good method also, thanks!

Re: How to standardize U.S. cities variable in SAS

addicted4frinds — Wed, 14 Jul 2021 12:37:46 GMT

How can i have first name and last name in signle column without using functions. By using length and colon. can any1 help me with this please

Re: How to standardize U.S. cities variable in SAS

Kurt_Bremser — Wed, 14 Jul 2021 12:58:45 GMT

Start your own thread, and give it a meaningful subject line; adding a post to a thread that is 8(!) years old won't get you many responses
Provide example data, so we know what you are talking about. Usable example data means posting a DATA step with DATALINES (or a simple assignment if one observation is sufficient)
Show what you expect to get out of that
Posting unreasonable conditions makes no sense; if a certain function is ideal for your task, use it