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!
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.
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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.