SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Keep only alphanumeric characters from character string

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Keep only alphanumeric characters from character string

[ Edited ]

Hi there,

 

I have a simple query i want to execute and I'm finding it hard, as I'm new to this. 

 

I have a list off "Agreement" numbers but they have character and numeric values and I just want to extract the characters into a new column. 

 

Example of "Agreement" number:

BAU123455L - I only need BAU

 

below is the code I used but getting no results.

 

if substr(left(Agreement),3) = 'BRN'  

Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 9,840

Re: Help needed

For future note, good idea to post test data (form of a datastep) and required output, so we can see input and output.  Something like:

want=substr(compress(agreement,"","ka"),1,3);

Should work. 

View solution in original post


All Replies
Super User
Super User
Posts: 9,840

Re: Help needed

Something like:

want=compress(agreement,"","ka");

You can find details of the options k and a in the documentation:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212246.htm

New Contributor
Posts: 3

Re: Help needed

Hi RW9,

 

Thank you for your quick response - it works but I only want the first 3 characters - this is bring the letter at the end as well and I just need the first 3 characters?

Solution
3 weeks ago
Super User
Super User
Posts: 9,840

Re: Help needed

For future note, good idea to post test data (form of a datastep) and required output, so we can see input and output.  Something like:

want=substr(compress(agreement,"","ka"),1,3);

Should work. 

New Contributor
Posts: 3

Re: Help needed

Thank you so much RW9.

 

This has helped, in future I will share more info. this was my first time I submitted a question and you guys are very helpful.

 

Thank you again.

PROC Star
Posts: 1,334

Re: Help needed

Glad you got a good answer! Another suggestion...in the future, give your post a meaningful title, something like what RW9 edited it to. It makes it easer to decide whether to take a look at it or not.

Tom

Occasional Contributor
Posts: 14

Re: Keep only alphanumeric characters from character string

Just define a new variable with the function you are using. Check the example below.

 

data test;
	agreement = '  BAU123455L';
	newcol = substr(left(agreement),1,3);
run;
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 130 views
  • 0 likes
  • 4 in conversation