What is causing the error in ODS EXCEL?

[PaigeMiller]

I have written a macro, and it works fine until the ODS EXCEL command. The macro extracts some data from data bases, based upon macro arguments, does some calculations, and then tries to creates a table via ODS EXCEL. I have removed from this example the stuff that works properly, as there are no errors in the log.

%macro monitoring2(startdate1=,enddate1=,startdate2=,enddate2=,
    no_rates_lastXweeks=2,graphics_only=0,debug=0,outxlsx=,acaps=1);
	%put;
	%put &=outxlsx;
	%put;
	ods excel file="&outxlsx" options(sheet_name='Data');
	proc report data=_stats1a_;
		columns method week _freq_ booking_rate approval_rate conversion_rate exception_rate;
		define method/group 'Method' order=data;
		define week/group "Week" format=wff. order=internal;
		define booking_rate/display 'Booking Rate';
		define _freq_/display 'N Apps' format=comma10.0;
		define approval_rate/display 'Approval Rate';
		define conversion_rate/display 'Conversion Rate';
		define exception_rate/display 'Exception Rate';
		compute week;
		    if week=1e10 then call define(_row_,'style','style={color=verydarkblue fontweight=bold background=lightyellow}');
		endcompute;
	run;
	ods excel close;
%mend;

%monitoring2(startdate1=01JUL19,enddate1=31JUL19,
 startdate2=01AUG19,debug=1,graphics_only=1,
 outxlsx=&pers\ias2\testplots3.xlsx,acaps=0)

Now, here's the log. Note that the %PUT statement shows that the macro variable &OUTXLSX has the desired value, it hasn't changed (shown in red). The error occurs in the ODS EXCEL command, and not in the PROC REPORT. The file name, which is correct in &OUTXLSX, has C:\Users... etc. prepended to the value of &OUTXLSX, and of course, there is no such folder once the mysterious addition to &OUTXLSX happens. Why?

9503  %monitoring2(startdate1=01JUL19,enddate1=31JUL19,
startdate2=01AUG19,debug=1,graphics_only=1,
outxlsx=&pers\ias2\testplots3.xlsx)

OUTXLSX=G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx

MPRINT(MONITORING2):   ods excel file="G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx" options(sheet_name='Data');
NOTE: Writing EXCEL Body file: G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx
ERROR: A component of C:\Users\eccrpm0\AppData\Local\Temp\SAS Temporary Files\_TD16364_PCVPTWX6VP00119_\_T00000000069D5120\G:\Team12\Credit Risk
       Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx is not a directory.
ERROR: No body file. EXCEL output will not be created.

Just for completeness, the macro variable &pers in the call to the macro resolves as G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP, so &OUTXLSX does indeed have the desired folder location.

[DWilson]

---

@PaigeMiller wrote:

I have written a macro, and it works fine until the ODS EXCEL command. The macro extracts some data from data bases, based upon macro arguments, does some calculations, and then tries to creates a table via ODS EXCEL. I have removed from this example the stuff that works properly, as there are no errors in the log.

%macro monitoring2(startdate1=,enddate1=,startdate2=,enddate2=,
    no_rates_lastXweeks=2,graphics_only=0,debug=0,outxlsx=,acaps=1);
	%put;
	%put &=outxlsx;
	%put;
	ods excel file="&outxlsx" options(sheet_name='Data');
	proc report data=_stats1a_;
		columns method week _freq_ booking_rate approval_rate conversion_rate exception_rate;
		define method/group 'Method' order=data;
		define week/group "Week" format=wff. order=internal;
		define booking_rate/display 'Booking Rate';
		define _freq_/display 'N Apps' format=comma10.0;
		define approval_rate/display 'Approval Rate';
		define conversion_rate/display 'Conversion Rate';
		define exception_rate/display 'Exception Rate';
		compute week;
		    if week=1e10 then call define(_row_,'style','style={color=verydarkblue fontweight=bold background=lightyellow}');
		endcompute;
	run;
	ods excel close;
%mend;

%monitoring2(startdate1=01JUL19,enddate1=31JUL19,
 startdate2=01AUG19,debug=1,graphics_only=1,
 outxlsx=&pers\ias2\testplots3.xlsx,acaps=0)

Now, here's the log. Note that the %PUT statement shows that the macro variable &OUTXLSX has the desired value, it hasn't changed (shown in red). The error occurs in the ODS EXCEL command, and not in the PROC REPORT. The file name, which is correct in &OUTXLSX, has C:\Users... etc. prepended to the value of &OUTXLSX, and of course, there is no such folder once the mysterious addition to &OUTXLSX happens. Why?

9503  %monitoring2(startdate1=01JUL19,enddate1=31JUL19,
startdate2=01AUG19,debug=1,graphics_only=1,
outxlsx=&pers\ias2\testplots3.xlsx)

OUTXLSX=G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx

MPRINT(MONITORING2):   ods excel file="G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx" options(sheet_name='Data');
NOTE: Writing EXCEL Body file: G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx
ERROR: A component of C:\Users\eccrpm0\AppData\Local\Temp\SAS Temporary Files\_TD16364_PCVPTWX6VP00119_\_T00000000069D5120\G:\Team12\Credit Risk
       Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx is not a directory.
ERROR: No body file. EXCEL output will not be created.

Just for completeness, the macro variable &pers in the call to the macro resolves as G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP, so &OUTXLSX does indeed have the desired folder location.

---

Maybe this note is applicable:

http://support.sas.com/kb/15/046.html

and this (they seem related):

http://support.sas.com/kb/61/280.html

If you are using z/OS you might be out of luck:

http://support.sas.com/kb/62/838.html

[PaigeMiller]

Yes those are the same errors, except for HTML output. I don't think the solutions given apply to EXCEL output (on Windows 10).

[DWilson]

Maybe play around with the WORK= options with the ODS EXCEL statement? For example, only specify a filename with  FILE= and specify your output directory in the WORK= statement? Trying to trick SAS here.

[Krueger]

Have you tried changing your output directory to somewhere more local?

"Sometimes" Windows can mess with other programs, whether it is through folder permissions, special characters or simply invalid path names (sometimes we make typos). Given that's where the error seems to be complaining about that's at least where I would start. If you can output it directly to the C:\ (bad practice maybe take it to C:\Users\Folder but still a valid test) then that at least gets you a little closer to narrowing down the root of the issue.

[PaigeMiller]

The problem exists when I attempt to write the file locally, and when I attempt to write the file to a server.

[SASKiwi]

What maintenance release of SAS 9.4 are you using? The EXCEL ODS option was a bit buggy in earlier releases. 

[PaigeMiller]

SAS 9.4 TS1M5

[SASKiwi]

M5 is pretty recent so should be fine. I suggest you try moving your code outside of the macro and hard-coding all parameters to exclude that as the reason for your error. If the error still occurs, try simplifying your report until the error disappears, to isolate where it is happening.

[PaigeMiller]

---

@SASKiwi wrote:

M5 is pretty recent so should be fine. I suggest you try moving your code outside of the macro and hard-coding all parameters to exclude that as the reason for your error. If the error still occurs, try simplifying your report until the error disappears, to isolate where it is happening.

---

Did that. Same problem, so it's not the macro. 

It's not the report either, the reporting code works fine in ODS HTML and ODS RTF.

[DWilson]

Try specifying the work directory as something other than default:

ods excel file="&outxlsx" work="G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2" options(sheet_name='Data');

or

ods excel file="testplots3.xlsx" work="G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2" options(sheet_name='Data');

[Tom]

I am a little confused about how specific is the error.  From some of your answers it seems that just using the ODS EXCEL statement is generating the error.  That is doesn't matter what report you are writing.  Is that true? Do you get the error just trying to write a copy of SASHELP.CLASS using PROC PRINT?

If so then does it matter if you try to write to a different directory on the G drive?  A different drive letter?

What if you include quotes to protect the spaces in the folder name from Windows/DOS? 

ods excel file="""G:\Team12\Credit Risk Oversight\Retail Risk\Users\MillerP\ias2\testplots3.xlsx"""  ...

What if you write to a directory that doesn't include spaces in its name?

[Ksharp]

Two options:

1) try proc print or proc tabulate instead of proc report .

2)remove   file="&outxlsx"   from ods excel , let sas create an temp file under work library .

[PaigeMiller]

The problem with almost all of these suggestions is that I shouldn't have to use PRINT or TABULATE; I shouldn't have to try different folders or folders with no spaces in the folder name. It should (and has in the past) worked properly as I have coded it. 

I am going to see what SAS Tech Support has to say.

[Ksharp]

Paige,

Is your SAS at server side or your local PC ? 

From the LOG ,it imply you are using SAS Server , Are you using SAS/EG or SAS Studio ?