topic Re: Calculate max value within groups, when a condition is not met, then add max value as new condit in SAS Programming

Calculate max value within groups, when a condition is not met, then add max value as new condition?

Excellrec — Tue, 22 Jun 2021 16:02:54 GMT

Here is what I have:

Group	Condition	Value
1	This condition	4
1	This condition	3
1	This condition	1
1	Not this condition	6
2	This condition	3
2	This condition	1
2	This condition	6
2	Not this condition	8

Here is what I'm trying to get (note that max is not derived from the 'Not this condition' condition):

Group	Condition	Value
1	Max condition	4
1	This condition	4
1	This condition	3
1	This condition	1
1	Not this condition	6
2	Max Condition	6
2	This condition	3
2	This condition	1
2	This condition	6
2	Not this condition	8

Thank you for your help!

Re: Calculate max value within groups, when a condition is not met, then add max value as new condit

tarheel13 — Tue, 22 Jun 2021 16:43:15 GMT

why don't you just get the max with proc sql and then append that table to the original?

proc sql;
create table maxes as 
select group, condition, max(value) as value
from have
where condition ^='Not this condition'
group by group, condition;
quit;

data stack;
set want have;
run;

Re: Calculate max value within groups, when a condition is not met, then add max value as new condit

Excellrec — Tue, 22 Jun 2021 17:27:33 GMT

Thank you for the reply. I've tried this syntax but the 'value' is not calculating as the max value, it's returning the value as it was. Could it be because the max value is often the 'not this condition' condition and the where statement is excluding it from the resultant dataset?

Re: Calculate max value within groups, when a condition is not met, then add max value as new condit

tarheel13 — Tue, 22 Jun 2021 17:33:57 GMT

What is your requirement again? You wrote something about the max not being from this condition so that's why I put that in the where clause. If it is just the max per group, then take condition out of the select statement as that should not be there.

Re: Calculate max value within groups, when a condition is not met, then add max value as new condit

tarheel13 — Tue, 22 Jun 2021 17:36:01 GMT

Try this if you just want max per group.

proc sql;
create table maxes as 
select group, max(value) as value
from have
where condition ^='Not this condition'
group by group;
quit;

data stack;
set want have;
run;

Re: Calculate max value within groups, when a condition is not met, then add max value as new condit

PeterClemmensen — Tue, 22 Jun 2021 18:51:37 GMT

data have;
input Group Condition $ 3-21 Value;
datalines;
1 This condition     4
1 This condition     3
1 This condition     1
1 Not this condition 6
2 This condition     3
2 This condition     1
2 This condition     6
2 Not this condition 8
;

data want(drop = max);
   do _N_ = 1 by 1 until (last.Group);
      set have;
      by Group;
      if Condition ne "Not this condition" & value > max then max = value;
   end;

   value = max;
   condition = "Max Condition";
   output;

   do _N_ = 1 to _N_;
      set have;
      output;
   end;

   max = .;
run;

Result:

Group Condition          Value 
1     Max Condition      4 
1     This condition     4 
1     This condition     3 
1     This condition     1 
1     Not this condition 6 
2     Max Condition      6 
2     This condition     3 
2     This condition     1 
2     This condition     6 
2     Not this condition 8

Re: Calculate max value within groups, when a condition is not met, then add max value as new condit

Excellrec — Tue, 22 Jun 2021 19:00:01 GMT

Perfect! Thanks you.

Re: Calculate max value within groups, when a condition is not met, then add max value as new condit

PeterClemmensen — Tue, 22 Jun 2021 19:05:47 GMT

Anytime 🙂