Working with multiple obs for same person - choosing a particular obs

[vegan_renegade]

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!

[Oligolas · Accepted solution]

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;

[yabwon]

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