topic Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE in SAS Programming

Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

rhachey — Mon, 01 Apr 2019 19:42:54 GMT

Hello, I am trying to use a macro within CALL EXECUTE. The source data for the macro is a data set with two fields: Group and Employer. Group never has any special characters, but Employer may have an apostrophe. When I run the following code, I get the following error:

ERROR: Expected close parenthesis after macro function invocation not found.

This is a simplified version of my macro:

%macro test (Group,Employer); 
proc sql; 
create table test as
select distinct
	MemberID
	,"&Employer" as Employer
	,"&Group" as Group
from Membership
where EmployerID like "&Group%" 
	and Date between "01Jan2017"d and "31Dec2017"d 
;quit; 
%mend;

This is the CALL EXECUTE code:

data _null_;
	set work.list;
	call execute('%nrstr(%deductible(
		 '||strip(Group)||'
		,'||strip(Employer)||'
	);)');
run;

As long as there are no special characters in the Employer field, my code runs without problem. The macro runs without problem, even with apostrophes, with:

%test (123456,%bquote(O'Test Employer));

So I tried to use:

data _null_;
	set work.list;
	call execute('%nrstr(%deductible(
		 '||strip(Group)||'
		,%bquote('||strip(Employer)||')
	);)');
run;

How can I get SAS to ignore any apostrophes that my come up in the Employer field? Any help is appreciated, thank you!

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

Shmuel — Mon, 01 Apr 2019 19:56:35 GMT

Have you tried %NRBQUOTE instead %BQUOTE ?

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

rhachey — Mon, 01 Apr 2019 20:01:31 GMT

Yes, and I still get the same error.

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

PaigeMiller — Mon, 01 Apr 2019 20:05:26 GMT

Okay, let's start with the absolute basic fundamentals here.

You write a macro called %TEST, but then in CALL EXECUTE you are trying to run a macro called %DEDUCTIBLE.

Could this be the problem?

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

Tom — Mon, 01 Apr 2019 20:06:48 GMT

One easy way is to allow your macro to accept actual quotes.

For example this macro will except values with or without quotes around them.

%macro test (Group,Employer);
%let group=%sysfunc(quote(%qsysfunc(dequote(%superq(group))),%str(%')));
%let employer=%sysfunc(quote(%qsysfunc(dequote(%superq(employer))),%str(%')));
proc sql;
create table test as
select distinct
   MemberID
  ,&Employer as Employer length=20
  ,&Group as Group length=20
from Membership
where EmployerID like &Group||'%'
  and Date between "01Jan2017"d and "31Dec2017"d
;
quit;
%mend;

Then call the macro with quoted values.

data _null_;
  set work.list;
  call execute(cats('%nrstr(%deductible)'
,'(',quote(trim(group),"'")
,',',quote(trim(employer),"'")
,')'
));
run;

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

PaigeMiller — Mon, 01 Apr 2019 20:09:37 GMT

Next thought

Instead of %NRBQUOTE or %BQUOTE, I think you really want %NRSTR to handle apostrophe's and other special characters. If it is a single apostrophe, rather then a pair, you might have to precede it with % as stated here: https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=mcrolref&docsetTarget=n09tblrxldh8k0n1kt6dkj3xlxug.htm&locale=en

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

Tom — Mon, 01 Apr 2019 20:11:56 GMT

You can avoid a lot of the timing issues with CALL EXECUTE by just writing to a file and using %INCLUDE instead.

filename code temp;
data _null_;
  set work.list;
  file code ;
  put '%deductible(%bquote(' group +(-1) '),%bquote(' employer +(-1) '));';
run;
%include code / source2;

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

Astounding — Mon, 01 Apr 2019 20:18:40 GMT

A simple workaround would be to write the macro calls to a file instead of using CALL EXECUTE:


file mcalls temp;

data _null_;
  set list;
  file mcalls;
  put '%deductible(' strip(Group) ',' strip(Employer) ')';
run;

%include mcalls;

Note that the final semicolon is not necessary when calling a macro, and occasionally can harm the program's logic.

If the STRIP function causes problems, you should be able to just remove it:

put '%deductible(' group ',' employer ')';

If the single quotes cause problems, you can add %bquote back in:

put '%deductible(' group ', %bquote(' employer '))';

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

rhachey — Thu, 04 Apr 2019 16:34:49 GMT

Sorry, in trying to simplify my code to post here, I forgot to change the %DEDUCTIBLE to %TEST in my post.

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

rhachey — Thu, 04 Apr 2019 16:56:29 GMT

Thank you, this works! But I don't understand how/why... can you explain the code a bit, or point me in the direction of resources? Especially the +(-1) piece. Thanks!

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

Tom — Thu, 04 Apr 2019 17:30:01 GMT

The FILENAME statement will create a fileref to use for a temporary file.

The data step then writes to the file.

The %INCLUDE statement will execute the code in the file.

The data step reads the data and writes one line per observation using a simple PUT statement. The quoted text is written as is. The variable names reference variables in the input dataset. The pointer motion command removes the extra space that PUT adds when you write a variable in list mode (without a format specification). It does this by moving the pointer back one space so that the next thing written overwrites the space character.

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

rhachey — Thu, 04 Apr 2019 17:33:10 GMT

Very helpful, thank you!

Re: Getting an error when using a macro with paramaters with single quotes in CALL EXECUTE

Tom — Thu, 04 Apr 2019 17:46:24 GMT

The pointer motion is only needed in this case because of the use of the %BQUOTE() function in the generated code. That would quote the trailing space making it part of the value passed to the macro. Since I don't have your macro I am not sure if that extra space would make a difference. Normally in generated SAS code an extra space is not an issue.

If you name your macro parameters after your dataset variables (or visa versa) then PUT statement is even easier. So if your macro had two parameters named VAR1 and VAR2 then your metadata dataset should have two variables named VAR1 and VAR2.

So a program like this:

data _null_;
 set metadata ;
 file code;
 put '%macro_name('  var1= ',' var2= ')';
run;

Will create code like:

%macro_name(var1=ABC ,var2=xyz )
%macro_name(var1=DEF ,var2=123 )

Remember that even if you define your macro to allow the parameters to be specified by position only (like: %my_macro(ABC,xyz) ) you can still use the parameter names in the call to the macro (like: %macro_name(var1=ABC ,var2=xyz ) ).