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

[Excellrec]

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!

[tarheel13]

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;

[Excellrec]

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?

[tarheel13]

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. 

[tarheel13]

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;

[PeterClemmensen · Accepted solution]

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 

[Excellrec]

Perfect! Thanks you.

[PeterClemmensen]

Anytime 🙂