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

I have the following data set, I am trying to select only observations

with at least 1 positive and 1 negative outcome.  

 

Data Diagnose;
Input @1 Patient_No $2.
@3 Date MMDDYY10.
@14 Visit_No $2.
@16 Outcome $12.;
Format Date MMDDYY10.;
Datalines;
1 10/21/2000 1 Positive
1 10/25/2000 2 Positive
1 11/01/2000 3 Negative
1 05/28/2001 4 Negative
2 11/22/2000 1 Positive
2 11/29/2000 2 Positive
2 12/28/2000 3 Positive
2 06/28/2001 4 Low positive
2 10/29/2001 5 Negative
3 12/12/2000 1 Positive
3 12/29/2000 2 Positive
3 02/21/2001 3 Positive
3 07/12/2001 4 Negative
3 08/29/2001 5 Positive
4 08/29/2001 1 Positive
4 09/05/2001 2 Positive
4 11/01/2001 3 Positive
;
run;

 

I tried the following code.

data a;
set diagnose;
where outcome = "Positive" and "Negative";
proc print;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
nerdy2703
Fluorite | Level 6

Yes, that works. Thank you for the addition of patient no 5. 

View solution in original post

7 REPLIES 7
PGStats
Opal | Level 21

Beware of the cases where there are more than one distinct outcome but only one of them is Positive or Negative (patient_no = 5 below) :

 

Data Diagnose;
Input @1 Patient_No $2.
@3 Date MMDDYY10.
@14 Visit_No $2.
@16 Outcome $12.;
Format Date MMDDYY10.;
Datalines;
1 10/21/2000 1 Positive
1 10/25/2000 2 Positive
1 11/01/2000 3 Negative
1 05/28/2001 4 Negative
2 11/22/2000 1 Positive
2 11/29/2000 2 Positive
2 12/28/2000 3 Positive
2 06/28/2001 4 Low positive
2 10/29/2001 5 Negative
3 12/12/2000 1 Positive
3 12/29/2000 2 Positive
3 02/21/2001 3 Positive
3 07/12/2001 4 Negative
3 08/29/2001 5 Positive
4 08/29/2001 1 Positive
4 09/05/2001 2 Positive
4 11/01/2001 3 Positive
5 11/22/2000 1 Low positive
5 11/29/2000 2 Negative
5 12/28/2000 3 Negative
5 06/28/2001 4 Negative
;

proc sql;
create table a as
select *
from diagnose
group by patient_no
having count( distinct 
    case outcome 
    when "Positive" then 1 
    when "Negative" then 2 
    else . end ) > 1
order by patient_no, visit_no;
select * from a;
quit;
PG
nerdy2703
Fluorite | Level 6

Yes, that works. Thank you for the addition of patient no 5. 

PGStats
Opal | Level 21

For a non SQL solution:

 

data b;
do until(last.patient_no);
    set diagnose; by patient_no notsorted;
    pos = pos or outcome = "Positive";
    neg = neg or outcome = "Negative";
    end;
do until(last.patient_no);
    set diagnose; by patient_no notsorted;
    if pos and neg then output;
    end;
drop pos neg;
run;

proc print noobs; run;
PG
SAS_inquisitive
Lapis Lazuli | Level 10
@ PGStats, can you explan how does this code works?
pos = pos or outcome = "Positive";
I tried below, and it does not work.
pos = (outcome = "Positive");
PGStats
Opal | Level 21

Simple logic. At the start of a patient obs, pos is missing which is interpreted as False. The logic expression ensures that at each iteration over the visits of a patient, if pos is already True, it remains True (True OR <anything> = True), otherwise it becomes true if outcome="Positive".

 

Thus, after iterating over a patients' visits, pos = True if "Positive" was met at least once.

PG
Kurt_Bremser
Super User

Since there's only one outcome value in your observations, it can never be positive and negative at the same time.

Define what you consider an "observation"; in SAS speak, an observation is a single row of data in a dataset.

nerdy2703
Fluorite | Level 6

Yes, but the above example is transposed and ordered, should have pointed that out.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 994 views
  • 2 likes
  • 4 in conversation