Calculate difference in minutes for events to confirm chronological order?

[SM1]

I am working with date and time variables for 7 events that occur in a specific chronological order. As part of the QA process, I need to create a flag for a record in which any of the 7 events appear to be out of order. My data exists as a wide file (so information about all 7 events in the same row or observation).

Variables include:

reportID = unique report ID (1 report = 7 events)

Event1_Date, Event2_Date, Event3_Date, Event4_Date, Event5_Date, Event6_Date, Event7_Date - SAS dates, format date9.

Event1_Time, Event2_Time, Event3_Time, Event4_Time, Event5_Time, Event6_Time, Event7_Time -  SAS time, format time5.

I would prefer to NOT combine the date and time variables into a single datetime variable. While most reports have comprehensive date and time variables, there are some missing time variables (the date variables are 99.9% populated). Due to data errors, there are some incorrect date variables that appear to be both random and somewhat systematic (e.g., if the series of events spans 2 days that begins on the last day of a month and ends on the 1st day of the next month, there is a much greater likelihood of having an incorrect date variable for 1 of the events).  So while a date might be incorrect, the corresponding time appears to be correct.

I tried using the following syntax to begin creating a flag for events that might have incorrect/bad time variable values.

data want;

     data have;

    minute1=INTCK('minute',event1_time,event2_time);
    minute2=INTCK('minute',event2_time,event3_time);
    minute3=INTCK('minute',event3_time,event4_time);
    minute4=INTCK('minute',event4_time,event5_time);
    minute5=INTCK('minute',event5_time,event6_time);
    minute6=INTCK('minute',event6_time,event7_time);

run;

This syntax works - except for times that span midnight.

For example, if event1time = 23:50 and event2_time = 0:15 then minute1 = -1415 (when it should be 25).

Is there a way have the syntax work with times that span midnight, without combining the date and time variables into a single variable?

Any ideas will be most appreciated.

[Reeza · Accepted solution]

---

@SM1 wrote:

Is there a way have the syntax work with times that span midnight, without combining the date and time variables into a single variable?

---

You can check if the dates are the same first and then apply the difference logic. You're not combining the variables but you need to use both otherwise how do you know if it's one, two or more 'nights' that were crossed? 

It seems easier to just combine them in the function personally...

    minute1=INTCK('minutedt',dhms(event1_date, 0, 0, event1_time), dhms(event2_day, 0, 0, event2_time));

[SM1]

@Reeza

Yes, that makes sense. I tried running the suggested syntax, but it failed. Do I need to define the suggested interval (i.e., 'minutedt') or is that a default interval that should be recognized?

Thanks!

[Reeza]

Check the docs for the valid options. You can also subtract the two values and divide by 60...that seems easier to me personally

[SM1]

@Reeza

I did check the docs and didn't find that interval listed.

Subtracting and then dividing by 60 should work. I'll do that now.

Thanks again!

[SM1]

@Reeza

Your syntax required a slight tweak to work:

 minute1=INTCK('minute',dhms(event1_date, 0, 0, event1_time), dhms(event2_date, 0, 0, event2_time));

Thanks for the help!

[Patrick]

@SM1

I believe things will become much simpler if you're using SAS datetime values so I'd combine your variables.

Below a code sample how this could work. 

data want;
  data have;

  array dttm {7} 8 _temporary_;
  array dt {7}  Event1_Date, Event2_Date, Event3_Date, Event4_Date, Event5_Date, Event6_Date, Event7_Date;
  array t {7}   Event1_Time, Event2_Time, Event3_Time, Event4_Time, Event5_Time, Event6_Time, Event7_Time;

  dttm[1]=sum(dt[1]*3600,t[1]);
  do _i=2 to dim(dttm);
    dttm[_i]=sum(dt[_i]*3600,t[_i]);
    if dttm[_i-1]>=dttm[_i] then
      do;
        DQ_issue_flg=1;
        leave;
      end;
  end;

run;

Depending on what you want you could now build on above i.e. by implementing an exception table where you write each combination of date/time values having a DQ issue as a separate row to an exception table.