Min and Max Values by By Group

[buechler66]

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

[Astounding]

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?

[buechler66]

Yes, I'd need to keep ties. The ds order does not matter. Thanks.

[Astounding]

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.

[jhlaramore · Accepted solution]

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;

[buechler66]

Awesome. I really appreciate your help!

[buechler66]

Thank you for showing me this.

[jhlaramore]

You're welcome - Glad it was able to help.