I need regular expressions to validate a particular column and can't use PRXMATCH in PROC FEDSQL. My solution has been to wrap it in a PROC DS2 package like this:
proc ds2 scond=error;
package regexp / overwrite=yes;
method match( varchar re, varchar string ) returns integer;
return prxmatch(re, string) > 0;
end;
endpackage;
run;
quit;
And testing it on a sample dataset:
data foo;
input id $12.;
datalines;
20467400081
;
run;
proc fedsql;
drop table bar force;
select
id,
regexp.match('/^[1-5](?!00000)\d{5}0(?!0000)\d{4}\w?(?:\s*)$/', id) as is_valid
into bar
from foo;
quit;
I get the following note:
NOTE: Argument 1 to function prxmatch is invalid.
Clearly the regular expression is valid. So just running a test function to see what's going on:
proc ds2 scond=error;
package testing / overwrite=yes;
method echo ( varchar string ) returns varchar;
return string;
end;
endpackage;
run;
quit;
proc fedsql;
drop table baz force;
select
id,
testing.echo('/^[1-5](?!00000)\d{5}0(?!0000)\d{4}\w?(?:\s*)$/') as test
into baz
from foo;
quit;
It creates this dataset:
20467400081 /^[1-5](
So it is cutting it off at 8 characters.
How can I tell SAS that the quoted string is a varchar and not a char? In other words how can I use a string parameter for a function that exceeds 8 characters?
It seems to work (although I do not understand your PRX string, but anyway I did not get an error message) if you change the input parameter definition of your package function, e.g.:
proc ds2 scond=error;
package regexp / overwrite=yes;
method match( varchar(50) re, varchar(50) string ) returns integer;
return prxmatch(re, string) > 0;
end;
endpackage;
run;
quit;
This is generating the following error:
ERROR: Access Violation occurred during PREPARE!
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.