DATA Step, Macro, Functions and more

Macro for dynamic number of variables in a GROUP BY

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

Macro for dynamic number of variables in a GROUP BY

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!


Accepted Solutions
Solution
‎10-03-2014 07:38 PM
Frequent Contributor
Posts: 81

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

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

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

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;

Frequent Contributor
Posts: 81

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

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;

Trusted Advisor
Posts: 1,228

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

/* 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;

Respected Advisor
Posts: 3,799

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

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);
N/A
Posts: 1

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

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

Attachment
Frequent Contributor
Posts: 81

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

Posted in reply to NeilGundel

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

Super User
Super User
Posts: 7,059

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

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 ;

Solution
‎10-03-2014 07:38 PM
Frequent Contributor
Posts: 81

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

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

Super User
Super User
Posts: 7,059

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

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 ;

Frequent Contributor
Posts: 81

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

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

Super User
Super User
Posts: 7,059

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

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 ;

Frequent Contributor
Posts: 81

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

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.

Frequent Contributor
Posts: 81

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

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

Super User
Super User
Posts: 7,059

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

You have an extra semi-colon.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 1117 views
  • 7 likes
  • 5 in conversation