Help using Base SAS procedures

Proc Geocode- Cleaning city names using M_CITY

Accepted Solution Solved
Reply
Occasional Contributor TB2
Occasional Contributor
Posts: 8
Accepted Solution

Proc Geocode- Cleaning city names using M_CITY

[ Edited ]

I am trying to clean address data by correcting different spellings of the same city (Ex. St. Lous, Saint Louis...). I am able to use PROC GEOCODE to turn street addresses into Y X cordinates. SAS suggests alternative addresses and states (M_ADDR and M_STATE). However the M_CITY variable field is blank for every observation. 

 

I wanted the M_CITY variable because I thought it could help standardize city spellings. According to the SAS documentation, 

M_CITY contains the city name for a city and state match. The M_CITY value is the match value from the lookup data.

 

Question:

Can anyone help me figure out why the M_CITY variable is blank?

Details:

I am using the SASHELP.USM dataset to look up street locations.

I am also open to other solutions to standadize city spellings.

 

 

Code:

PROC GEOCODE
METHOD=STREET
DATA=WORK.ADDRESS_TEST1
OUT=WORK.GEO_ADDR_TEST2
lookupSTREET=SASHELP.USM;
RUN; 

Thanks,

TB


Accepted Solutions
Solution
‎02-19-2016 02:34 PM
SAS Employee
Posts: 170

Re: Proc Geocode- Cleaning city names using M_CITY

Posted in reply to ChrisHemedinger

I did a little more research on M_CITY.  It was only returned if we did a city match (not a street-level match or zip match, etc).  It was suggested that having that information for a street match would be useful so we added the M_CITY information.  That appears to be added in 9.4m1.  So, 9.3 definitely does not have M_CITY.

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: Proc Geocode- Cleaning city names using M_CITY

It has been a while since I did my last geocoding, I can only speak from my impression. You may need to download the most current TIGER file from census site to do a thorough geocoding. The dataset in sashelp is just for demo, I believe. It is neither complete nor current. Please reference this paper for details:

http://support.sas.com/resources/papers/proceedings10/332-2010.pdf

Occasional Contributor TB2
Occasional Contributor
Posts: 8

Re: Proc Geocode- Cleaning city names using M_CITY

I am using the 2014 TIGER file. I noticed they have 2015 available, but thought it might not be necessary for my purposes. I am comfortable assuming that the addresses in my file were around in 2014. Unless you can think of a new featurture in the 2015 TIGER data set that would help.

 

 

Thanks for your paper suggestion.

Respected Advisor
Posts: 3,156

Re: Proc Geocode- Cleaning city names using M_CITY

[ Edited ]

Understood what you are saying, but just to confirm, otherwise I will be utterly lost, is this the actual code you were running?

 

lookupSTREET=SASHELP.USM;

 

As this is refering to sashelp library, which by the way, can NOT be reassigned. So are you saying in your real code, you were using 2014 Tiger in a different library, meaning you real code will be using a different non-reserved libname to address that.

 

Other than that, the only thing I know is that if the city level match is failed , then the city will be left blank.

Occasional Contributor TB2
Occasional Contributor
Posts: 8

Re: Proc Geocode- Cleaning city names using M_CITY

 I did not download the TIGER dataset myself. However when I open the SASHELP.USM dataset, it says that the source is Tiger 2014.


Tiger2014.JPG
Respected Advisor
Posts: 3,156

Re: Proc Geocode- Cleaning city names using M_CITY

Hmm. Something isn't quite right about your USM file. This is mine, mine is 2014 Tiger as well. Yours doesn't even have city columns.usm.PNG

So you may want to check your lookup table.

 

I am aware of you have a big data to tackle, so this is just FYI in case you need it in the future. The following link provides the most updated golden standard geocoding for financial industry:

https://geomap.ffiec.gov/FFIECGeocMap/GeocodeMap1.aspx

 

They don't provide API for automated search and web scrapping, but google will help.

Super User
Posts: 11,343

Re: Proc Geocode- Cleaning city names using M_CITY

You may want to look at http://support.sas.com/rnd/datavisualization/mapsonline/html/geocode.html for additional datasets.

 

I believe the SASHELP.USM is a subset of data from TIGER.

Super User
Posts: 3,257

Re: Proc Geocode- Cleaning city names using M_CITY

FYI, SAS's Data Quality aka SAS Dataflux, with an address matching plug-in like Loqate provides an automated approach to your problem. It does geo-coding, address standardisation and cleansing, zip codes and then some. Its really an enterprise solution for fixing data quality issues but thought you would like to know about it.

SAS Employee
Posts: 170

Re: Proc Geocode- Cleaning city names using M_CITY

There was a defect in Proc GECOCODE that M_CITY didn't display in the output data set.  I know it works in 9.4M2.  I do not know how far back the problem went.

Occasional Contributor TB2
Occasional Contributor
Posts: 8

Re: Proc Geocode- Cleaning city names using M_CITY

Posted in reply to Darrell_sas

I am using SAS Enterprise Guide Version 5.1 (5.100.0.13304 Hot fix 17)

 

Do you think this issue effects my version of SAS?

Community Manager
Posts: 2,955

Re: Proc Geocode- Cleaning city names using M_CITY

With EG 5.1 you are probably using SAS 9.3.  You can check the SAS version by submitting:

 

%put &SYSVLONG;

Or by right-clicking on the server in your Server list, select Properties, then look at the Software tab in the window.

 

sasversion.png

Occasional Contributor TB2
Occasional Contributor
Posts: 8

Re: Proc Geocode- Cleaning city names using M_CITY

Posted in reply to ChrisHemedinger

I am using SAS 9.3. So I guess it is possible that the defect is the reason that the M_CITY field is empty.


SAS version.JPG
Community Manager
Posts: 2,955

Re: Proc Geocode- Cleaning city names using M_CITY

Oh, and it looks like there was a related defect (if not the one @Darrell_sas mentions) fixed at 9.4 Maint 1.

Solution
‎02-19-2016 02:34 PM
SAS Employee
Posts: 170

Re: Proc Geocode- Cleaning city names using M_CITY

Posted in reply to ChrisHemedinger

I did a little more research on M_CITY.  It was only returned if we did a city match (not a street-level match or zip match, etc).  It was suggested that having that information for a street match would be useful so we added the M_CITY information.  That appears to be added in 9.4m1.  So, 9.3 definitely does not have M_CITY.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 683 views
  • 4 likes
  • 6 in conversation