How to exclude specific records

[hjjijkkl]

I have a data where I would like to excludes records of a participant where "test"  recorded  0's from the "first visitdate" to the "last visitdate". If the participant has 0's in between "visits" I would like to keep them. Please see the table below as an example.

ID	visitdate	test	Result
1	6/8/2020	1	87
1	9/6/2020	1	54
1	12/4/2020	0	76
2	7/4/2020	2	84
2	7/22/2021	1	33
3	1/3/2021	0	88
3	5/14/2021	0	65
4	7/9/2021	1	23
5	8/9/2020	0	98
5	12/4/2020	0	44

Below is how the output would look like

ID	visitdate	test	Result
1	6/8/2020	1	87
1	9/6/2020	1	54
1	12/4/2020	0	76
2	7/4/2020	2	84
2	7/22/2021	1	33
4	7/9/2021	1	23

[PaigeMiller]

Please provide data as SAS data step code, following these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

[Reeza]

Please show what you've tried so far, we've answered similar questions so that should help you get started here.

[hjjijkkl]

this is what I tried the code in different ways but, its not giving me the answer I wanted
data want ;
do until(last.ID);
set need; by ID;
t = max(t, test);
end;
if t > 0;
keep ID test visitdate result;
run;

[jimbarbour]

The answer from @sbxkoenk looks good to me.  

If it were helpful, here's how it could be done with LAST. type processing (see below).  It's basically the same idea as @sbxkoenk's solution:  1) identify the ID's to keep and 2) extract all of the records corresponding to the identified ID's.

DATA	Have;
	INFILE	DATALINES	DSD	DLM='09'X	MISSOVER;
	INPUT
		ID			$	
		VisitDate	:	ANYDTDTE10.	
		Test		$
		Result		$
		;
	FORMAT	VisitDate	MMDDYYS10.;
DATALINES;
1	6/8/2020	1	87
1	9/6/2020	1	54
1	12/4/2020	0	76
2	7/4/2020	2	84	
2	7/22/2021	1	33
3	1/3/2021	0	88
3	5/14/2021	0	65
4	7/9/2021	1	23
5	8/9/2020	0	98
5	12/4/2020	0	44
;
RUN;

DATA	IDs_To_Keep	(KEEP=ID);
	DROP	_:;
	SET	Have;
		BY	ID	NOTSORTED;

	IF	Test			THEN
		_Test_Count	+	1;

	IF	LAST.ID			THEN
		IF	_Test_Count	THEN
			DO;
				OUTPUT;
				_Test_Count	=	0;
			END;
		ELSE
			DO;
				DELETE;
			END;
RUN;

PROC	SQL;
	CREATE	TABLE	Want	AS
		SELECT	*	FROM	Have
			WHERE	ID	IN	(
				SELECT	*	FROM	IDs_To_Keep);
QUIT;

Jim

[sbxkoenk · Accepted solution]

data HAVE;
input ID $ visitdate : MMDDYY10. test $ Result;
cards;
1  6/08/2020 1 87
1  9/06/2020 1 54
1 12/04/2020 0 76
2  7/04/2020 2 84
2  7/22/2021 1 33
3  1/03/2021 0 88
3  5/14/2021 0 65
4  7/09/2021 1 23
5  8/09/2020 0 98
5 12/04/2020 0 44
;
run;

PROC SQL noprint;
 create table IDs_to_retain as
 select distinct ID
 from HAVE
 where test ^= '0';
QUIT;

PROC SQL noprint;
 create table WANT as
 select *
 from HAVE
 where ID IN (select ID from IDs_to_retain);
QUIT;
/* end of program */