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?
... View more