Search code examples
saslagretain

How to add a flag based on a condition on previous rows in SAS


I have the following data and would like to add a flag to each row if a condition is met in the previous row.

In the following data, I want a flag=1 if Cntr=S and only if the next row is FE followed by BC/ABC. I don't want 2/8/2019 observation for 101 and no data for 102 as there is no BC/ABC after FE.

Have:

   id   Date        Evt      Cntr
  101  2/2/2019      FE         
  101  2/3/2019      BC      S 
  101  2/4/2019      FE
  101  2/5/2019      BC
  101  2/6/2019      FE
  101  2/7/2019      ABC
  101  2/8/2019      FE
  102  2/2/2019      FE

Want:

   id   Date        Evt      Cntr       flag
  101  2/2/2019      FE         
  101  2/3/2019      BC      S 
  101  2/4/2019      FE                  1
  101  2/5/2019      BC                  1 
  101  2/6/2019      FE                  1
  101  2/7/2019      ABC                 1  
  101  2/8/2019      FE
  102  2/2/2019      FE 

I tried using lag and retain functions to solve this problem but did not get what I wanted. Please help !!


Solution

  • This is another case where DOW processing can compute the flagging state of a row.

    Arrays can be used to track values in the group. The arrays simplify computing the flagging of multiple regions after the S. Choose an array size greater than the largest expected group size.

    data have;
    infile datalines missover;
    attrib 
      id format=4. 
      date informat=mmddyy10. format=mmddyy10. 
      evt length=$3 
      cntr length=$1
    ;
    input 
      id   Date        Evt      Cntr; datalines;
      101  2/2/2019      FE         
      101  2/3/2019      BC      S 
      101  2/4/2019      FE
      101  2/5/2019      BC
      101  2/6/2019      FE
      101  2/7/2019      ABC
      101  2/8/2019      FE
      102  2/2/2019      FE
      ;
    
    data want;
      array evts[-1:1000] $3 _temporary_ ;
      array flags[1000] $1 _temporary_;
    
      call missing(of evts[*]);
      call missing(of flags[*]);
    
      do _n_ = 1 to dim(flags) until (last.id);
    
        set have;
        by id;
    
        evts[_n_] = evt;
    
        if cntr='S' then _s_index = _n_;
    
        if 0 < _s_index < _n_ - 1 then 
          if evt in ('BC', 'ABC') then 
            if evts[_n_-1] = 'FE' then 
              do ;
                flags[_n_] = '1';
                flags[_n_-1] = '1';
              end;
      end;
    
      if not last.id then do;
        put 'ERROR: ' id= 'group size larger than array size';
        stop;
      end;
    
      * apply flag value computed for each row of the group;
      do _n_ = 1 to _n_;
        set have;
        flag = flags[_n_];
        output;
      end;
    
      drop _:;
    run;