topic Re: creating flag variable based on dates in SAS Programming

creating flag variable based on dates

jins — Mon, 11 Nov 2019 22:46:37 GMT

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 .

Re: creating flag variable based on dates

Reeza — Mon, 11 Nov 2019 22:57:54 GMT

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/ta-p/424354

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 .

Re: creating flag variable based on dates

novinosrin — Mon, 11 Nov 2019 23:20:47 GMT


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;

Re: creating flag variable based on dates

novinosrin — Mon, 11 Nov 2019 23:34:14 GMT

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;

Re: creating flag variable based on dates

hashman — Tue, 12 Nov 2019 02:33:00 GMT

@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:

for a given ID, determine if there are any 2 records with Values=1 whose Time values lie within 15-60 minutes
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.

Re: creating flag variable based on dates

jins — Tue, 12 Nov 2019 12:31:49 GMT

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

Re: creating flag variable based on dates

novinosrin — Tue, 12 Nov 2019 13:11:29 GMT

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!