topic Re: Calculate length of time job runs-over midnight in SAS Programming

Calculate length of time job runs-over midnight

labin — Mon, 06 Mar 2017 17:58:07 GMT

%let End_Date_Time = %sysfunc(datetime());

%let Start_Date_Time = %sysfunc(datetime());

%put Duration = %sysfunc(putn(%sysevalf(&datetime_end.-&datetime_start.),mmss.)) (mm:ss) ;

The problem is when this job starts before midnight and ends the next day. I am getting " -1437 (mm:ss)". How do I get this to calculate the real minutes/seconds between the two dates. See example date times.

%let datetime_start = %Sysfunc( InputN( 05MAR2017:23:59:12 , DateTime14 ) ) ;

%let datetime_end = %Sysfunc( InputN( 06MAR2017:00:01:31 , DateTime14 ) ) ;

Re: Calculate length of time job runs-over midnight

AhmedAl_Attar — Mon, 06 Mar 2017 18:15:18 GMT

Hi,

I think if you change your datatime format length , you should get the correct results using your existing code

Here is a woking example:

%let datetime_start = %Sysfunc( InputN( 05MAR2017:23:59:12 , DateTime18. ) ) ;
%let datetime_end = %Sysfunc( InputN( 06MAR2017:00:01:31 , DateTime18. ) ) ;

%put Duration = %sysfunc(putn(%sysevalf(&datetime_end.-&datetime_start.),mmss.)) (mm:ss) ; --> Duration = 2:19 (mm:ss)

Re: Calculate length of time job runs-over midnight

Shmuel — Mon, 06 Mar 2017 18:19:49 GMT

Change line:

%put Duration = %sysfunc(putn(%sysevalf(&datetime_end.-&datetime_start.),mmss.)) (mm:ss) ;

To:

%put Duration = %sysfunc(putn(%sysevalf(&datetime_end.-&datetime_start.),time9.)) (hh:mm:ss) ;

Re: Calculate length of time job runs-over midnight

ballardw — Mon, 06 Mar 2017 18:23:37 GMT

When I run:

%let datetime_start = %Sysfunc( InputN( 05MAR2017:23:59:12 , DateTime14 ) ) ;

%let datetime_end = %Sysfunc( InputN( 06MAR2017:00:01:31 , DateTime14 ) ) ;

%put Duration= %sysfunc(putn(%sysevalf(&datetime_end.-&datetime_start.),mmss.)) (mm:ss) ;

Result is

Duration= 55:00 (mm:ss)

To get a value like -1437 then the End has to be smaller than the start, i.e: your END value is before the START..

Re: Calculate length of time job runs-over midnight

labin — Mon, 06 Mar 2017 18:25:46 GMT

Thank you for quick response!

Re: Calculate length of time job runs-over midnight

labin — Mon, 06 Mar 2017 21:20:48 GMT

This code works:

%let datetime_start = %Sysfunc( InputN( 05MAR2017:23:59:12 , DateTime18. ) ) ;
%let datetime_end = %Sysfunc( InputN( 06MAR2017:00:01:31 , DateTime18. ) ) ;
%let Duration = %sysfunc(putn(%sysevalf(&datetime_end-&datetime_start.),mmss.)) (mm:ss) ;
%put PROCESSING TIME: %sysfunc(putn(%sysevalf(&datetime_end-&datetime_start.),mmss.)) (mm:ss) ;

However, when I change to datetime(), it fails.

%let datetime_start = %sysfunc(DATETIME(),Datetime18.) ;
%put &datetime_start;
%let datetime_end = %sysfunc(DATETIME(),Datetime18.) ;
%put &datetime_end;
%let Duration = %sysfunc(putn(%sysevalf(&datetime_end-&datetime_start.),mmss.)) (mm:ss) ;
%put PROCESSING TIME: %sysfunc(putn(%sysevalf(&datetime_end-&datetime_start.),mmss.)) (mm:ss) ;

Errors:

1 %let datetime_start = %Sysfunc( InputN( 05MAR2017:23:59:12 , DateTime18. ) ) ;
2 %let datetime_end = %Sysfunc( InputN( 06MAR2017:00:01:31 , DateTime18. ) ) ;
3 %let Duration = %sysfunc(putn(%sysevalf(&datetime_end-&datetime_start.),mmss.)) (mm:ss) ;
4 %put PROCESSING TIME: %sysfunc(putn(%sysevalf(&datetime_end-&datetime_start.),mmss.)) (mm:ss) ;
PROCESSING TIME: 2:19 (mm:ss)
5 %let datetime_start = %sysfunc(DATETIME(),Datetime18.) ;
6 %put &datetime_start;
06MAR17:14:19:53
7 %let datetime_end = %sysfunc(DATETIME(),Datetime18.) ;
8 %put &datetime_end;
06MAR17:14:19:53
9 %let Duration = %sysfunc(putn(%sysevalf(&datetime_end-&datetime_start.),mmss.)) (mm:ss) ;
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
06MAR17:14:19:53-06MAR17:14:19:53
ERROR: %SYSEVALF function has no expression to evaluate.
ERROR: Argument 1 to function PUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC or
%QSYSFUNC function reference is terminated.
10 %put PROCESSING TIME: %sysfunc(putn(%sysevalf(&datetime_end-&datetime_start.),mmss.)) (mm:ss) ;
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
06MAR17:14:19:53-06MAR17:14:19:53
ERROR: %SYSEVALF function has no expression to evaluate.
ERROR: Argument 1 to function PUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC or
%QSYSFUNC function reference is terminated.
PROCESSING TIME: (mm:ss)

Re: Calculate length of time job runs-over midnight

AhmedAl_Attar — Mon, 06 Mar 2017 21:47:47 GMT

You don't need to format the datetime_start and datetime_end macro variables. Keep them in their native numeric value, to avoid arythmetic operation errors.

You just need to format them at the end during Final/Summary phase.

You original formula worked, because you converted the datetime text value into a numeric value using the INPUTN function.

Your second formula fails, because you are using text with ':' in a mathematical formula.

Hope this helps,

Ahmed