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

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

2 REPLIES 2
ballardw
Super User

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.

x2PSx
Calcite | Level 5
Exactly what I needed, many thanks!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 2887 views
  • 0 likes
  • 2 in conversation