topic Delete with a By Statement in SAS Programming

Delete with a By Statement

Mathis1 — Thu, 10 Dec 2020 15:19:12 GMT

Hi,

I would like to delete all the line that have a commun variable condionally to a "If then" statement.

For instance, if X2= 2 , I want that all the observations that have the same X1 as the observation with X2=2 are deleted.

Example :

HAVE :

X1	X2
A	1
A	3
A	4
A	5
B	1
B	3
B	5
B	6
B	8
B	2
B	1
C	1
C	5
C	4
C	3
C	9

WANT :

X1	X2
A	1
A	3
A	4
A	5
C	1
C	5
C	4
C	3
C	9

I first thought to :

Data Wante;

Set Have;

By X1;

If X2=2 then delete;

run;

But it doesn't work...

Re: Delete with a By Statement

novinosrin — Thu, 10 Dec 2020 15:37:59 GMT



data have;
input X1 $	X2;
cards;
A	1
A	3
A	4
A	5
B	1
B	3
B	5
B	6
B	8
B	2
B	1
C	1
C	5
C	4
C	3
C	9
;

data want;
 do until(z);
   set have(in=a) have(in=b) end=z;
   by x1;
   if first.x1 then _n_=0;
   if  a and x2=2 then _n_=1 ;
   if b and not _n_ then output;
 end;
run;

Re: Delete with a By Statement

Ksharp — Fri, 11 Dec 2020 13:44:31 GMT

data have;
input X1 $	X2;
cards;
A	1
A	3
A	4
A	5
B	1
B	3
B	5
B	6
B	8
B	2
B	1
C	1
C	5
C	4
C	3
C	9
;
proc sql;
create table want as
select * from have 
 where x1 not in (select distinct x1 from have where x2=2);
quit;

Re: Delete with a By Statement

Kurt_Bremser — Fri, 11 Dec 2020 13:54:03 GMT

data want;
merge
  have
  have (
    in=_in2
    rename=(x2=_x2)
    where=(_x2 = 2)
  )
;
by x1;
if _in2 then delete;
drop _:;
run;

Re: Delete with a By Statement

hhinohar — Fri, 11 Dec 2020 21:39:57 GMT

data have;
	infile datalines dlm="09"x;
	input X1 $ X2;
	datalines;
A	1
A	3
A	4
A	5
B	1
B	3
B	5
B	6
B	8
B	2
B	1
C	1
C	5
C	4
C	3
C	9
;
run;

data want;
  do _N_=1 by 1 until(last.x1);
    set have;
    by x1;

    if x2=2 then
      flg=1;
  end;

  do _N_=1 by 1 to _N_ ;
    set have;
    by x1;
    
    if flg then continue;
	output;
  end;
  drop flg;
run;