topic Re: Delete a group based on a particular observation in SAS Programming

Delete a group based on a particular observation

TheNovice — Tue, 09 Jul 2019 01:05:12 GMT

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.

Re: Delete a group based on a particular observation

heffo — Tue, 09 Jul 2019 02:20:03 GMT

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;

Re: Delete a group based on a particular observation

Astounding — Tue, 09 Jul 2019 02:29:08 GMT

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;

Re: Delete a group based on a particular observation

TheNovice — Wed, 24 Jul 2019 01:31:22 GMT

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