topic Re: Delete group on condition in SAS Programming

Delete group on condition

blackandwhite — Thu, 25 Apr 2019 16:05:21 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

novinosrin — Thu, 25 Apr 2019 16:15:35 GMT


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;

Re: Delete group on condition

blackandwhite — Thu, 25 Apr 2019 16:35:45 GMT

thanks!!

Re: Delete group on condition

PeterClemmensen — Thu, 25 Apr 2019 17:30:45 GMT

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;