DATA Step, Macro, Functions and more

IN operator in proc sql with macro

Regular Contributor
Posts: 199

IN operator in proc sql with macro


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;
1 100
2 100
3 200
4 300
5 400
6 500
7 500
8 500
9 500
10 500

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

%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??*/


Super User
Posts: 10,546

Re: IN operator in proc sql with macro

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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,913

Re: IN operator in proc sql with macro

%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.



Respected Advisor
Posts: 3,258

Re: IN operator in proc sql with macro

[ Edited ]

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))


Paige Miller
Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation