Delete group on condition

[antona]

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

[smijoss1]

Sql 

select  from table 

where id not in ( select id from table group by ID having max(var) = 0 );

[andreas_lds]

Hello @antona, welcome to the Community!

Is  var numeric or alphanumeric?

If it is alphanumeric try:

data killList;
   set have;
   by id;

   retain onlyZeros;

   if first.id then onlyZeros = 1;
   if var = '1' then onlyZeros = 0;
   if last.id and onlyZeros then output;

   keep id;
run;

data want;
   merge have killList(in= removeIt);
   by id;

   if removeIt then delete;
run;