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.
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;
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;
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;
Thank you so much!! this worked. Sorry for the delay in response
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: