topic Re: GROUP BY a macro variable list (except one variable) in SAS Programming

GROUP BY a macro variable list (except one variable)

x2PSx — Tue, 21 Aug 2018 19:03:16 GMT

Hello everyone,

I have an SQL procedure in which I want to aggregate data with a group by procedure. I use a macro variable list because the level of aggregation needed might change. However, there is always one variable that I want to exclude from that list at that step and I don't know how to proceed.

What I have right now:

%LET CATEG = %str(VAR1, VAR2, VAR3, EXCL);
%LET CATEG2 = %str(VAR1, VAR2, VAR3);
proc sql noprint;
	create table WANT as
		select  &CATEG.,
				sum(Nobs_last) as CAT_Nobs_last,
				sum(Nobs) as CAT_Nobs

			from HAVE
				group by &CATEG2.
				order by &CATEG.;
quit;

What I would like to have:

%LET CATEG = %str(VAR1, VAR2, VAR3, EXCL);
proc sql noprint;
	create table WANT as
		select  &CATEG.,
				sum(Nobs_last) as CAT_Nobs_last,
				sum(Nobs) as CAT_Nobs

			from HAVE
				group by &CATEG. [with an function that let me exclude the var EXCL]
				order by &CATEG.;
quit;

Thank you very much for your time, Cheers!

Re: GROUP BY a macro variable list (except one variable)

ballardw — Tue, 21 Aug 2018 20:23:39 GMT

I would recommend building a new macro variable with something like:

%let categ2 = %sysfunc(tranwrd(&categ.,%str(, EXCL), ));
%put &categ2.;

WARNING: The above is CASE SENSITIVE and if you use Excl EXcl or others it will fail. This can be addressed by %upcase your categ variable. variable names in SAS aren't case sensitive so no issue with that.

WARNING: The above is also space sensitive. If you do not always include ", EXCL" with one space it will fail.

I would create a different variable instead of attempting an inline "function", whatever that might have been, because you can get the value of a macro variable when you need to debug something.

Re: GROUP BY a macro variable list (except one variable)

x2PSx — Tue, 21 Aug 2018 20:33:44 GMT

Exactly what I needed, many thanks!