SAS "More positional parameters found than defined" with proc sql in a macro

1
votes

I am encountering the error "More positional parameters found than defined" for the following code:

%let my_list_of_vars = x, y, z;

%macro sumstats(my_vars);

proc sql;
    create table test2 as
    select distinct &my_vars.
    from my_dataset;
quit;

%mend sumstats;

%sumstats(&my_list_of_vars.);

I'm not quite sure why the proc sql isn't taking in my list of variables "x", "y", and "z", and is instead returning that error. I've looked online and cannot seem to find solutions to this issue. For instance, this website (http://support.sas.com/kb/31/012.html) suggests using %put or %bquote, but neither work. Would much appreciate some guidance.

Thank you.

2
sqldatabasesas
How did you use %bquote, and what went wrong? – Quentin
Thanks!! The answer that Reeza provides below using %bquote is absolutely correct. I realize that I was, incorrectly, defining my variables using %put (instead of %let) and then adding %bquote in the macro function. Correcting the %put to %let has fixed my problem. – TryingtoLearn

2 Answers

1
votes

Your code resolves to the following, so you need to mask the comma's in the macro call. 

%sumstats(x, y, z);

INCORRECT: Try using %str()

%sumstats(%str(&my_list_of_vars.));

EDIT: You have example 2, so %BQUOTE() is correct. 

%sumstats(%BQUOTE(&my_list_of_vars.));

SAS Note regarding this issue and solutions: http://support.sas.com/kb/31/012.html

1
votes

Don't try to use commas as a delimiter in a list of variable names. That is not very useful in normal SAS code and leads to headaches and confusion when trying use them in function calls or macro calls. Instead add the commas when you need them.

%macro sumstats(my_vars);
* Normal SAS code ;
proc sort data=my_dataset(keep=&my_vars) out=test1 nodupkey;
run;

* Make version of list with commas for use in PROC SQL code ;
%local my_varsc ;
%let my_varsc = %sysfunc(compbl(&my_vars));
%let my_varsc = %sysfunc(translate(&my_varsc,',',' '));
proc sql;
  create table test2 as
    select distinct &my_varsc
    from my_dataset
  ;
quit;

%mend sumstats;

%let my_list_of_vars = x y z;
%sumstats(&my_list_of_vars.);