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!
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
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;
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.
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;
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
Anytime 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.