topic Re: More elegant way to extract a substring between two delimiters in SAS Programming

More elegant way to extract a substring between two delimiters

LisaSklar — Thu, 12 Jun 2025 16:14:48 GMT

I am trying to extract a substring from a field that has up to 2000 characters. I need to capture whatever is between "Destination(s):" and "." The following works, but is there a more elegant way to do this? I feel like I'm missing something obvious.

Case
When FIND(t1.Comment_ID,"Destination(s):") > 1 then STRIP(SUBSTR(t1.Comment_ID,FIND(t1.Comment_ID,"Destination(s):")+16,FIND(SUBSTR(t1.Comment_ID,FIND(t1.Comment_ID,"Destination(s):")+16,200),".")-1))
Else ""
End

Thanks,

Lisa

Re: More elegant way to extract a substring between two delimiters

Kathryn_SAS — Thu, 12 Jun 2025 17:16:26 GMT

Another alternative is to use PRXPARSE. Here is an example.

data parse_data;
retain re;
text = 'this is some text. Destination(s): more text.';
if(_N_ = 1) then re = PRXPARSE("/(?<=Destination\(s\):)(.*)(?=\.)/io");
if(prxmatch(re, text) ) then new_text = strip(prxposn(re, 1, text) );
drop re;
run;

proc print;
run;

Re: More elegant way to extract a substring between two delimiters

Tom — Thu, 12 Jun 2025 18:43:42 GMT

One simple thing to make it easier is to use a data step instead of SQL code.

If SQL you could store the location into another variable and use the CALCULATED keyword to reference it.

If you want everything up to a period then use SCAN().

....
  find(t1.Comment_ID,"Destination(s):") as loc
, case when (calculated loc > 0) 
       then left(scan(substr(t1.Comment_ID,calculated loc+16),1,'.'))
       else ' ' 
  end as WANT length=2000

Re: More elegant way to extract a substring between two delimiters

Ksharp — Fri, 13 Jun 2025 01:29:15 GMT

options parmcards=x;
filename x temp;
parmcards;
whatever Destination(s): and and .  The following and works, but is there a mor  Destination(s): or or .  The following 
whatever Destination(s): and and .  The following and works, but is there a mor  Destination(s): or or .  The following 
;

data want;
infile x length=len;
input x $varying2000. len;
n+1;
pid=prxparse('/Destination\(s\):.+?\./i');
s=1;e=length(x);
call prxnext(pid,s,e,x,p,l);
do while(p>0);
 want=scan(substr(x,p,l),2,':.');
 output;
 call prxnext(pid,s,e,x,p,l);
end;
keep n want;
run;

Re: More elegant way to extract a substring between two delimiters

LisaSklar — Fri, 13 Jun 2025 12:35:24 GMT

Thanks, this is along the lines of what I was, it just totally slipped my mind. Appreciate it!

Re: More elegant way to extract a substring between two delimiters

Ksharp — Sat, 14 Jun 2025 07:10:55 GMT

I would like to mention that if you have data like this:

text = 'this is some text. Destination(s): more text.xxxxx.';

And you could get this :

It that what you are looking for?

If I were you ,I would prefer to use Tom 's code .