DATA Step, Macro, Functions and more

Extracting substring using scan or any function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Extracting substring using scan or any function

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

Accepted Solutions
Solution
a week ago
Respected Advisor
Posts: 3,887

Re: Extracting substring using scan or any function

[ Edited ]

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


All Replies
SAS Employee
Posts: 160

Re: Extracting substring using scan or any function

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]
Super User
Posts: 10,466

Re: Extracting substring using scan or any function

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);
Occasional Contributor
Posts: 14

Re: Extracting substring using scan or any function

Dear ballardw

scan with -1 will give last word but I want remaining words excepting last word.... Message was edited by: kishore415
Solution
a week ago
Respected Advisor
Posts: 3,887

Re: Extracting substring using scan or any function

[ Edited ]

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;

Occasional Contributor
Posts: 14

Re: Extracting substring using scan or any function

Dear Patrick...

Your code looks simple and works..thanks a lot...
Super User
Posts: 9,662

Re: Extracting substring using scan or any function

[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
N/A
Posts: 1

Re: Extracting substring using scan or any function

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;

Contributor
Posts: 33

Re: Extracting substring using scan or any function

[ Edited ]

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

 

Nathan Och

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 9111 views
  • 1 like
  • 7 in conversation