topic Re: Macro variable in excel file name using ods in SAS Programming

Macro variable in excel file name using ods

sasecn — Thu, 16 Jul 2020 15:19:27 GMT

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;

Re: Macro variable in excel file name using ods

PaigeMiller — Thu, 16 Jul 2020 15:28:26 GMT

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.

Re: Macro variable in excel file name using ods

ballardw — Thu, 16 Jul 2020 15:34:09 GMT

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

Re: Macro variable in excel file name using ods

sasecn — Thu, 16 Jul 2020 15:34:46 GMT

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"

Re: Macro variable in excel file name using ods

PaigeMiller — Thu, 16 Jul 2020 15:59:53 GMT

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.

Re: Macro variable in excel file name using ods

Kurt_Bremser — Thu, 16 Jul 2020 15:53:22 GMT

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.

Re: Macro variable in excel file name using ods

Kurt_Bremser — Thu, 16 Jul 2020 15:55:25 GMT

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

 PATH...\test_&run_date..xlsx

See Maxim 44.

Re: Macro variable in excel file name using ods

Tom — Thu, 16 Jul 2020 16:07:55 GMT

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.

Re: Macro variable in excel file name using ods

sasecn — Thu, 16 Jul 2020 16:10:27 GMT

Thanks all for the reply!

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

Thanks again!