Macro for dynamic number of variables in a GROUP BY

[DangIT]

I'm not sure that's it.. I'm still getting the same error:

%macro Test(variables) ;

%let varlist=%sysfunc(tranwrd(%sysfunc(compbl(&variables)),%str( ),%str(,)));

PROC SQL ;

Create table Table3 as Select

     T1.*,

     T2.temp

FROM Table1 as T1

LEFT JOIN Table2 as T2

%let prefix=ON ;

%do i=1 %to %sysfunc(count(&varlist));

     &prefix T1.%scan(&varlist,&i) = T2.%scan(&varlist,&i)

     %let prefix=AND ;

%end ;

Order by &varlist

; quit ;

%mend ;

%test(var1 var2) ;

[Tom]

You have changed the value of VARLIST to be the version with comma. So you need to protect the commas from being read by the %SCAN() function.  You could add quoting.

%let x=a,b ;

%put %scan(%superq(x),1);

Or you could just use two different macro variables for the list with and without commas.

%local varlistc ;

%let varlistc=%sysfunc(tranwrd(%sysfunc(compbl(&variables)),%str( ),%str(,)));

[DangIT]

Thank you very much Tom, i've gotten the code to work.

[Tom]

For your example application SQL is probably not the preferred solution.  Use PROC SUMMARY.

%macro sum(classvars,sumvars,in,out);

proc summary nway data=&in ;

  class &classvars;

  var &sumvars ;

  output out=&out sum= ;

run;

%mend sum;

%sum(classvars=sex age,sumvars=height weight,in=sashelp.class,out=sum1);