Delete group on condition

[blackandwhite]

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

[novinosrin · Accepted solution]

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;

[blackandwhite]

thanks!!

[PeterClemmensen]

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;