topic Re: calculate minute difference between 2 timestamps (character variables) in SAS Programming

calculate minute difference between 2 timestamps (character variables)

urban58 — Mon, 27 Jun 2022 12:35:36 GMT

Hello SAS community,

I'm trying to find the minutes between 2 timestamps (both character variables) in a large SAS dataset I received,

e.g.

id starttime endtime
1 2021-10-27 10:11:01 2021-10-27 10:19:33

I know intck function can do it but all the examples I see have date in a date9 format

mins_between = intck('minute',starttime,endtime);

but I just get

Character values have been converted to numeric values at the places given by: (Line):(Column).
171:31 171:46
NOTE: Invalid numeric data, starttime='2021-10-27 10:11:01' , at line 171 column 31.
NOTE: Invalid numeric data, endtime='2021-10-27 10:19:33' , at line 171 column 46.

Can someone please help!

Thanks,

Margaret

Re: calculate minute difference between 2 timestamps (character variables)

Tom — Mon, 27 Jun 2022 12:46:13 GMT

You need to convert your strings into DATETIME values.

You could try using ANYDTDTM informat, but that is a GUESSING procedure.

The most reliable way is to convert the date part of the string and the time part separately and recombine into a datetime value. That way you can ensure the date string is evaluated using YMD order and not some other order.

Since datetime values are stored in seconds to convert the difference to minutes just divide by 60 since there are 60 seconds in a minute.

startdt = dhms(input(startime,yymmdd10.),0,0,input(scan(starttime,2,' '),time8.));
enddt = dhms(input(endtime,yymmdd10.),0,0,input(scan(endtime,2,' '),time8.));
mintues = (enddt - starrdt) / 60 ;

If you want an integer then decide how want to convert 5 minutes and 30 seconds into an integer number of minutes. Is that 5 minutes? then use INT() . Should that be 6 minutes then use ROUND().

Re: calculate minute difference between 2 timestamps (character variables)

PaigeMiller — Mon, 27 Jun 2022 12:51:37 GMT

If your starttime and endtime are really character strings, then you can't do any subtraction at all.

If you are creating these variables as character strings, that's a mistake. Read them in or create them as numeric (date/time values, which are in seconds), then do the subtraction and divide by 60 seconds in a minute. The informat :ymddttm18. will read the values in as SAS date/time values.

data have;
    input id starttime & :ymddttm18. endtime & :ymddttm18. ;
cards;
1     2021-10-27 10:11:01      2021-10-27 10:19:33
;
data want;
    set have;
    minute_difference=(endtime-starttime)/60;
run;

Re: calculate minute difference between 2 timestamps (character variables)

urban58 — Mon, 27 Jun 2022 13:05:40 GMT

Thank you Tom for you quick comprehensive reply. I like your answer as I don't have to make changes to other variables I've created from these timestamps and it works beautifully.

Also thanks for thinking of the rounding function as that was my next thought when I saw the output!

Really appreciate it.

Margaret

Re: calculate minute difference between 2 timestamps (character variables)

urban58 — Mon, 27 Jun 2022 13:08:32 GMT

Thank you Paige for your reply which of course would have been the correct way to do it starting out.

Margaret