topic Re: max group per ID in SAS Programming

max group per ID

monday89 — Sat, 30 May 2020 17:14:55 GMT

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

Re: max group per ID

Kurt_Bremser — Sat, 30 May 2020 17:30:56 GMT

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;

Re: max group per ID

novinosrin — Sat, 30 May 2020 17:41:21 GMT


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;

Re: max group per ID

Reeza — Sun, 31 May 2020 02:59:48 GMT

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.