BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ela_84
Fluorite | Level 6

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'  
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Ela_84
Fluorite | Level 6

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

Ela_84
Fluorite | Level 6

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.

TomKari
Onyx | Level 15

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

smicha
Obsidian | Level 7

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;

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 15370 views
  • 2 likes
  • 4 in conversation