DATA Step, Macro, Functions and more

How to use user defined functions with PROC FEDSQL?

Reply
Frequent Contributor
Posts: 101

How to use user defined functions with PROC FEDSQL?

[ Edited ]

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? 

 

PROC Star
Posts: 266

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

Posted in reply to tomcmacdonald

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;
Frequent Contributor
Posts: 101

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

This is generating the following error:

 

ERROR: Access Violation occurred during PREPARE!

 

 

Ask a Question
Discussion stats
  • 2 replies
  • 90 views
  • 0 likes
  • 2 in conversation