topic Re: IN operator in proc sql with macro in SAS Programming

IN operator in proc sql with macro

Ronein — Sun, 05 Aug 2018 08:53:24 GMT

Hello

May you please look at the following code and tell what is wrong with it?

The error is

ERROR: All positional parameters must precede keyword parameters.

Data tbl;
input ID  branch;
cards;
1 100
2 100
3 200
4 300
5 400
6 500
7 500
8 500
9 500
10 500
;
run;

%macro rjoe(x,branchP);
PROC SQL;
	create table outcome_&x. as
	select *	   
	from tbl
	where branch in (&branchP.)
;
QUIT;
%mend;

%rjoe(x=1,branchP=100);
%rjoe(x=2,branchP=200);
%rjoe(x=3,branchP=300);
%rjoe(x=4,branchP=400);
%rjoe(x=5,branchP=500);
%rjoe(x=6,branchP=100 200 300 400 500);/*It is working well*/
%rjoe(x=6,branchP=100, 200, 300, 400, 500);/*It is  not working .why??*/

Re: IN operator in proc sql with macro

Kurt_Bremser — Sun, 05 Aug 2018 09:03:36 GMT

You have no keyword parameters in your macro definition (only positional ones), but supply keyword parameters in the call. Make up your mind how you want to call your macro.

Re: IN operator in proc sql with macro

ballardw — Mon, 06 Aug 2018 16:43:05 GMT

%rjoe(x=6,branchP=100, 200, 300, 400, 500);

Has a major issue in that you defined the macro with two variables. But since the comma is the macro delimiter in a parameter list you have 4 extra parameters: 200 300 400 500.

Since the code you are using with the IN operator does not require a comma then use

%rjoe(x=6,branchP=100 200 300 400 500);/

The specific error you get comes from the macro parser. From the documentation: Note: You can define an unlimited number of parameters. If both positional and keyword parameters appear in a macro definition, positional parameters must come first.

I added emphasis. Since you had comma delimited items after the other x= and branch = the parser saw positional parameters after the keyword parameters. If you had used:

%rjoe(6,100, 200, 300, 400, 500);

You would have received an error of "more positional parameters found than defined" or similar.

Re: IN operator in proc sql with macro

PaigeMiller — Mon, 06 Aug 2018 17:06:45 GMT

Commas are interpreted as delimiters of the arguments in the call to the macro. The %STR function allows the commas to be treated as text instead of delimeters.

%rjoe(x=6,branchP=%str(100, 200, 300, 400, 500))