DATA Step, Macro, Functions and more

Calculate length of time job runs-over midnight

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Calculate length of time job runs-over midnight

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





Accepted Solutions
Solution
‎03-06-2017 01:25 PM
Regular Contributor
Posts: 222

Re: Calculate length of time job runs-over midnight

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)

View solution in original post


All Replies
Solution
‎03-06-2017 01:25 PM
Regular Contributor
Posts: 222

Re: Calculate length of time job runs-over midnight

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)

Occasional Contributor
Posts: 14

Re: Calculate length of time job runs-over midnight

Posted in reply to AhmedAl_Attar
Thank you for quick response!
Trusted Advisor
Posts: 1,586

Re: Calculate length of time job runs-over midnight

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

Super User
Posts: 11,343

Re: Calculate length of time job runs-over midnight

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

Occasional Contributor
Posts: 14

Re: Calculate length of time job runs-over midnight

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)

 

Regular Contributor
Posts: 222

Re: Calculate length of time job runs-over midnight

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

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 221 views
  • 0 likes
  • 4 in conversation