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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.