topic Re: Delete group on condition in New SAS User

Delete group on condition

antona — Fri, 26 Apr 2019 08:14:08 GMT

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

Re: Delete group on condition

smijoss1 — Sun, 28 Apr 2019 01:44:35 GMT

Sql

select from table

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

Re: Delete group on condition

andreas_lds — Mon, 29 Apr 2019 06:43:09 GMT

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;