BookmarkSubscribeRSS Feed
rob_roche
Calcite | Level 5

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?

9 REPLIES 9
Reeza
Super User

NOTALPHA() function.

http://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p15hz07s8zvsywn1q59607pduunb.htm&...

 

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?


 

 

PeterClemmensen
Tourmaline | Level 20
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;
rob_roche
Calcite | Level 5

This seems to have been the most effective - thanks!

PeterClemmensen
Tourmaline | Level 20

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

Astounding
PROC Star

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?

 

 

rob_roche
Calcite | Level 5
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.
Reeza
Super User

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.

 

rob_roche
Calcite | Level 5

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

 

Thank you for the guidance. 

 

ballardw
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1185 views
  • 3 likes
  • 5 in conversation