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
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.