regexp_substr pick out gibberish names in text field

agrocrag14 — Mon, 02 May 2016 21:40:38 GMT

I am trying to pick out gibberish names (typically a string of consonants) out of a name field I will call TEXT3. I was passed the REGEXP_SUBSTR function by a colleague and got the function to work on another data set. The field I am applying to in my data set is a text field char $40. For some reason I can't get it to work:

proc sql;

create table check_susp_names as

select TEXT3, case

when REGEXP_SUBSTR(trim(TEXT3),'(m)c{1,2}+',1,1,'i') is not null then 0

when REGEXP_SUBSTR(trim(TEXT3),'[bcdfghjklmnpqrstvwxzBCDFGHJKLMNPQRSTVWXZ]{4}+',1,1,'i') is not null then 1

when REGEXP_SUBSTR(trim(TEXT3),'(q)[^u]+',1,1,'i') <>'' then 1

else 0

end as Susp_Ind

from dataset_a;quit;

the error I get is:

ERROR: Function REGEXP_SUBSTR could not be located.

Re: regexp_substr pick out gibberish names in text field

FreelanceReinh — Tue, 03 May 2016 11:38:20 GMT

Hello @agrocrag14,

REGEXP_SUBSTR is an Oracle function, as is explained in this paper. So, you can use it in queries against an Oracle database using the PROC SQL Pass-Through facility, but it is not applicable to SAS datasets. Use PRX functions instead (see references in the paper or the documentation).

topic Re: regexp_substr pick out gibberish names in text field in SAS Enterprise Guide

regexp_substr pick out gibberish names in text field

Re: regexp_substr pick out gibberish names in text field