topic date and time on excel file in New SAS User

date and time on excel file

mmea — Wed, 03 Feb 2021 11:06:37 GMT

Hi I have several proc reports that should be in an Excel file


ODS EXCEL FILE="F:\COVID-19\ \Data\COVID-19  Report(%sysfunc(date(),date9.) %sysfunc(time(),HHMM.)).xlsx" 
	options(sheet_name = 'Tabel 1'
			embedded_titles = 'yes' 
			embedded_footnotes = 'yes' 
            orientation = 'landscape'   
			)  
	author='dsjfsjdk ';

ods noproctitle;
ods noptitle;
title;

I Start it like this - but it gives me this error:

error "A component of directory-name is not a directory"

I put the right directory, what can the problem be?

Re: date and time on excel file

Kurt_Bremser — Wed, 03 Feb 2021 12:07:46 GMT

The colon (created by the HHMM format) is not allowed in Windows file names.

Instead of

%sysfunc(time(),HHMM.)

use

%sysfunc(compress(%sysfunc(time(),hhmm.),:))

And I strongly recommend to NOT use the DATE9 format in filenames, use YYMMDDN8 or YYMMDD10 instead. This sorts nicely.

Re: date and time on excel file

mmea — Wed, 03 Feb 2021 12:17:07 GMT

Okay thanks!

How can I do it if I want the time to give me just hour like this "13:00"

Re: date and time on excel file

Kurt_Bremser — Wed, 03 Feb 2021 12:40:50 GMT

Use the INTNX function to align times to full hours:

data _null_;
ftime = intnx('hour',time(),0,'b');
cftime = compress(put(ftime,hhmm.),":");
call symputx("ftime",cftime);
run;

%put &=ftime.;

I used the DATA _NULL_ to keep the code readable (I don't like SYSFUNC avalanches).