SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Calculate difference in minutes for events to confirm chronological order?

Accepted Solution Solved
Reply
Contributor SM1
Contributor
Posts: 43
Accepted Solution

Calculate difference in minutes for events to confirm chronological order?

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.

   

 


Accepted Solutions
Solution
‎09-11-2017 05:22 PM
Super User
Posts: 19,080

Re: Calculate difference in minutes for events to confirm chronological order?


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));

View solution in original post


All Replies
Solution
‎09-11-2017 05:22 PM
Super User
Posts: 19,080

Re: Calculate difference in minutes for events to confirm chronological order?


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));
Contributor SM1
Contributor
Posts: 43

Re: Calculate difference in minutes for events to confirm chronological order?

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

 

 

Super User
Posts: 19,080

Re: Calculate difference in minutes for events to confirm chronological order?

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

Contributor SM1
Contributor
Posts: 43

Re: Calculate difference in minutes for events to confirm chronological order?

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

Contributor SM1
Contributor
Posts: 43

Re: Calculate difference in minutes for events to confirm chronological order?

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

Respected Advisor
Posts: 4,132

Re: Calculate difference in minutes for events to confirm chronological order?

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 306 views
  • 4 likes
  • 3 in conversation