How to use user defined functions with PROC FEDSQL?

tomcmacdonald — Fri, 15 Jun 2018 19:08:06 GMT

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?

Re: How to use user defined functions with PROC FEDSQL?

s_lassen — Mon, 18 Jun 2018 11:24:11 GMT

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;

Re: How to use user defined functions with PROC FEDSQL?

tomcmacdonald — Mon, 18 Jun 2018 13:53:51 GMT

This is generating the following error:

ERROR: Access Violation occurred during PREPARE!

topic Re: How to use user defined functions with PROC FEDSQL? in SAS Programming

How to use user defined functions with PROC FEDSQL?

Re: How to use user defined functions with PROC FEDSQL?

Re: How to use user defined functions with PROC FEDSQL?