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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.