topic Re: How do I get regular expressions functions in PROC FEDSQL? in SAS Data Management

How do I get regular expressions functions in PROC FEDSQL?

tomcmacdonald — Thu, 21 Jun 2018 15:19:58 GMT

I am running SAS 9.04.01M3P062415 on UNIX. I do no have predefined packages PCRXMATCH and PCRXFIND available to me. How can I have regular expressions functionality with PROC FEDSQL? The following code fails:

PROC DS2;
	package regexp /overwrite=yes;
		method match(varchar(1024) pattern, varchar(1024) string) returns varchar(1024);
			return prxmatch(pattern, string);
		end;
	endpackage;
	run;
QUIT;


PROC FEDSQL;
	CREATE TABLE foo AS 
	SELECT 
		regexp.match('/[0-9]+/', id) AS test1, 
		regexp.match('/[A-Z]+/', id) AS test 
	FROM someDatabase.someSchema.someTable;
QUIT;

With an error message of:

ERROR: Access Violation occurred during PREPARE!

I can use the above function once in a query but not more than once? How can I get around this?

Re: How do I get regular expressions functions in PROC FEDSQL?

AhmedAl_Attar — Thu, 21 Jun 2018 15:38:24 GMT

Hi @tomcmacdonald

Try this Trick from @SASJedi (Mark Jordan)'s Book Mastering the SAS DS2 Procedure: Advanced Data-Wrangling Techniques, Second Edition, 5.2.2 FCMP Package Example

Hope it helps,

Ahmed

Re: How do I get regular expressions functions in PROC FEDSQL?

ccaulkins912 — Fri, 22 Jun 2018 12:31:43 GMT

hey, friend Ahmed -
don't you think the error message could be more descriptive?

Cheers,

Chad Caulkins

Re: How do I get regular expressions functions in PROC FEDSQL?

AhmedAl_Attar — Fri, 22 Jun 2018 13:40:04 GMT

Hey,

I would agree, let's hope SAS's R&D team are looking at this entry.

Ahmed

Re: How do I get regular expressions functions in PROC FEDSQL?

AnnaBrown — Fri, 22 Jun 2018 18:55:41 GMT

Hi there,

The SAS Data Management team monitors this community, so rest assured it is aware of this request. You can also add an idea to the SASWare Ballot to more officially submit it .

Best,

Anna