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.
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).
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.