topic Re: Cleaning data to removed non-letter entries. in SAS Procedures

Cleaning data to removed non-letter entries.

rob_roche — Thu, 12 Jul 2018 15:08:34 GMT

I have a dataset that contains items such as "Oklahoma" "0klahoma" "Okla#oma" "555-444-3333" and I only want to keep entries that contain purely letters - dropping all entries that contain anything other than a letter. How do you do it?

Re: Cleaning data to removed non-letter entries.

Reeza — Thu, 12 Jul 2018 15:19:27 GMT

NOTALPHA() function.

http://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p15hz07s8zvsywn1q59607pduunb.htm&docsetVersion=9.4&locale=en

if notalpha(your_variable_name) then delete;

@rob_roche wrote:

I have a dataset that contains items such as "Oklahoma" "0klahoma" "Okla#oma" "555-444-3333" and I only want to keep entries that contain purely letters - dropping all entries that contain anything other than a letter. How do you do it?

Re: Cleaning data to removed non-letter entries.

PeterClemmensen — Thu, 12 Jul 2018 15:22:26 GMT

data have;
length string $20;
input string $;
datalines;
Oklahoma
0klahoma
Okla#oma
555-444-3333
;

data want;
   set have;
   if anypunct(string)=0 & anydigit(string)=0;
run;

Re: Cleaning data to removed non-letter entries.

rob_roche — Thu, 12 Jul 2018 15:25:05 GMT

This seems to have been the most effective - thanks!

Re: Cleaning data to removed non-letter entries.

Astounding — Thu, 12 Jul 2018 15:29:13 GMT

It would be safer if you would specify some of the details. Since the objective is to delete observations, it's a good idea to spell out the rules before deletion takes place.

Is a comma always invalid, or could this appear: Washington, DC

Is a hyphen always invalid, or could this appear: Raleigh-Durham

Would a zip code be valid?

Do you have a small list of valid values?

Does capitalization matter?

Re: Cleaning data to removed non-letter entries.

PeterClemmensen — Thu, 12 Jul 2018 15:30:57 GMT

I agree with @Astounding. Also, I think @Reezas solution is the way to go 🙂

Re: Cleaning data to removed non-letter entries.

rob_roche — Thu, 12 Jul 2018 15:34:35 GMT

These are great questions - what I am trying to do is create a list of US city names, this will become my reference list. I'm trying to bump international names out of a master list of 55 million records. I'm trying to create a table that has a list of pairs of city, county, zip. FORTUNTELY, I found a .txt file online containing this information. However, I was frustrated I couldn't figure out how to clean my data myself - hence the question.

Re: Cleaning data to removed non-letter entries.

Reeza — Thu, 12 Jul 2018 15:37:33 GMT

sashelp and sasmaps libraries have several of these datasets readily available for you.

@rob_roche wrote:
These are great questions - what I am trying to do is create a list of US city names, this will become my reference list. I'm trying to bump international names out of a master list of 55 million records. I'm trying to create a table that has a list of pairs of city, county, zip. FORTUNTELY, I found a .txt file online containing this information. However, I was frustrated I couldn't figure out how to clean my data myself - hence the question.

Re: Cleaning data to removed non-letter entries.

rob_roche — Thu, 12 Jul 2018 15:40:03 GMT

That's also great to know - this is week 3 of using SAS 🙂

Thank you for the guidance.

Re: Cleaning data to removed non-letter entries.

ballardw — Thu, 12 Jul 2018 15:50:29 GMT

@rob_roche wrote:
These are great questions - what I am trying to do is create a list of US city names, this will become my reference list. I'm trying to bump international names out of a master list of 55 million records. I'm trying to create a table that has a list of pairs of city, county, zip. FORTUNTELY, I found a .txt file online containing this information. However, I was frustrated I couldn't figure out how to clean my data myself - hence the question.

You may want to look and see if you have a data set SASHELP.Zipcode. It should be part of your install. It has US city names (and some alternate names) state codes, zip codes, county name and FIPS number area codes and other information.