DATA Step, Macro, Functions and more

Min and Max Values by By Group

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

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;

View solution in original post


All Replies
Super User
Posts: 5,099

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: 5,099

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

Re: Min and Max Values by By Group

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 281 views
  • 2 likes
  • 3 in conversation