topic Re: last observations with last group in SAS Programming

last observations with last group

noda6003 — Wed, 16 Dec 2020 08:43:42 GMT

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;

Re: last observations with last group

PeterClemmensen — Wed, 16 Dec 2020 08:58:57 GMT

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;

Re: last observations with last group

Kurt_Bremser — Wed, 16 Dec 2020 09:01:17 GMT

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;