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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 2915 views
  • 0 likes
  • 2 in conversation