Macro variable in excel file name using ods

[sasecn]

Hello:

I couldn't create an excel file that has macro value in the file name. I got error:

ERROR: Physical file does not exist, H:\\test 16\07\2020.xlsx.

I tested without using the &run_date macro variable, the code worked fine.

Thanks for any help!

Data _null_;
	call symput("run_yyyy"  , put(year(date()),      Z4.));
	call symput("run_mon"   , put(month(date()),     Z2.));
	call symput("run_day"   , put(day(date()),       Z2.));
	call symput("run_hour"  , put(hour(datetime()),  Z2.));
	call symput("run_min"   , put(minute(datetime()),Z2.));
run;


%let run_date = &run_day./&run_mon./&run_yyyy.;
/* I also tried:
%let filepath = " PATH...\test &run_date..xlsx";
ods excel file=&filepath
*/
ods excel file=" PATH...\test &run_date..xlsx"
options(embedded_titles="yes" sheet_name="test" sheet_interval='none'); 
title "test";
proc print data=sashelp.cars;
run;
title;
ods excel close;

[PaigeMiller]

ERROR: Physical file does not exist, H:\\test 16\07\2020.xlsx.

Typically on Windows computers, drive letters do not have double-slashes after the letter and colon.

Also, if folder test 16 does not exist, or folder test 16\07 does not exist, then you get the error.

So really, this is not a macro variable error at all. It is that you haven't constructed the path and file name to be in a folder that actually exists.

[sasecn]

Thanks for your reply!

I am taking H:\\ as an example, my real path is a shared folder "\\PATH ...\test &run_date.xlsx". I am sure i have access to the folder. I tested that if i try "\\PATH ...\test.xlsx", the code worked. It created the excel file named "test.xlsx". I just wanted to created the excel file named by today's date, e.g "test 16\07\2020.xlsx"

[PaigeMiller]

Access to the folder is not the issue. You have named it wrong.

Windows does not recognize H:\\ (with two slashes)

It does recognize \\path (with two slashes)

So you have to get the folder name correct, and you have to use a folder that actually exists (if folder \\path\test 16\ doesn't exist, then you get the error)

Lastly, a side issue ... Normally people create hierarchy that is Year\Month\Date, I really don't see a benefit of the way you are doing it which is Date/Month/Year, but I guess that's up to you.

[ballardw]

You may also want to ensure you do not have any leading spaces inside the filename indicated by the quotes.

This implies that you possibly have a space in your filename

ods excel file=" PATH...\test &run_date..xlsx"

assuming the PATH is supposed to be a macro variable as well.

I found out when we switched to OneDrive that file names became particular about a space. Where previously

file = " d:\folder\file.xlxs" worked, note this is without any macro variable, I had similar errors. The correction was to use file= "d:\folder\file.xlsx" .

[Kurt_Bremser · Accepted solution]

Simply do:

%let run_date = %sysfunc(putn(%sysfunc(date()),yymmddd10.));

This replaces the (bad) slashes with (allowed) dashes, and also changes the date order to YMD, so the files sort automatically in chronological order.

[Kurt_Bremser]

And for future convenience, replace the blank with an underline:

 PATH...\test_&run_date..xlsx

See Maxim 44.

[Tom]

Do NOT try to make filenames that have / or \ as part of the name.

%let run_date = &run_yyyy._&run_mon._&run_day ;

PS When using dates as part of a filename always use YMD order instead of MDY or DMY. Then the names will sort lexicographically in chronological order and also avoid confusion  about which part is the month and which is the day.

[sasecn]

Thanks all for the reply!

Now, i understand that the slash in my original date caused the path reference problems. 

Thanks again!