topic Re: how to build a set of macro variables to store the max value for each group in SAS Programming

how to build a set of macro variables to store the max value for each group

stataq — Wed, 02 Oct 2024 00:43:05 GMT

Hi all,

I am trying to get max value by group and assign it to a set of macro variables. Can anyone please help?

My codes were able to get the max value for each group.(might not be a smart way. If you know a better way, please share your thoughts. thanks.) but how can create macro variables base on the groups we have?

data have;
input group $  value;
datalines;
g1 30
g2 37
g2 76
g3 22
g3 84
g4 15
g4 21
g4 94
g4 73
;
run;

proc sort;
by group descending value  ;
run; 
data want (keep= group max_v);
   set have;
   by group;
   retain max_v;
   if first.group then max_v= value;
run;

proc sort nodupkey;
by _all_;
run;

here we have 4 group, the ideal macro variables are

%let grp1=30; %let grp2=76;  %let grp3=84; %let grp4=94;

Is there a smart way to build those macro variables automatically?

Re: how to build a set of macro variables to store the max value for each group

AhmedAl_Attar — Wed, 02 Oct 2024 01:35:14 GMT

Try this

data have;
input group $  value;
datalines;
g1 30
g2 37
g2 76
g3 22
g3 84
g4 15
g4 21
g4 94
g4 73
;
run;

proc sql noprint;
	select max(value)
	into :grp1-
	from have
	group by group;
quit;

Re: how to build a set of macro variables to store the max value for each group

stataq — Wed, 02 Oct 2024 02:14:59 GMT

@AhmedAl_Attar Thanks for the prompt answer. What if there is null value in group? How to assign macro variable only for non-null group?

data have; input group $ value; datalines; g1 30 g2 37 g2 76 g3 22 g3 84 g4 15 g4 21 94 g4 73 ; run;

Re: how to build a set of macro variables to store the max value for each group

Kurt_Bremser — Wed, 02 Oct 2024 07:11:36 GMT

Use a WHERE clause in the SQL.

Re: how to build a set of macro variables to store the max value for each group

Kurt_Bremser — Wed, 02 Oct 2024 08:02:59 GMT

proc means data=have;
class group;
var value;
output out=want max()=;
run;

data _null_;
set want;
call symputx(group,value);
run;

But I seriously question the need for macro variables; for all purposes I see, the want dataset is better suited.

What are you intending to do with those macro variables?

Re: how to build a set of macro variables to store the max value for each group

AhmedAl_Attar — Wed, 02 Oct 2024 09:50:10 GMT

Hi @stataq

Typically, the missing value would be considered as a fifth grouping value, and the below SQL statement would generate 5 macros (grp1 -- grp5), where grp1 represents the null/missing value.

proc sql noprint;
	select max(value)
	into :grp1-
	from have
	/*Where group is not null*/ /* This would ignore null/missing values */
	group by group;
quit;
%put _user_;

But if you want to exclude the null/missing group value, then use where clause as @Kurt_Bremser has suggested already.

Re: how to build a set of macro variables to store the max value for each group

PaigeMiller — Wed, 02 Oct 2024 10:59:23 GMT

Hello, @stataq , excellent advice from @Kurt_Bremser , please tell us (as he said) the big picture. Why you need these in macro variables? What are you going to do with the macro variables? (And in fact, knowing the big picture is something you should tell us every time you post a question here, we need explanation of the problem, explanation of the big picture, rather than a simple question about code). When we have that big picture explanation, we can often come up with a better solution than the one you are trying to program. For example, if you want to subtract the mean from the data in each group (that's the big picture description), no macro variables are needed, you can use PROC STDIZE and get it done with one PROC call.

Re: how to build a set of macro variables to store the max value for each group

stataq — Wed, 02 Oct 2024 12:19:01 GMT

I am learning how to set up macro. Just try to understand how to extract the needed info from a dataset and put it into a macro variable.

Re: how to build a set of macro variables to store the max value for each group

PaigeMiller — Wed, 02 Oct 2024 12:25:10 GMT

Part of learning macros is learning when NOT to use macros. As @Kurt_Bremser said: "But I seriously question the need for macro variables; for all purposes I see, the want dataset is better suited."