BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kishore415
Calcite | Level 5
Hi All..

I want to extract the substring from a string till the last occurrence of my delimiter, for example a dataset has values a below..

str
A
A,B
A,B,C
A,B,C,D


I want to extract the substring till the last occurence of ',' in the above dataset.the resultant dataset should look like ...

str
A
A
A,B
A,B,C

Thanks in advance..
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

Just another variation:

data input; 
input str $; 
datalines;
A
A,B
A,B,C
A,B,C,D
;
run;

data want;
 set input;
 SubStrLength=findc(str, ',',-length(str)) ;
 if SubStrLength=0 then WantStr=Str;
  else WantStr=substr(str,1,SubStrLength-1);
 drop SubStrLength;
run;

proc print;
run;

And this from @cidab:

 

data  input;
 input str $;
 want = substr(str,1,length(str)-indexc(reverse(trim(str)),','));    
...

  **trim the input string, reverse it and find the position of last (now first) comma, then substring input string from start to string length minus the number of characters to drop;

View solution in original post

9 REPLIES 9
GertNissen
Barite | Level 11
Two way of doing it...

[pre]
data input;
input str $;
datalines;
A
A,B
A,B,C
A,B,C,D
;
run;

data extract1(drop=_str_length _i _comma_pos);
set input;
_str_length = length(str);
do _i = 1 to _str_length;
if substr(str,_i,1) = ',' then _comma_pos=_i-1;
end;
ext=substr(str,1,max(_comma_pos,1));
output;
run;

data extract2(drop=_last_element);
set input;
_last_element=scan(str,-1);
ext=tranwrd(str,cats(',',_last_element),'');
run;
[/pre]
ballardw
Super User
SCAN may be better for your approach. You can specify a list of delimiters if something other than the defaults and to search backwards, the -1 says start at the end and find the first "word" counting backwards.

result= scan(str,-1);
kishore415
Calcite | Level 5
Dear ballardw

scan with -1 will give last word but I want remaining words excepting last word.... Message was edited by: kishore415
Patrick
Opal | Level 21

Just another variation:

data input; 
input str $; 
datalines;
A
A,B
A,B,C
A,B,C,D
;
run;

data want;
 set input;
 SubStrLength=findc(str, ',',-length(str)) ;
 if SubStrLength=0 then WantStr=Str;
  else WantStr=substr(str,1,SubStrLength-1);
 drop SubStrLength;
run;

proc print;
run;

And this from @cidab:

 

data  input;
 input str $;
 want = substr(str,1,length(str)-indexc(reverse(trim(str)),','));    
...

  **trim the input string, reverse it and find the position of last (now first) comma, then substring input string from start to string length minus the number of characters to drop;

kishore415
Calcite | Level 5
Dear Patrick...

Your code looks simple and works..thanks a lot...
Ksharp
Super User
[pre]


data input;
input str $20.;
position=findc(str,',','b');
if position=0 then want=str;
else want=substr(str,1,position-1);
datalines;
A
A,Brertyr
A,B,Ce
A,B,C,Ddsd
;
run;
[/pre]


Ksharp
cidab
Calcite | Level 5

data  input;

input str $;

want = substr(str,1,length(str)-indexc(reverse(trim(str)),','));    

  **trim the input string, reverse it and find the position of last (now first) comma, then substring input string from start to string length minus the number of characters to drop;

NathanOch
Obsidian | Level 7

cidab has a great solution. I've used this a couple times now to only extract what I need from a string.

 

Nathan Och

arpi
Fluorite | Level 6

data input_extract;
set input;

len = length(str);

do i =1 to len;
len1 = len -2;
sub_str = substr(str,1,len1);
end;
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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 29790 views
  • 1 like
  • 8 in conversation