BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DangIT
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
DangIT
Fluorite | Level 6

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

18 REPLIES 18
stat_sas
Ammonite | Level 13

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;

DangIT
Fluorite | Level 6

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;

stat_sas
Ammonite | Level 13

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

data_null__
Jade | Level 19

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);
NeilGundel
Calcite | Level 5

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

DangIT
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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 ;

DangIT
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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 ;

DangIT
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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 ;

DangIT
Fluorite | Level 6

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.

DangIT
Fluorite | Level 6

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

Tom
Super User Tom
Super User

You have an extra semi-colon.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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