Data Logic

[Almoha]

All,

I have the following data structure .

ID   time(hr)   flag1   flag2

11    2           Y

11    4           Y

11    6  

11    8           Y      

11   10        

22   2            Y

22   4

22   6            Y      

22   8            Y      

22   10           Y        

If 2 or more consecutive time points(time) have a "Y " for flag1 , then I am trying to assig the "Y" to flag2  field as below

The output I am looking for is

ID   time(hr) flag1    flag2

11    2         Y         Y

11    4         Y         Y

11    6  

11    8         Y      

11    10                

22    2         Y

22    4

22    6         Y         Y

22    8         Y         Y

22    10        Y         Y

can anyone help me with the logic?

[andreas_lds]

You can use the method called "Look-Ahead and Look-Back" described in Look-Ahead and Look-Back - sasCommunity

[Kurt_Bremser]

I came to the following solution:

data comp (keep=id h1 flag2 rename=(h1=time));

set have;

by id;

retain flag2 'Y';

flag1_l1 = lag(flag1);

flag1_l2 = lag2(flag1);

time_l1 = lag(time);

id_l1 = lag(id);

id_l2 = lag2(id);

if not first.id then do;

  if flag1 = 'Y' and flag1_l1 = 'Y' then do;

    if flag1_l2 ne 'Y' and (id_l2 = id or id_l2 = .) then do;

      h1 = time_l1;

      output;

    end;

    h1 = time;

    output;

  end;

end;

run;

data want;

merge have comp;

by id time;

run;

[Ksharp]

If I understood your question rightly.

data have;
infile cards truncover;
input ID   time   flag1  $ ;
cards;
11    2           Y
11    4           Y
11    6  
11    8           Y      
11   10        
22   2            Y
22   4
22   6            Y      
22   8            Y      
22   10           Y     
;
run; 
data want;
 set have;
 by id flag1 notsorted;
 if not (first.flag1 and last.flag1) then flag2='Y';
run;

Xia Keshan

[Kurt_Bremser]

But that would also mark 2 successive empty flag1 with flag2 = 'Y'.

[Astounding]

Yes, but that is easily correctible:

if (flag1='Y') and not (first.flag1 and last.flag1) then flag2='Y';

[Ksharp]

Astounding have my word . Thanks, Astounding . 

[Astounding]

A nice problem to start the week.  One approach:

data want;

   yes_count=0;

  do until (last.id or flag1=' ');
      set have;

      by id;

      if flag1='Y' then yes_count + 1;

   end;

   do until (last.id or flag1=' ');

      set have;

      by id;

      if flag1='Y' and yes_count >= 2 then flag2='Y';

      else flag2=' ';

      output;

   end;

   drop yes_count;

run;

The top loop reads a bunch of records, and determines when FLAG2 should be Y for this records.  The bottom loop reads the same records and assigns FLAG2.

Good luck.

[Kurt_Bremser]

I knew there was a DWO loop solution, just couldn't get myself to  find it!

But you need to compare >=2, or you will only catch three Ys in succession

Edit: saw you already fixed it.

[Astounding]

Thanks, I did change that once I noticed.

[Haikuo]

OK, look ahead and look back:

data have;

     infile cards truncover;

     input id time flag $;

     cards;

11    2 Y

11    4 Y

11    6  

11    8 Y      

11   10

22   2 Y

22   4

22   6 Y      

22   8 Y      

22   10 Y

;

data want;

     set have;

     by id;

     set have(firstobs=2 keep=flag rename=flag=_f) have(obs=1 drop=_all_);

     if not last.id and flag='Y' and _f='Y' or flag='Y' and lag(flag)='Y' then

           flag2='Y';

     drop _f;

run;

Haikuo