BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vegan_renegade
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Oligolas
Barite | Level 11

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 -

View solution in original post

2 REPLIES 2
Oligolas
Barite | Level 11

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 -

yabwon
Onyx | Level 15

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

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 442 views
  • 2 likes
  • 3 in conversation