BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buechler66
Barite | Level 11

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jhlaramore
SAS Employee

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

7 REPLIES 7
Astounding
PROC Star

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
Barite | Level 11
Yes, I'd need to keep ties. The ds order does not matter. Thanks.
Astounding
PROC Star

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
SAS Employee

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
Barite | Level 11
Awesome. I really appreciate your help!
buechler66
Barite | Level 11
Thank you for showing me this.
jhlaramore
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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