creating flag variable based on dates

[jins]

What will be the best approach to find 2 values( values=1) 15-60 minutes apart for each ID? The objective is to obtain a flag variable for each ID when this criteria is met.

ID     Time        Values

1      11.30       1

1      11.45       1

1      12.00       1 

1      13.00        .

1      13.30       1

1      15.00       1

2      9.30         .   

2      9.45         1

2      10.30       .

2      11.45       1

2      13.00       1

2      15.00       1

2      15.30       1

2      16.30       .

[Reeza]

Times are stored in seconds. 

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/...

This should get you started but isn't a complete solution. 

data want;
set have;

by ID;

values = dif(time);
values_min = dif(time)/(60);

if first.id then call missing(of values:);


run;

---

@jins wrote:

What will be the best approach to find 2 values( values=1) 15-60 minutes apart for each ID? The objective is to obtain a flag variable for each ID when this criteria is met.

ID     Time        Values

1      11.30       1

1      11.45       1

1      12.00       1 

1      13.00        .

1      13.30       1

1      15.00       1

2      9.30         .   

2      9.45         1

2      10.30       .

2      11.45       1

2      13.00       1

2      15.00       1

2      15.30       1

2      16.30       .

---

[novinosrin]

data have;
input ID     Time :time5.        Values;
format time time5.;
cards;
1      11.30       1
1      11.45       1
1      12.00       1 
1      13.00        .
1      13.30       1
1      15.00       1
2      9.30         .   
2      9.45         1
2      10.30       .
2      11.45       1
2      13.00       1
2      15.00       1
2      15.30       1
2      16.30       .
; 

data want;
set have;
by id values notsorted;
dif=ifn(not first.values and values=1, intck('minute',lag(time),time),.);
flag=15<=dif<=60;
run;

[novinosrin]

With some little bit of gymnastics, I would like a cleaner log

data want;
 do _n_=1 by 1 until(last.values);
  set have;
  by id values notsorted;
  if not values then continue;
  if _n_=1 then _t=time;
  else do;
   dif=intck('minute',_t,time);
   Flag=15<=dif<=60;
   _t=time;
  end;
  output;
 end;
 drop _:;
run;

/*Or the whole*/

data want;
 do _n_=1 by 1 until(last.values);
  set have;
  by id values notsorted;
  if not values then do;output; continue;end;
  if _n_=1 then _t=time;
  else do;
   dif=intck('minute',_t,time);
   Flag=15<=dif<=60;
   _t=time;
  end;
  output;
 end;
 drop _:;
run;

[hashman]

@jins: 

Frankly, I'm not sure at all that I comprehend your criteria correctly without a sample output that would include the flag being sought. Therefore, I can't help but marvel at the perceptive powers of @novinosrin and @Reeza for whom the goal of the exercise is apparently clear. 

To wit, my interpretation of your phrase "... find 2 values( values=1) 15-60 minutes apart for each ID? The objective is to obtain a flag variable for each ID when this criteria is met." would be:

1. for a given ID, determine if there are any 2 records with Values=1 whose Time values lie within 15-60 minutes
2. if so, flag the entire ID (i.e. all of its records) with Flag=1 indicating that #1 is true; otherwise, leave it with Flag=.  

If my interpretation is correct, both ID=1 and ID=2 would be marked with Flag=1; but then it raises the question why you wouldn't present a case where the criterion is not met in you sample input - normally, an OP would present data with both outcomes. At any rate, in the unlikely case it is correct, a solution can be approached this way (note that I've changed the last 2 records for ID=2 to make it fail the criterion):

data have ;                                                                                                                             
  input id time :time5. values ;                                                                                                        
  format time time5. ;                                                                                                                  
  cards ;                                                                                                                               
1  11.30  1                                                                                                                             
1  11.45  1                                                                                                                             
1  12.00  1                                                                                                                             
1  13.00  .                                                                                                                             
1  13.30  1                                                                                                                             
1  15.00  1                                                                                                                             
2   9.30  .                                                                                                                             
2   9.45  1                                                                                                                             
2  10.30  .                                                                                                                             
2  11.45  1                                                                                                                             
2  13.00  1                                                                                                                             
2  15.00  1                                                                                                                             
2  16.30  1                                                                                                                             
2  18.00  .                                                                                                                             
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data want (drop = _:) ;                                                                                                                 
  do _n_ = 1 by 1 until (last.id) ;                                                                                                     
    set have (where=(values)) ;                                                                                                         
    by id ;                                                                                                                             
    if _n_ > 1 then if 15 <= intck ("min", _t, time) <= 60 then flag = 1 ;                                                              
    _t = time ;                                                                                                                         
  end ;                                                                                                                                 
  do until (last.id) ;                                                                                                                  
    set have ;                                                                                                                          
    by id ;                                                                                                                             
    output ;                                                                                                                            
  end ;                                                                                                                                 
run ;                               

Kind regards

Paul D.

[jins]

Thanks and you have got interpretation right and i should have been much clearer.

[novinosrin]

Guru @hashman   First off, Thank you for encouraging words while I am awe of your attention to detail that you always seem to be perfect. One of that great trait I am yet to copy besides many of the solutions of yours that I have already memorized or in others plagiarized. Very eloquent observation here and noteworthy. Cool stuff!