Picture format for negative time duration

[Oligolas]

Hi,

I'm wondering if you could please help with a custom time format that could represent negative durations (end before start)

That's what I've got, Time5. works but not my picture format

DATA have;
   Bloodsample='2020-01-12T15:00'; Infusion='2020-01-12T15:10';output;
   Bloodsample='2019-11-18T14:40'; Infusion='2019-11-18T10:30';output;
   Bloodsample='2019-11-13T15:13'; Infusion='2019-11-18';output;
RUN;

PROC FORMAT;
   picture mytime 
      low - < 0 ='%Hh %0Mmn' (datatype=time prefix='-')
      0 - high='%Hh %0Mmn' (datatype=time)
      ;
run;

DATA want;
   set have;
   if length(Bloodsample) eq 16 and length(Infusion) eq 16 then do;
       dur=input(Infusion,b8601DT.) - input(Bloodsample,b8601DT.); 
       time5=put(dur,time5.);
       picture=put(dur,mytime.); *ERROR for Infusion < Bloodsample;
   end;
RUN;
PROC PRINT;RUN;

[Reeza]

Works fine for me, except for the last record but that's because you have data quality issues - a datetime with no time component.
Please clarify what isn't working in detail.

I'm wrong, your code does have an issue.

[data_null__]

This works to put the minus sign.   

'-%Hh %0Mmn'

Is this what what you wanted?  Because TIME5 is way different.

[ballardw]

---

@Oligolas wrote:

Hi,

I'm wondering if you could please help with a custom time format that could represent negative durations (end before start)

That's what I've got, Time5. works but not my picture format

DATA have;
   Bloodsample='2020-01-12T15:00'; Infusion='2020-01-12T15:10';output;
   Bloodsample='2019-11-18T14:40'; Infusion='2019-11-18T10:30';output;
   Bloodsample='2019-11-13T15:13'; Infusion='2019-11-18';output;
RUN;

PROC FORMAT;
   picture mytime 
      low - < 0 ='%Hh %0Mmn' (datatype=time prefix='-')
      0 - high='%Hh %0Mmn' (datatype=time)
      ;
run;

DATA want;
   set have;
   if length(Bloodsample) eq 16 and length(Infusion) eq 16 then do;
       dur=input(Infusion,b8601DT.) - input(Bloodsample,b8601DT.); 
       time5=put(dur,time5.);
       picture=put(dur,mytime.); *ERROR for Infusion < Bloodsample;
   end;
RUN;
PROC PRINT;RUN;

---

 The %H directive is the HOUR of the 24-hour clock time, not an interval. So for your second value which is showing "19h 50mn" that is 4 hours and 10 minutes BEFORE the end of the 24-hour clock. Just like %m shows the month number, not the interval from a previous date.

You don't get a result for the third row because you show an invalid datetime value (no time component)

[Oligolas]

Hi, thanks the 3rd row is fine, sorry for the ambiguity. My problem is the 2nd row.

@ballardw So, do you mean minus signs are not allowed because the %H directive is showing hours?

@data_null__ indeed with '-%Hh %0Mmn' I get a minus sign output " -19h 50mn" with datatype=datetime,

but I'd like to modify the picture format to get "-4h 10mn" in the 2nd row.

in a datastep I would fix it with 

PROC FORMAT;
   picture mytime 
      low - high ='%Hh %0Mmn' (datatype=time);
RUN;

and

want=cats(ifc(dur<0,'-',''),put(abs(dur),mytime.));

But how can I do this with the picture format?

[Tom]

You can't do it with PICTURE because 3 hours before midnight is hour 21 not hour -3.

You need to create your own function to produce the strings you want using PROC FCMP and call that function in the format definition.

Or just make your original data step check the sign of the difference and then you can use your picture format on the absolute value of the difference.  So something like this:

picture=put(abs(dur),mytime.);
if . < dur <0 then picture=cats('-',picture);

[Ksharp · Accepted solution]

It looks like PICTURE don't take negative value as a valid time value .

And I am also surprised , if you use HOUR(-15000) ,MINUTE(-15000) would get different result from TIME5. format.

I suggest you feedback to sas support.

Here is a workaround way if you like it .

DATA have;
   Bloodsample='2020-01-12T15:00'; Infusion='2020-01-12T15:10';output;
   Bloodsample='2019-11-18T14:40'; Infusion='2019-11-18T10:30';output;
   Bloodsample='2019-11-13T15:13'; Infusion='2019-11-18';output;
RUN;


proc fcmp outlib=work.math.func;
function mytime(x) $;
length mytime $ 10;
 if not missing(x)then mytime=cat(int(x/3600),'h ',put(abs(mod(x,3600)/60),z2.),'mn');
 return (mytime);
endsub;
run;

options cmplib=work.math;
PROC FORMAT;
   value mytime 
      low - high=[mytime()]
      ;
run;

DATA want;
   set have;
   if length(Bloodsample) eq 16 and length(Infusion) eq 16 then do;
       dur=input(Infusion,b8601DT.) - input(Bloodsample,b8601DT.); 
       time5=put(dur,time5.);
       picture=put(dur,mytime8.); *ERROR for Infusion < Bloodsample;
   end;
RUN;
PROC PRINT;RUN;

[Oligolas]

Hi,

thank you all for your posts that led to this powerful demonstration. 

I think for now a single line data step check is the easiest solution.

But wow, I'm astonished by the combination of fcmp with the format, never thought of that.

One is able to logically adjust the format on the fly, it's amazing.

I'll definitely use it in the future and optimize some processes

Thank you very much