Hello,
I would like to delete the ID with more than 2 coutinous 0 value of RX. I am wondering what is efficient way to do it. Thank you very much.
ID RX
1 1
1 1
1 0
1 1
1 0
1 1
2 0
2 0
2 1
So want to look for two in a row? You didn't mention any variable to use for ordering so we should we assume the data is already sorted?
So two is easy. Basically if RX=0 and it is not an isolated block of just one RX=0 then you have two or more RX=0 records in a row.
Using double DOW loop you can test an ID and then in the second loop decide whether to output the records for that ID.
data want ;
do until (last.id);
set have ;
by id rx notsorted ;
if rx=0 and not (first.rx and last.rx) then any2=1;
end;
do until (last.id);
set have ;
by id rx notsorted ;
if not any2 then output;
end;
run;
It looks like both of your example IDs would be deleted since the both have two or more RX=0 records next to each other.
I prefer proc sql because the code/concept may be used in many applications.
For your consideration:
PROC SQL;
CREATE TABLE TOTALS AS
SELECT ID, SUM(RX) AS TOTAL FROM IDS GROUP BY ID;
QUIT;
PROC SQL;
CREATE TABLE NEWTABLE AS
SELECT * FROM IDS WHERE ID IN (SELECT ID FROM TOTALS WHERE TOTAL <=2);
QUIT;
So want to look for two in a row? You didn't mention any variable to use for ordering so we should we assume the data is already sorted?
So two is easy. Basically if RX=0 and it is not an isolated block of just one RX=0 then you have two or more RX=0 records in a row.
Using double DOW loop you can test an ID and then in the second loop decide whether to output the records for that ID.
data want ;
do until (last.id);
set have ;
by id rx notsorted ;
if rx=0 and not (first.rx and last.rx) then any2=1;
end;
do until (last.id);
set have ;
by id rx notsorted ;
if not any2 then output;
end;
run;
It looks like both of your example IDs would be deleted since the both have two or more RX=0 records next to each other.
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.