Delete rows with condition

zihdonv19 — Fri, 18 Aug 2023 15:25:50 GMT

I want to keep all rows of same id as long as this id has at least one record of type=2.

The dataset I have is:

data have;
   input id 	type	visit_date :yymmdd10.;
   format visit_date yymmdd10.;
datalines;
1 	1 	2000-01-01
1 	2 	2005-01-02
1 	2 	2006-01-03
1 	3 	2007-01-04
1 	5 	2010-01-05
2 	. 	2000-01-06
2 	. 	2000-01-07
2 	. 	2000-01-08
3 	2 	2001-01-09
3 	. 	2006-01-10
3 	2 	2007-01-11
3 	. 	2008-01-12
4 	. 	2006-01-10
4 	3 	2007-01-11
4 	1 	2008-01-12
5 	2 	2007-01-11
5 	2 	2008-01-12
5 	2 	2009-01-10
5 	2 	2010-01-11
6 	2 	2002-01-12
6 	1 	2003-01-10
6 	. 	2004-01-11
;

What I want:

data have;
   input id 	type	visit_date :yymmdd10.;
   format visit_date yymmdd10.;
datalines;
1 	1 	2000-01-01
1 	2 	2005-01-02
1 	2 	2006-01-03
1 	3 	2007-01-04
1 	5 	2010-01-05
3 	2 	2001-01-09
3 	. 	2006-01-10
3 	2 	2007-01-11
3 	. 	2008-01-12
5 	2 	2007-01-11
5 	2 	2008-01-12
5 	2 	2009-01-10
5 	2 	2010-01-11
6 	2 	2002-01-12
6 	1 	2003-01-10
6 	. 	2004-01-11
;

Re: Delete rows with condition

HB — Fri, 18 Aug 2023 15:38:33 GMT

There are multiple ways to do this:

Try:

proc sql;
	select * 
	from have
	where id in 
		(select distinct id
		from have
		where type = 2);
quit;

topic Delete rows with condition in SAS Programming

Delete rows with condition

Re: Delete rows with condition