🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 04-25-2019 12:05 PM
(2161 views)
Hi,
I would like to delete all rows for IDs who only have 0s for var
So, in the example below, I would delete all rows for ID#2.
Somehow, I have to read all obs in each by group and then flag those who only have 0 but having trouble finding how..
Thanks very much!!
Have | Want | |||
ID | var | ID | var | |
1 | 1 | 1 | 1 | |
1 | 0 | 1 | 0 | |
1 | 0 | 1 | 0 | |
1 | 0 | 1 | 0 | |
1 | 1 | 1 | 1 | |
1 | 0 | 1 | 0 | |
1 | 1 | 1 | 1 | |
1 | 0 | 1 | 0 | |
2 | 0 | 3 | 0 | |
2 | 0 | 3 | 0 | |
2 | 0 | 3 | 0 | |
2 | 0 | 3 | 1 | |
3 | 0 | |||
3 | 0 | |||
3 | 0 | |||
3 | 1 |
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input ID var ;
cards;
1 1 1 1
1 0 1 0
1 0 1 0
1 0 1 0
1 1 1 1
1 0 1 0
1 1 1 1
1 0 1 0
2 0 3 0
2 0 3 0
2 0 3 0
2 0 3 1
3 0
3 0
3 0
3 1
;
proc sql;
create table want as
select *
from have
group by id
having n(id) ne sum(var=0);
quit;
3 REPLIES 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input ID var ;
cards;
1 1 1 1
1 0 1 0
1 0 1 0
1 0 1 0
1 1 1 1
1 0 1 0
1 1 1 1
1 0 1 0
2 0 3 0
2 0 3 0
2 0 3 0
2 0 3 1
3 0
3 0
3 0
3 1
;
proc sql;
create table want as
select *
from have
group by id
having n(id) ne sum(var=0);
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
thanks!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input ID var;
cards;
1 1
1 0
1 0
1 0
1 1
1 0
1 1
1 0
2 0
2 0
2 0
2 0
3 0
3 0
3 0
3 1
;
data want;
if _N_ = 1 then do;
declare hash h(dataset:'have(where=(var=1))');
h.defineKey('ID');
h.defineDone();
end;
set have;
if h.check()=0;
run;