topic Re: Macro for dynamic number of variables in a GROUP BY in SAS Programming

Macro for dynamic number of variables in a GROUP BY

DangIT — Thu, 02 Oct 2014 15:19:01 GMT

Hi,

I've currently have a macro written for a single variable that looks like:

%macro singlevar(var) ;

Proc sql ;

Create table temp as select

&var,

sum(Sales) as Sales

From DataTable

Group by &var

Order by &var

; quit ;

%mend ;

I'm currently lost trying to write the macro to be dynamic with essentially an infinite number of variables to group by.

%macro Multivar(var1, var2, var.etc.) ;

Proc sql ;

Create table temp as select

&var,

&var2,

&var.etc,

sum(Sales) as Sales

From DataTable

Group by &var, &var2, &var.etc

Order by &var, &var2, &var.etc

; quit ;

%mend ;

Thank you in advance for the help!

Re: Macro for dynamic number of variables in a GROUP BY

stat_sas — Thu, 02 Oct 2014 15:47:37 GMT

Wrap up grouping variables in a macro variable and use that in group by and order by something like this.

proc sql;

select NAME

into :vars separated by ','

from SASHELP.VCOLUMN

where LIBNAME="SASHELP"

and MEMNAME="CARS"

and name in ('Make','Origin');

quit;

proc sql;

select &vars,sum(msrp) as sales

from sashelp.cars

group by &vars

order by &vars;

quit;

Re: Macro for dynamic number of variables in a GROUP BY

DangIT — Thu, 02 Oct 2014 16:21:26 GMT

Great thank you for the quick reply!

How would it work if I wanted to put in a criteria? when I add

Where &vars not in ('')

it will give me an error in this case.

proc sql;

select &vars,sum(msrp) as sales

from sashelp.cars

Where &vars not in ('')

group by &vars

order by &vars;

quit;

Re: Macro for dynamic number of variables in a GROUP BY

data_null__ — Thu, 02 Oct 2014 16:36:34 GMT

You might consider something like this. However you can get the same thing with much less work using PROC SUMMARY.

%macro ex(arg,arg2); 
   %let arg = %sysfunc(scan(%superq(arg),1,%str(%)%()));
   %put NOTE: &=arg;
   proc sql;
      create table a
         as select &arg, &arg2
         from sashelp.class
         group by &arg
         order by &arg
         ;
      quit;
      run;
   %mend ex;
options mprint=1; 
%ex((age,sex),sum(weight) as sum_weight);

Re: Macro for dynamic number of variables in a GROUP BY

NeilGundel — Thu, 02 Oct 2014 20:45:41 GMT

You can actually use the PARMBUF option in the macro declaration, and pass an arbitrary number of parameters, which you loop through in the macro body. See attached. (for some reason I cannot paste into this comment, or I would do so).

Re: Macro for dynamic number of variables in a GROUP BY

DangIT — Fri, 03 Oct 2014 16:08:06 GMT

Thank you, this is very interesting, I will look into this for my current problem.

Re: Macro for dynamic number of variables in a GROUP BY

Tom — Fri, 03 Oct 2014 23:20:50 GMT

Personally I use space delimited lists of variables. Not only do they not confuse the macro calls they are more useful in normal SAS statements (like VAR, TABLES, KEEP , DROP etc.). If you need to use the list in SQL code with its insistence on using commas as delimiters than just convert the value inside the macro rather than forcing the macro user to insert the commas.

%macro multivar(varlist) ;

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

proc sql NOPRINT;

create table temp as

select &varlist

, sum(sales) as sales

from datatable

group by &varlist

order by &varlist

;

quit;

%mend ;

Re: Macro for dynamic number of variables in a GROUP BY

DangIT — Fri, 03 Oct 2014 23:38:08 GMT

Thanks for the reply Tom.

In this case how would you include conditions on the varlist such as:

Where var1 is not null and var2 is not null etc.

I suppose its like breaking the varlist down into individual variables..

Re: Macro for dynamic number of variables in a GROUP BY

Tom — Sat, 04 Oct 2014 00:01:57 GMT

TRANWRD() works well for that also (if the list if not too long).

%let varlist=var1 var2;

%put where %sysfunc(tranwrd(&varlist,%str( ),%str( is not null AND ))) is not null ;

Re: Macro for dynamic number of variables in a GROUP BY

Tom — Sat, 04 Oct 2014 00:10:11 GMT

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

Re: Macro for dynamic number of variables in a GROUP BY

stat_sas — Sat, 04 Oct 2014 02:07:22 GMT

/* Group/order variables */

proc sql;
select NAME
into    :grp separated by ','
from    SASHELP.VCOLUMN
where   LIBNAME="SASHELP"
and   MEMNAME="CARS"
and name in ('Make','Origin');
quit;

/* Where variables */

proc sql;
select NAME
into    :filter separated by ' is not null and '
from    SASHELP.VCOLUMN
where   LIBNAME="SASHELP"
and   MEMNAME="CARS"
and name in ('Make','Origin');
quit;

proc sql;
select &vars,sum(msrp) as sales
from have
where &filter is not null
group by &grp
order by &grp;
quit;

Re: Macro for dynamic number of variables in a GROUP BY

DangIT — Wed, 08 Oct 2014 20:18:50 GMT

The Tranwrd() solution worked great!.. but of course I want to complicate things further...

I want to add a Proc Sql step to JOIN tables based on the variables in the varlist.

In this case it will require the string to output the following:

on T1.&var1=T2.&var1 and T1.&var2=T2.&var2

I'm trying to use tranwrd() again to get this, but I think it requires an additional loop..

Re: Macro for dynamic number of variables in a GROUP BY

Tom — Wed, 08 Oct 2014 22:38:46 GMT

You might need to have a %DO loop .

%let prefix=ON ;

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

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

%let prefix=AND ;

%end ;

Re: Macro for dynamic number of variables in a GROUP BY

DangIT — Thu, 09 Oct 2014 16:48:45 GMT

I'm having troubles incorporating this into my macro's proc sql statement. I'm not sure where i'm going wrong with this

Example code:

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

From the log, I get the error "Found "Order" when expecting ON, so it seems like the's skipping the whole section of the loop.

Re: Macro for dynamic number of variables in a GROUP BY

DangIT — Thu, 09 Oct 2014 16:52:40 GMT

it works beautifully when i try it alone :

%macro Words(Word_list);

%let prefix=ON ;

%do i=1 %to %sysfunc(countw(&Word_List));

%put &&prefix T1.%scan(&Word_List,&i) = T2.%scan(&Word_List,&i) ;

%let prefix=AND ;

%end ;

%mend ;

%words(var1 var2) ;

Re: Macro for dynamic number of variables in a GROUP BY

Tom — Thu, 09 Oct 2014 17:33:17 GMT

You have an extra semi-colon.

Re: Macro for dynamic number of variables in a GROUP BY

DangIT — Thu, 09 Oct 2014 17:48:08 GMT

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

Re: Macro for dynamic number of variables in a GROUP BY

Tom — Thu, 09 Oct 2014 18:12:24 GMT

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(,)));

Re: Macro for dynamic number of variables in a GROUP BY

DangIT — Thu, 09 Oct 2014 18:15:52 GMT

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