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...
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;
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;
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;
data want;
merge
have
have (
in=_in2
rename=(x2=_x2)
where=(_x2 = 2)
)
;
by x1;
if _in2 then delete;
drop _:;
run;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.