BookmarkSubscribeRSS Feed
Almoha
Calcite | Level 5

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?

10 REPLIES 10
andreas_lds
Jade | Level 19

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

Kurt_Bremser
Super User

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
Super User

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

Astounding
PROC Star

Yes, but that is easily correctible:

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

Ksharp
Super User

Astounding have my word . Thanks, Astounding .  Smiley Happy

Astounding
PROC Star

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
Super User

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
PROC Star

Thanks, I did change that once I noticed.


Haikuo
Onyx | Level 15

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

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
  • 10 replies
  • 1482 views
  • 1 like
  • 6 in conversation