## Min and Max Values by By Group

Solved
Regular Contributor
Posts: 212

# Min and Max Values by By Group

I have a ds with ZipCode, Order_Num, and Total. Is it possible to create a ds with the Min and Max Total for each Order_Num?

Any help would be appreciated.

Dataset Example:

ZipCode Order_Num Total

12345     1                  99

23451     1                  1

34512     1                  10

45123     2                  10

51234     2                  13

67890     2                  10

78901     3                  11

89012     3                  10

90123     3                  20

Desired Result Set:

ZipCode Order_Num Total

12345     1                  99

23451     1                  1

45123     2                  10

51234     2                  13

67890     2                  10

89012     3                  10

90123     3                  20

Accepted Solutions
Solution
‎11-08-2016 02:54 PM
SAS Employee
Posts: 15

## Re: Min and Max Values by By Group

Try this:

data have;
input ZIPCODE \$ ORDER \$ NUM_TOTAL;
datalines;
12345 1 99
23451 1 1
34512 1 10
45123 2 10
51234 2 13
67890 2 10
78901 3 11
89012 3 10
90123 3 20
;
run;

proc sql;
create table want as
select *
from have
group by order
having Num_Total = max(Num_Total) or Num_Total = min(Num_Total)
order by order, zipcode;
quit;

All Replies
Super User
Posts: 6,898

## Re: Min and Max Values by By Group

It looks like you could have a tie.  In that case, do you want all observations that match the min (or all that match the max)?

In the example, you left the observations in their original order.  Is that a requirement, or is sorting an option?

Regular Contributor
Posts: 212

## Re: Min and Max Values by By Group

Yes, I'd need to keep ties. The ds order does not matter. Thanks.
Super User
Posts: 6,898

## Re: Min and Max Values by By Group

OK, here's one way to look at it.

data want;

do until (last.order_num);

set have;

by order_num;

minval = min(minval, total);

maxval = max(maxval, total);

end;

do until (last.order_num);

set have;

by order_num;

if total=minval or total=maxval then output;

end;

drop minval maxval;

run;

The top loop finds the minimum and maximum values for an ORDER_NUM, and the bottom loop reads through the same observations to find the matches.  I'm sure there's a SQL approach as well, but I'm more comfortable with a DATA step.

Solution
‎11-08-2016 02:54 PM
SAS Employee
Posts: 15

## Re: Min and Max Values by By Group

Try this:

data have;
input ZIPCODE \$ ORDER \$ NUM_TOTAL;
datalines;
12345 1 99
23451 1 1
34512 1 10
45123 2 10
51234 2 13
67890 2 10
78901 3 11
89012 3 10
90123 3 20
;
run;

proc sql;
create table want as
select *
from have
group by order
having Num_Total = max(Num_Total) or Num_Total = min(Num_Total)
order by order, zipcode;
quit;

Regular Contributor
Posts: 212

## Re: Min and Max Values by By Group

Awesome. I really appreciate your help!
Regular Contributor
Posts: 212

## Re: Min and Max Values by By Group

Thank you for showing me this.
SAS Employee
Posts: 15