Choosing obs based on outcomes in a particular order based on dates

[vegan_renegade]

I'm working with a dataset with multiple obs per patient (multiple test dates and results). I want to isolate any patient that has had a positive test, then later a negative, then later a positive again among all tests done. It doesn't have to be in this particular order- for example it's OK if patient had a positive, then 3 negatives, then a positive. Or any other possible combination of results...as long as at any time he had a positive, then later a negative, then again a positive. In the example below, patients 1 and 2 should be isolated as they met the criteria. Patient 3 did not as he never got  a second positive after the last negative. Thank you!

DATA have;
input patient_id collection_date : $15. test_result : $15.;
datalines;
1 05/18/2015 Positive
1 05/21/2015 Negative
1 08/24/2016 Positive
1 11/01/2016 Negative
1 04/11/2018 Positive
2 01/02/2015 Negative
2 01/10/2016 Positive
2 02/06/2016 Negative
2 03/16/2016 Positive
3 05/21/2016 Positive
3 05/27/2016 Positive
3 01/07/2017 Negative
;
RUN;

[japelin · Accepted solution]

I think collection_date is easier to handle with the date type.
(In the following, I've created another date variable.)

data next;
  set have;
  coldt=input(collection_date,mmddyy10.);
run;

proc sort data=next;
  by patient_id coldt;
run;
data want;
  set next;
  by patient_id coldt;
  retain flgp flgn flgo;
  if first.patient_id then do;
    flgp=0;
    flgn=0;
    flgo=0;
  end;
       if flgn=0 and test_result='Positive' then flgp=1;
  else if flgp=1 and test_result='Negative' then flgn=1;
  else if flgn=1 and test_result='Positive' then flgo=1;
  /* Enable "and test_result='Positive'" if you want to exclude patients who meet the criteria but test negative again. */
  if flgo=1 /*and test_result='Positive'*/ and last.patient_id then output;
  drop coldt flg:;
run;

[vegan_renegade]

Thank you! This worked, although I don't understand the meaning behind each line, starting with the do loop. Would like an explanation of each line if you have time.

[japelin]

How about this?
I put as many comments as I could in each line.

data next;
  set have;
  coldt=input(collection_date,mmddyy10.);/* Create a date variable for sorting. */
run;

proc sort data=next;
  by patient_id coldt;/* Sort by patient_id and date */
run;
data want;
  set next;
  by patient_id coldt;
  retain flgp flgn flgo;/* Specify the flag variables flgp, flgn, and flgo with retain so that they remain after the next obs is read. */
  if first.patient_id then do;/* Initialize the flag variable at the first obs of the patient */
    flgp=0;/* 1 If Positive */
    flgn=0;/* 1 if Negative */
    flgo=0;/* 1 if output target  */
  end;
       if flgn=0 and test_result='Positive' then flgp=1;/* Set the flag "flgp" when a Positive is detected. */
  else if flgp=1 and test_result='Negative' then flgn=1;/* Set the flag "flgn" when a Negative is detected after a Positive. */
  else if flgn=1 and test_result='Positive' then flgo=1;/* Set the flag "flgo" if there is a Positive after a Negative */
  /* Enable "and test_result='Positive'" if you want to exclude patients who meet the criteria but test negative again. */
  if flgo=1 /*and test_result='Positive'*/ and last.patient_id then output;/* If the patient is an output target (flgo=1), output the last obs of the patient */
  drop coldt flg:;/* Delete the work variables */
run;

[vegan_renegade]

Yes this is perfect, thank you!