Search code examples
sasdatastep

SAS task. Need to pick records conditionally


I have been given a task to solve, I'm quite new to this programming language. When the flag is 0, I have to pick the next immediate record, provided the flag variable has three or more consecutive records with value 1. I have been going at it for hours. Below is the datastep. Please suggest.

data two;
input usubjid visit flag;
cards;
1001 1 1
1001 2 1
1001 3 0
1001 4 1
1001 5 1
1001 6 1
1002 1 1
1002 2 1
1002 3 0
1002 4 1
1002 5 1
1003 1 0
1003 2 1
1003 3 1
1003 4 1
1003 5 1
;
run;

output will be:-

usubjid visit flag
1001     4     1

1003     2     1

This is what I have tried so far.

proc sort data = two ;
        by usubjid ;    
run;
  proc transpose data = two out = tran ;
        by usubjid ;
        id visit ;var flag ;
run;
data b ;
    set tran ;
    if ( ( _1 = _2 ) and ( _1 = _3 ) ) or ( ( _2 = _3 ) and ( _2 = _4 ) ) or ( ( _3 = _4 ) and (     _3 = _5 ) ) or
        ( ( _4 = _5 ) and ( _4 = _6 ) ) ;
run;
proc sort data = b ;
    by usubjid ;
run;
data c ;
    merge a ( in = a ) b ( in = b ) ;
    by usubjid ;
    if a ;                                                                
run;                                                                                   

Solution

  • Ok. Deleted my old answer as I didn't fully understand what you were looking for.

    data two;
    set two;
    n = _n_;
    run;
    
    proc sort data=two;
    by descending n ;
    run;
    
    data two;
    set two;
    retain count  0;
    if flag then
        count = count + 1;
    else 
        count = 0;
    run;
    
    proc sort data=two;
    by n;
    run;
    
    data two (drop=count n);
    set two;
    
    if lag(flag) = 0 and count >=3;
    run;
    

    First, I add a row number variable.

    Then sort by that value in reverse order.

    Add a variable that counts the number of consecutive records where flag=1.

    Resort back to the original order.

    Finally, subset down to what you are looking for.