How to create system time in a specific format "Hour_Minute_Second"?

[ybz12003]

Hello,

I have a program below, and I would like to output an Excel file name appending with the program's starting running time.  I found the format is not recognized by the macro.  Please help, thanks.

%let StartTime1 = %sysfunc(datetime(), datetime20.);

ods excel file="Pathway\Test_&starttime1..xlsx";

And the log shows 

Spoiler

SYMBOLGEN: Macro variable STARTFMT resolves to .
53 %let StartTime1 = %sysfunc(datetime(), datetime20.);
54
55 ods excel
55 ! file="Pathway\test_&starttime1..xlsx";
SYMBOLGEN: Macro variable STARTTIME1 resolves to 30JAN2024:17:30:44
ERROR: Physical file does not exist,
Pathway\test_30JAN2024:17:30:\05.xlsx.

Spoiler

Spoiler

[ybz12003]

The STARTTIME1 resolves to 30JAN2024:17:30:44, which is not suitable for the title.  Is there a way to change to "30JAN2024_17_30_44"?  Thanks.

[ballardw · Accepted solution]

If you are going to use this frequently it may be worth writing a custom format. At which point I might recommend using a YYYYMMDD based format so the file names sort properly.

Proc format;
picture datetimeunderscore
low-high='%0d%b%Y_%0H_%0M_%0S' (datatype=datetime);

run;

%let StartTime1 = %sysfunc(datetime(), datetimeunderscore.);
%put starttime is:&starttime1.;

Notes: do not use double quotes around the directives. Those % which are the directives for date, time and datetime pieces get confused with macro statements. The directives are case sensitive.%M is minutes 0 to 59, %m is month 1 to 12. The %H is a 24 hour clock hour. Read the documentation on the Format Picture statement for additional details options.

The %0 means to insert a 0 so 1 hour displays as 01.

[PaigeMiller]

Convert the time to a text string using the PUT function, then use the TRANSLATE function to change : to _

data _null_;
    starttime1=time();
    call symputx('excel_time',translate(put(starttime1,time.),'_',':'));
run;
%put &=excel_time;

[Tom]

You can use TRANSLATE() to replace the characters you don't want with something else. 

But don't use date values in the style produced by the DATE format.  Strings like that will not sort properly 

Also decide whether you want the time the program started, like you said. 

Or the time when you ran the DATETIME() function call, like the code you shared.

1    * When SAS started ;
2    %let ts = %sysfunc(translate(%sysfunc(putn("&sysdate9:&systime"dt,e8601dt19.)),___,T:-));
3    %put &=ts;
TS=2024_01_30_18_56_00
4
5    * NOW ;
6    %let ts = %sysfunc(translate(%sysfunc(datetime(),e8601dt19.),___,T:-));
7    %put &=ts;
TS=2024_01_30_19_03_09