Sequence or first and last variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Sequence or first and last variable

I have the following dataset below. I have been trying to subset the data 1) have patients (Patient_No) with any prior positive outcome and any subsequent negative outcome, 2) have patients with at least 1  prior positive outcome, at least 1 subsequent negative outcome, at least 90 days between the first low positive or negative outcome and most recent negative outcome, most recent outcome should be negative and there are no subsequent positive outcomes.  I have tried using Proc sql  first and last to select the patients but the code is getting too long and messy.

 

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
;


Accepted Solutions
Solution
‎10-08-2017 12:02 AM
Respected Advisor
Posts: 4,973

Re: Sequence or first and last variable

Posted in reply to nerdy2703

I hope this implements all of your rules:

 


data sub1 sub2;
do until(last.patient_no);
    set diagnose; by patient_no;
    select (Outcome);
        when ("Positive") do;
            if missing(firstPos) then firstPos = date;
            end;
        when ("Negative") do;
            if missing(firstNeg) then firstNeg = date;
            recentNeg = date;
            end;
        when("Low positive") do;
            if missing(firstLow) then firstLow = date;
            end;
        otherwise;
        end;
    end;

/* Subset 1 :  any prior positive outcome and any subsequent 
   negative outcome */
if firstPos < firstNeg 
then output sub1;

/* Subset 2 */
if
    /* at least 1  prior positive outcome */
    not missing(firstPos) and
    /* at least 1 subsequent negative outcome */
    firstPos < firstNeg and
    /* at least 90 days between the first low positive 
       or negative outcome and most recent negative outcome */
    intck("day", min(firstLow, firstNeg), recentNeg) >= 90 and
    /* most recent outcome should be negative and there are no 
       subsequent positive outcomes */
    Outcome = "Negative" 
then output sub2;

keep patient_No; /* Applies to both output datasets */
run;
PG

View solution in original post


All Replies
Regular Contributor
Posts: 161

Re: Sequence or first and last variable

Posted in reply to nerdy2703
Can you post the expected result dataset? This allows use to verify a suggestion before posting it.

Your requirements are complex, so don't expect a single seven statements solution.
Super User
Posts: 20,219

Re: Sequence or first and last variable

Posted in reply to nerdy2703

Please post what you've tried, otherwise we may make suggestions that will not work for you.

Solution
‎10-08-2017 12:02 AM
Respected Advisor
Posts: 4,973

Re: Sequence or first and last variable

Posted in reply to nerdy2703

I hope this implements all of your rules:

 


data sub1 sub2;
do until(last.patient_no);
    set diagnose; by patient_no;
    select (Outcome);
        when ("Positive") do;
            if missing(firstPos) then firstPos = date;
            end;
        when ("Negative") do;
            if missing(firstNeg) then firstNeg = date;
            recentNeg = date;
            end;
        when("Low positive") do;
            if missing(firstLow) then firstLow = date;
            end;
        otherwise;
        end;
    end;

/* Subset 1 :  any prior positive outcome and any subsequent 
   negative outcome */
if firstPos < firstNeg 
then output sub1;

/* Subset 2 */
if
    /* at least 1  prior positive outcome */
    not missing(firstPos) and
    /* at least 1 subsequent negative outcome */
    firstPos < firstNeg and
    /* at least 90 days between the first low positive 
       or negative outcome and most recent negative outcome */
    intck("day", min(firstLow, firstNeg), recentNeg) >= 90 and
    /* most recent outcome should be negative and there are no 
       subsequent positive outcomes */
    Outcome = "Negative" 
then output sub2;

keep patient_No; /* Applies to both output datasets */
run;
PG
Occasional Contributor
Posts: 7

Re: Sequence or first and last variable

Yes, it does. Thank you
Occasional Contributor
Posts: 7

Re: Sequence or first and last variable

@PGStats, the code works fine, but I noticed that if the first visit is a negative, the code fails to meet the rules. For example, in the data set below. Patient 4 meets the rules, but has a negative first visit. 

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 10/21/2000 1 Negative
4 11/25/2001 2 Positive
4 12/01/2001 3 Positive
4 06/28/2002 4 Negative
4 10/26/2002 5 Negative
;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 194 views
  • 2 likes
  • 4 in conversation