Delete a group based on a particular observation

[TheNovice]

There is data that I have sorted using the following code:

proc sort data=RESTORED ; by ID ent_seq_no descending col_actv_code ; quit;

I get the following. below is an example of a type of group in the data. What I want to do is delete these records( all of them) if the last actv_code = 'S'. I only want to delete the group of ID's if the last code is S. I cannot figure out how.

ID      ENT_SEQ_NO  ACTV_CODE
0935     913240851          S
0935     913863008            K
0935     915287558          S
0935     915733042          K
0935     919660717          S

Data r ;
set restored;
by ban ent_seq_no descending col_actv_code;
if not last.ban then delete;
run;

Any direction is appreciated.

[heffo]

This could work:

data _null_;
	set restored end=lastRow;
	length toBeDeleted $ 500;
	by ID;
	if last.id and upcase(col_actv_code) = "S" then do;
			toBeDeleted = cat(strip(toBeDeleted)," '",strip(ID),"' ");
	end;
	if lastRow then call symputx ("toBeDeleted",toBeDeleted);
run;

proc sql noprint;
	create table Restored_2 as 
	select * from Restored 
	where ID not in (&toBeDeleted);
quit;

Potentially you could sort your data the other way around, so that the first row is the S and if so, then you make sure that you don't have an output command for the rest of the rows in that id. But you then have to resort the data again afterwards.

proc sort data=RESTORED out=RESTORED_opp; 
	by ID descending ent_seq_no col_actv_code ; 
run;

data restored_2;
	set restored_opp;
	by ID;
	retain _Keep_ID 1;
	if first.id and upcase(col_actv_code) = "S" then do;
		_Keep_ID = 0;
	end;
	if _Keep_ID = 1 then output;
	if last.id then _Keep_ID = 1;
run;

[Astounding · Accepted solution]

Using the variable names from your sample data, this would be one way:

data want;

   do until (last.id);

      set have;

      by id;

   end;

   if actv_code='S' then output_flag='N';

   else output_flag='Y';

   do until (last.id);

      set have;

      by id;

      if output_flag='Y' then output;

   end;

   drop output_flag;

run;

[TheNovice]

Thank you so much!! this worked. Sorry for the delay in response