DATA Step, Macro, Functions and more

count

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 76
Accepted Solution

count

[ Edited ]

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


Accepted Solutions
Solution
‎04-26-2017 01:55 PM
Super User
Super User
Posts: 6,500

Re: count

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.

 

View solution in original post


All Replies
Frequent Contributor
Posts: 93

Re: count

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;
Frequent Contributor
Frequent Contributor
Posts: 76

Re: count

Thank you, we need to consider the >= 2 continuous 0 instead of total 0 . For example, there are two 0 for ID 1, but it cannot be deleted, due to not 0 is separated.
Solution
‎04-26-2017 01:55 PM
Super User
Super User
Posts: 6,500

Re: count

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.

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 119 views
  • 0 likes
  • 3 in conversation