topic Re: Debugging PROC DS2 and PROC FEDSQL in SAS Programming

Debugging PROC DS2 and PROC FEDSQL

tomcmacdonald — Mon, 18 Jun 2018 13:58:15 GMT

Are there any debugging options I can set? I'm having trouble incorporating a PROC DS2 package into PROC FEDSQL receiving the following cryptic error message:

ERROR: Access Violation occurred during PREPARE!

Not really sure how to proceed with this.

Re: Debugging PROC DS2 and PROC FEDSQL

tomcmacdonald — Mon, 18 Jun 2018 14:12:38 GMT

Trying out the PUTLOG statement:

http://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n05jha8tsrpanyn19m05ss7ltv6e.htm&docsetVersion=9.4&locale=en

Something like this:

proc ds2 scond=note;
	package regexp / overwrite=yes;
		method match( char pattern, char string ) returns integer;
			putlog pattern;
			putlog string;
			return prxmatch(trim(pattern), string);
		end;
	run;
quit;

I'm thinking SAS will treat each paramter as a string and print it to the log put that doesn't work. I'm getting this error message:

24         proc ds2 scond=note;
25         	package regexp / overwrite=yes;
26         		method match( char pattern, char string ) returns integer;
27         			putlog pattern;
28         			putlog string;
29         			return prxmatch(pattern, string);
30         		end;
31         	run;
ERROR: Compilation error.
ERROR: Parse encountered identifier when expecting one of: '=' '-' '+'.
ERROR: Line 27: Parse failed: putlog  >>> pattern <<< ;

Re: Debugging PROC DS2 and PROC FEDSQL

tomcmacdonald — Mon, 18 Jun 2018 14:20:41 GMT

OK how about just regular put? Will that print to log when executing the function? Something like this:

proc ds2 scond=note;
	package regexp / overwrite=yes;
		method match( char pattern, char string ) returns integer;
			put pattern;
			return prxmatch(pattern, string);
		end;
	run;
quit;

OK it compiles but doesn't print anything to the log when I invoke the function. So that didn't work either.

I really thought this one would work looking at this help file on page 2.

http://support.sas.com/resources/papers/proceedings17/0916-2017.pdf

Re: Debugging PROC DS2 and PROC FEDSQL

tomcmacdonald — Mon, 18 Jun 2018 14:49:10 GMT

Trying out this code:

proc ds2;
	package regexp / overwrite=yes;
		method match( char pattern, char string ) returns integer;
			return prxmatch(pattern, string);
		end;
	run;
quit;


data raw;
	input id $11.;
	datalines;
10000100002
10000000002
;
run;


PROC FEDSQL;
	DROP TABLE example FORCE;
  CREATE TABLE example (
    is_valid INTEGER
  );
QUIT;


PROC FEDSQL;
  INSERT INTO example
  SELECT regexp.match('/\d+/', id)
  FROM raw;
QUIT;

So I should be see a 1 for each row of raw in example. It's working just fine on my regular expressions utility. However regular expressions fails to find a match. I'm getting:

0
0

Which is clearly incorrect.

Re: Debugging PROC DS2 and PROC FEDSQL

tomcmacdonald — Mon, 18 Jun 2018 14:59:31 GMT

OK this however works:

PROC FEDSQL;
  INSERT INTO example
  SELECT regexp.match('/[0-9]+/', id)
  FROM raw;
QUIT;

So SAS regular expressions isn't strictly compliant with the PERL regular expression standard. Looks like shorthands like \d and \w are out unfortunately.

edit:

That isn't true. I wasn't escaping the backslash character. This works. Any way for SAS to interpret parameters as raw strings?

PROC FEDSQL;
  INSERT INTO example
  SELECT regexp.match('/\\d+/', id)
  FROM raw;
QUIT;

Re: Debugging PROC DS2 and PROC FEDSQL

tomcmacdonald — Mon, 18 Jun 2018 15:21:44 GMT

Still cannot solve the problem of logging a parameter from a PROC DS2 package. The put statement simply does not work.

proc ds2;
	package regexp / overwrite=yes;
		method match( varchar(1024) pattern, char string ) returns integer;
			put 'foobar';
			return prxmatch(pattern, string);
		end;
	run;
quit;

What's disappointing about this is I'm reading an example from Mastering the SAS DS2 Procedure: Advanced Data Wrangling Techniques on page 62 with a similar user defined package with put statements.

Re: Debugging PROC DS2 and PROC FEDSQL

SASJedi — Mon, 25 Jun 2018 12:17:25 GMT

If you create a package, it is just stored on disk. None of the methods will execute until you instantiate the package and call the method from a DATA program. For example, I think this produces the result you're looking for:

proc ds2;
	package regexp / overwrite=yes;
		method match( varchar(1024) pattern, char string ) returns integer;
			put 'foobar';
			return prxmatch(pattern, string);
		end;
   endpackage;
	run;
   data _null_;
      dcl package regexp r();
      method init();
         dcl int rc;
         rc=r.match('/\d+/','10000100002');
         put rc=;
     end;         
   enddata;
   run;
quit;

the result:

57      data _null_;
58         dcl package regexp r();
59         method init();
60            dcl int rc;
61            rc=r.match('/\d+/','10000100002');
62            put rc=;
63        end;
64      enddata;
65      run;
foobar
rc=1
NOTE: Execution succeeded. No rows affected.