Determining correct elapsed time using the SAS datepart and timepart

[wlierman]

I am measuring the time between the creation_date (Created_on) and the time the person was reached (1st attempt; 2nd attempt; 3rd attempt).  The metric is that the time between the contact attempt (1st; 2nd; or 3rd) and the time the record was Created_on is within 24 hours.

So the sequence should be Created_on and then measure the time from the contact attempt

                Time_1st_attempt - Created_on <= 24

                 Time_2nd_attempt - Created_on <= 24

                 Time_3rd_attempt - Created_on <= 24

There are times when the record was contacted successfully first and then the  record was Created_on in the record (so that is the negative time elapsed).

But I forgot that for a few the dates are different. For example, one record was successfully contacted on the third attempt on 8/20/2020 but the record was Created_On  8/22/2020.

How can this 24 hours of the date be captured.  Usually the date of successful contact and Created record are the same date - but there are a few.

Here is my code:

Data ARIAS.Correct_hours (drop = First_Attempt Second_Attempt Third_Attempt Created_Date Person_ID Contact_ID Case_ID);
   Format Hours_between_1stAttempt_Create 3.2;
   Format Hours_between_2ndAttempt_Create 3.2;
   Format Hours_between_3rdAttempt_Create 3.2;
Set Arias.Time_Part;
If First_Attempt ne . and Second_Attempt eq . and Third_Attempt eq . then do;
         Hours_between_1stAttempt_Create = mod(/*24+*/(First_Attempt - Created_Date)/3600,24);
        end;
Else If First_Attempt ne . and Second_Attempt ne . and Third_Attempt eq . then do;
        Hours_between_2ndAttempt_Create = mod(/*24+*/(Second_Attempt - Created_Date)/3600,24);
       end;
Else If Third_Attempt ne . then do;
        Hours_between_3rdAttempt_Create = mod(/*24+*/(Third_Attempt - Created_Date)/3600,24);
        end;
run;

I have attached a small csv file that illustrates some of the problems in the data set.

I would appreciate your expertise in demonstrating the correct way to handle this data measurement.

Thank you.

Walt Lierman

[mkeintz]

@wlierman 

I'd be happy to help you.  Can you please set up sample data in the form of a sas data step (not csv - it's too much work for me)?

[wlierman]

I'll try to set somethig up. It will be a small data set.

[wlierman]

I can't send the output dataset, since it is on a SAS server and I am remoting on Citrix.

I appreciate your willingness to help, but I will repost the question. I'll keep working a little to see if is possible to get the dataset via remote connection.

Thank you.

Walt Lierman

[Tom · Accepted solution]

---

@wlierman wrote:
I can't send the output dataset, since it is on a SAS server and I am remoting on Citrix.

I appreciate your willingness to help, but I will repost the question. I'll keep working a little to see if is possible to get the dataset via remote connection.

Thank you.

Walt Lierman

---

The request was just to send the data in a usable form instead of pasted into an attached file.  Fortunately this website lets you preview some attachments without downloading them.  So it looks like you have separate DATE and TIME values.  Just use them to make DATETIME values.  Then the difference between the two will be in seconds.

So here are some dates from a couple of rows that I pulled from the preview of your attachment.  So once I have the separate DATE and TIME values I can use the DHMS() function to make a DATETIME value.  Then simple subtraction will find the difference in seconds. Which can then be easily compared to the number of seconds in 24 hours.

data have;
  input date1 time1 date2 time2 date3 time3 ;
  informat date: ddmmyy. time: time. ;
  datetime1=dhms(date1,0,0,time1);
  datetime2=dhms(date2,0,0,time2);
  datetime3=dhms(date3,0,0,time3);
  diff1 = datetime2 - datetime1;
  diff2 = datetime3 - datetime1;
  flag1 = (0 <= diff1 <= '24:00't);
  flag2 = (0 <= diff2 <= '24:00't);
  format date: yymmdd10. time: tod8. datetime: datetime19. ;
  format diff: time12.;
cards;
24/08/2020 9:52:00AM 24/08/2020 11:30:00AM . .
15/07/2020 9:38:00AM 16/07/2020 9:30:00AM 17/07/2020 2:45:00PM
;

proc print;
run;

Results:

Obs        date1      time1        date2      time2        date3      time3

 1    2020-08-24   09:52:00   2020-08-24   11:30:00            .          .
 2    2020-07-15   09:38:00   2020-07-16   09:30:00   2020-07-17   14:45:00

Obs             datetime1              datetime2              datetime3

 1     24AUG2020:09:52:00     24AUG2020:11:30:00                      .
 2     15JUL2020:09:38:00     16JUL2020:09:30:00     17JUL2020:14:45:00

Obs          diff1           diff2    flag1    flag2

 1         1:38:00               .      1        0
 2        23:52:00        53:07:00      1        0

[wlierman]

Tom,

Thank you for your assistance with this. The date and time part is challenging for me. Thanks again. Do you have any SAS / SUGI paper that you would recommend as a good
foundation?
Take care.

Wlierman