topic Re: Min and Max Values by By Group in SAS Programming

Min and Max Values by By Group

buechler66 — Tue, 08 Nov 2016 18:15:21 GMT

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

Re: Min and Max Values by By Group

Astounding — Tue, 08 Nov 2016 18:21:23 GMT

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?

Re: Min and Max Values by By Group

buechler66 — Tue, 08 Nov 2016 18:35:05 GMT

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

Re: Min and Max Values by By Group

Astounding — Tue, 08 Nov 2016 18:44:20 GMT

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.

Re: Min and Max Values by By Group

jhlaramore — Tue, 08 Nov 2016 18:50:00 GMT

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;

Re: Min and Max Values by By Group

buechler66 — Tue, 08 Nov 2016 19:55:14 GMT

Awesome. I really appreciate your help!

Re: Min and Max Values by By Group

buechler66 — Tue, 08 Nov 2016 19:55:41 GMT

Thank you for showing me this.

Re: Min and Max Values by By Group

jhlaramore — Tue, 08 Nov 2016 20:23:06 GMT

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