max group per ID

[monday89]

I have the following dataset:

ID	order	status	time_group
1234	1	increase	0 to 6 hours
1234	2	increase	12 to 24 hours
1234	3	decrease	24 to 48 hours
1234	4	increase	24 to 48 hours
2342	1	increase	0 to 6 hours
2342	2	decrease	12 to 24 hours
2342	3	increase	12 to 24 hours
2342	4	increase	24 to 48 hours
2342	5	decrease	24 to 48 hours

What I would like to do is extract max order ID and status by time group. For example, in ID 2342, there are two 24 to 48 hours but with differing status (i.e. increase and decrease). What I would like to do is keep the max order number for that. So keep decrease for group 24-48 hours.

Something like this below:

ID	order	status	time_group
1234	1	increase	0 to 6 hours
1234	2	increase	12 to 24 hours
1234	4	increase	24 to 48 hours
2342	1	increase	0 to 6 hours
2342	3	increase	12 to 24 hours
2342	5	decrease	24 to 48 hours

I did the following

proc sql;

create table want as

select distinct ID, timegroup, status, max(order) as ID

from have 

group by ID, timegroup, status;quit;

not getting what i want

[Kurt_Bremser]

You will need a "having" clause:

proc sql;
create table want as
  select
    id,
    time_group,
    order,
    status
  from have
  group by id, time_group
  having order = max(order)
;
quit;

[novinosrin]

data have;
input (ID	order	status) (:$32.) time_group $32.;
cards;
1234	1	increase	0 to 6 hours
1234	2	increase	12 to 24 hours
1234	3	decrease	24 to 48 hours
1234	4	increase	24 to 48 hours
2342	1	increase	0 to 6 hours
2342	2	decrease	12 to 24 hours
2342	3	increase	12 to 24 hours
2342	4	increase	24 to 48 hours
2342	5	decrease	24 to 48 hours
;

data want;
 set have;
 by id time_group notsorted;
 if last.time_group;
run;

[Reeza]

And if you had a record with the time of 6 to 12? That would sort as the max, wouldn't it?

You'll need to recode that time variable to a variable that will order correctly. 

Is your grouping by order or by time_group? That isn't clear.