Hi all,
Sorry I don't have access to SAS right now (but using 9.4 at work), but I have a dataset like this:
patient_id collection_date test_type test_result
1 3/1/2020 Antibody Positive
1 3/3/2020 PCR Positive
1 3/14/2020 PCR Negative
2 2/12/2020 Antibody Negative
2 4/10/2020 Antibody Positive
3 6/10/2020 Antibody Positive
3 6/15/2020 PCR Negative
Notice I have multiple obs. per patient, with multiple tests per patient. I want to output to new table ONLY those patients that have Ab positive, but NO PCR done. So in this case, only patient_id 2 (second line showing Antibody Positive) should output because he had no PCR done. What's the best way to do this? I have a feeling with arrays, but I have not used them yet.
Thank you!
Hi,
use this:
DATA have;
input patient_id collection_date : $15. test_type : $15. test_result : $15.;
datalines;
1 3/1/2020 Antibody Positive
1 3/3/2020 PCR Positive
1 3/14/2020 PCR Negative
2 2/12/2020 Antibody Negative
2 4/10/2020 Antibody Positive
3 6/10/2020 Antibody Positive
3 6/15/2020 PCR Negative
;
RUN;
PROC SQL;
CREATE TABLE have1 AS
SELECT a.*,
CASE
WHEN missing(b.patient_id) then 1
ELSE 0
END as noPCR_done
FROM have a
LEFT JOIN have b
ON a.patient_id eq b.patient_id
AND upcase(b.test_type) eq 'PCR'
ORDER BY a.patient_id, a.collection_date, a.test_type
;
QUIT;
DATA want;
set have1;
*select patient having no PCR done and having any AB positive;
where noPCR_done eq 1
AND upcase(test_type) eq 'ANTIBODY' AND upcase(test_result) eq 'POSITIVE';
RUN;
- Cheers -
Hi,
use this:
DATA have;
input patient_id collection_date : $15. test_type : $15. test_result : $15.;
datalines;
1 3/1/2020 Antibody Positive
1 3/3/2020 PCR Positive
1 3/14/2020 PCR Negative
2 2/12/2020 Antibody Negative
2 4/10/2020 Antibody Positive
3 6/10/2020 Antibody Positive
3 6/15/2020 PCR Negative
;
RUN;
PROC SQL;
CREATE TABLE have1 AS
SELECT a.*,
CASE
WHEN missing(b.patient_id) then 1
ELSE 0
END as noPCR_done
FROM have a
LEFT JOIN have b
ON a.patient_id eq b.patient_id
AND upcase(b.test_type) eq 'PCR'
ORDER BY a.patient_id, a.collection_date, a.test_type
;
QUIT;
DATA want;
set have1;
*select patient having no PCR done and having any AB positive;
where noPCR_done eq 1
AND upcase(test_type) eq 'ANTIBODY' AND upcase(test_result) eq 'POSITIVE';
RUN;
- Cheers -
Assuming that HAVE is sorted by patient_id, the Double DOW-loop should do it.
DATA have;
input patient_id collection_date : $15. test_type : $15. test_result : $15.;
datalines;
1 3/1/2020 Antibody Positive
1 3/3/2020 PCR Positive
1 3/14/2020 PCR Negative
2 2/12/2020 Antibody Negative
2 4/10/2020 Antibody Positive
3 6/10/2020 Antibody Positive
3 6/15/2020 PCR Negative
;
RUN;
data want;
do _N_ = 1 by 1 until(last.patient_id);
set have;
by patient_id;
c1 + (test_type="Antibody" and test_result="Positive");
c2 + (test_type="PCR");
end;
do _N_ = 1 to _N_;
set have;
if c1 and not c2 then output;
end;
c1=0;
c2=0;
drop c1 c2;
run;
proc print;
run;
Bart
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.