PROC SQL: PRSPOSN/PRXPARSE

[Brian]

Hi,

I am trying to run a PRXPOSN/PRXPARSE function within a PROC SQL select statement:

Example:

SELECT TRIM(PRXPOSN(PRXPARSE("/(stringpattern)/"),1,t1.textfield)) as ReturnedString

FROM WORK.TABLE t1;

Using the same pattern in a prxmatch, I am obtaining non-zero values. However, I am not receiving any text from the select statement outlined above.

Any suggestions?

Thanks for your help in advance.

[art297 · Accepted solution]

I found the following in a SAS-L post.  It may provide the code which you could mimic to achieve what you want:

proc sql undo_policy=none;

       *   The regex does a replace with a capture rather than
       *   a straight replace.  I could not figure out how to
       *   make the straight capture work, as it involves
       *   passing a parsed regex id from a prxmatch to prxposn.
       *   This regex matches the whole string, capturing the
       *   contents inside the parens, and replaces the whole
       *   string with the paren contents, and calling the result
       *   paren_contents.  You see an extra set of parens
       *   escaped with '\' because you need to identify them
       *   as strings in your source string, and since they are
       *   special characters in a regex indicating string capture,
       *   they have to be escaped.
       ;
   create table want as
   select
       *,
       prxparse('/.*?\((.*)\).*/') as prxm,
       case when prxmatch(calculated prxm,the_source_string) then
       prxposn(calculated prxm , 1, the_source_string ) else ' '
        end
           as paren_contents
   from
       have
   ;

quit;

[Brian]

Thanks. Splitting up the prxparse and adding calculated fixed the problem. Greatly appreciated. It would be nice for prxposn to include a perl-regex argument similar to to prxchange.