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;
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;
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;
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;
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 16. 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.