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

[stataq]

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?

[AhmedAl_Attar · Accepted solution]

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;

[stataq]

@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;

[Kurt_Bremser]

Use a WHERE clause in the SQL.

[AhmedAl_Attar]

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.

[Kurt_Bremser]

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?

[PaigeMiller]

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.

[stataq]

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.

[PaigeMiller]

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."