I am doing the below step to get last ID but if the group has more than one number with last than i want multiple i.e if for ID the group has 3 as last record adn if i have multiple 3's than i want them all. CUrrently i dont get by the below step, Can anyone help me
Data have;
infile datalines dlm=',' dsd missover;
input ID ON_OFF :$8. CATEGORY :$8. Group:8.;
datalines;
1,OFF,P,1
1,ON,G,2
2,OFF,C,1
2,OFF,C,1
3,OFF,O,1
3,OFF,R,2
4,OFF,C,2
4,ON,G,2
5,OFF,S,1
5,OFF,R,2
5,ON,G,2
6,ON,S,1
6,ON,S,3
6,ON,R,3
;
run;
data want;
set have;
by ID group notsorted;
if last.ID;
run;
This?
proc sql;
create table want as
select *
from have
group by id
having group = max(group)
;
quit;
or this?
data want;
do until (last.id);
set have;
by id;
maxgroup = max(maxgroup,group);
end;
do until (last.id);
set have;
by id;
if group = maxgroup then output;
end;
drop maxgroup;
run;
How about
Data have;
infile datalines dlm=',' dsd missover;
input ID ON_OFF :$8. CATEGORY :$8. Group:8.;
datalines;
1,OFF,P,1
1,ON,G,2
2,OFF,C,1
2,OFF,C,1
3,OFF,O,1
3,OFF,R,2
4,OFF,C,2
4,ON,G,2
5,OFF,S,1
5,OFF,R,2
5,ON,G,2
6,ON,S,1
6,ON,S,3
6,ON,R,3
;
data want;
do until (last.id);
set have(rename=group=g);
by id;
end;
do until (last.id);
set have;
by id;
if group = g then output;
end;
run;
This?
proc sql;
create table want as
select *
from have
group by id
having group = max(group)
;
quit;
or this?
data want;
do until (last.id);
set have;
by id;
maxgroup = max(maxgroup,group);
end;
do until (last.id);
set have;
by id;
if group = maxgroup then output;
end;
drop maxgroup;
run;
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.