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!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: