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
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;
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?
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.
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;
You're welcome - Glad it was able to help.
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.
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.