topic Re: pull the middle value between first and last delimiter in SAS Programming

pull the middle value between first and last delimiter

1239 — Mon, 22 Nov 2021 14:19:56 GMT

Hi,

I am trying to find out is there any way to achieve final output in a better way wherein I need to pull the middle value between first and last delimiter. For example: final = ABC -DEF -IJKL

data test1;
x='014-ABC -DEF -IJKL -20 NOV 2019';
run;

data test2;
set test1;
y = substr(x, 1, index(x, "-"));
yl = length(y)+1;
y6 = substr(x, yl);
y2 = scan(y6, -1, "-");
y2l = length(y2)+1;
y3l = length(y6)-y2l;
final = substr(x, yl, y3l);
run;

Re: pull the middle value between first and last delimiter

Tom — Mon, 22 Nov 2021 14:57:31 GMT

Here are two ways.

One is to parse the string using - as delimiter and re-build the middle part using CATX(). Your messy string with inconsistent use of spaces in addition to hyphens between the words means the result is not exactly what was in the middle of the string.

Second is to use CALL SCAN() to find out where the second word starts and where the last word starts and then take the characters in between.

data test1;
  x='014-ABC -DEF -IJKL -20 NOV 2019';
  length middle1 middle2 $40 ;
  do index=2 to countw(x,'-')-1;
    middle1=catx('-',middle1,scan(x,index,'-'));
  end;
  call scan(x,2,start,len1,'-');
  call scan(x,-1,stop,len2,'-');
  middle2=substrn(x,len1+1,stop-start-1);
  put (_all_) (=/);
run;

Results

x=014-ABC -DEF -IJKL -20 NOV 2019
middle1=ABC-DEF-IJKL
middle2=ABC -DEF -IJKL
index=5
start=5
len1=4
stop=21
len2=11

Re: pull the middle value between first and last delimiter

andreas_lds — Mon, 22 Nov 2021 15:08:00 GMT

untested, using findc

data want;
   set test1;
   s = findc(x, '-');
   e = findc(x, '-', 'b');
   str = substr(x, s+1, e-s-1);
   
   drop s e;
run;

Re: pull the middle value between first and last delimiter

FreelanceReinh — Mon, 22 Nov 2021 16:12:27 GMT

Hi @1239,

Yet another option (more cryptic and probably slower, though) is the PRXCHANGE function:

data want;
set test1;
length final $30; /* specify a sufficient length */
final=ifc(countc(x,'-')>1, prxchange('s/^[^-]*-(.*)-[^-]*$/\1/',1,x), ' ');
run;

Explanation:

If two or more delimiters (here: hyphens) are found in variable X, the substring between the first and last delimiter -- zero or more arbitrary characters (except newline), denoted by .* -- is taken as the value of variable FINAL.
The substring between the beginning of the string (^) and the first hyphen as well as the substring between the last hyphen and the end of the string ($) -- both are sequences of arbitrary characters except the hyphen, denoted by [^-]* -- are not part of FINAL.
The replacement \1 stands for the substring matched by the pattern .* in parentheses. (See Tables of Perl Regular Expression (PRX) Metacharacters for the definitions.)
A single replacement (1 in the second argument of PRXCHANGE) achieves the desired result.
If less than two hyphens are found in X, FINAL is set to a missing value (third argument of the IFC function), but feel free to define FINAL differently in this case or to remove IFC and COUNTC entirely if the condition is not needed for your data.

Re: pull the middle value between first and last delimiter

1239 — Tue, 23 Nov 2021 05:27:22 GMT

Hi, Thanks for your help. Your code also working but I couldn't explore more on PRXCHANGE function.

Re: pull the middle value between first and last delimiter

ChrisNZ — Tue, 23 Nov 2021 06:00:21 GMT

> explore more on PRXCHANGE function.

Like this?

data T;
  A=prxchange('s/[^-]+-(.*)-[^-]+/\1/', 1, 'A-B- C-D-E ');
run;

A = 'B- C-D'