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!
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..
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;
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;
/* 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;
You might consider something like this. However you can get the same thing with much less work using PROC SUMMARY.
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).
Thank you, this is very interesting, I will look into this for my current problem.
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 ;
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..
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 ;
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..
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 ;
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.
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) ;
You have an extra semi-colon.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.