BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SM1
Obsidian | Level 7 SM1
Obsidian | Level 7

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.

   

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@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

6 REPLIES 6
Reeza
Super User

@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
Obsidian | Level 7 SM1
Obsidian | Level 7

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

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
Obsidian | Level 7 SM1
Obsidian | Level 7

@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
Obsidian | Level 7 SM1
Obsidian | Level 7

@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
Opal | Level 21

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

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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