topic Re: What is causing the error in ODS EXCEL? in SAS Programming

What is causing the error in ODS EXCEL?

PaigeMiller — Mon, 28 Oct 2019 17:21:04 GMT

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.

Re: What is causing the error in ODS EXCEL?

DWilson — Mon, 28 Oct 2019 17:44:33 GMT

@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

Re: What is causing the error in ODS EXCEL?

PaigeMiller — Mon, 28 Oct 2019 17:59:58 GMT

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

Re: What is causing the error in ODS EXCEL?

DWilson — Mon, 28 Oct 2019 18:09:41 GMT

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.

Re: What is causing the error in ODS EXCEL?

Krueger — Mon, 28 Oct 2019 18:10:47 GMT

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.

Re: What is causing the error in ODS EXCEL?

PaigeMiller — Mon, 28 Oct 2019 18:29:06 GMT

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

Re: What is causing the error in ODS EXCEL?

SASKiwi — Mon, 28 Oct 2019 19:25:07 GMT

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

Re: What is causing the error in ODS EXCEL?

PaigeMiller — Mon, 28 Oct 2019 19:29:28 GMT

SAS 9.4 TS1M5

Re: What is causing the error in ODS EXCEL?

SASKiwi — Mon, 28 Oct 2019 20:07:33 GMT

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.

Re: What is causing the error in ODS EXCEL?

PaigeMiller — Mon, 28 Oct 2019 20:10:31 GMT

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

Re: What is causing the error in ODS EXCEL?

DWilson — Mon, 28 Oct 2019 20:13:10 GMT

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');

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

Re: What is causing the error in ODS EXCEL?

Tom — Mon, 28 Oct 2019 21:12:47 GMT

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?

Re: What is causing the error in ODS EXCEL?

Ksharp — Tue, 29 Oct 2019 11:51:17 GMT

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 .

Re: What is causing the error in ODS EXCEL?

PaigeMiller — Tue, 29 Oct 2019 12:21:23 GMT

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.

Re: What is causing the error in ODS EXCEL?

Ksharp — Tue, 29 Oct 2019 12:29:07 GMT

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 ?

Re: What is causing the error in ODS EXCEL?

DWilson — Tue, 29 Oct 2019 12:31:16 GMT

@PaigeMiller wrote:
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.

That's all true but if you can get it to work by changing the temp folder or outputting to a different folder then that may help SAS Tech Support figure out what's going on.

Re: What is causing the error in ODS EXCEL?

PaigeMiller — Tue, 29 Oct 2019 12:49:31 GMT

@DWilson wrote:

@PaigeMiller wrote:

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.

That's all true but if you can get it to work by changing the temp folder or outputting to a different folder then that may help SAS Tech Support figure out what's going on.

Yes, and I have already stated that I have tried all reasonable debugging methods that I can think of, like different folder on a different drive, removing spaces from the folder name, etc. (none of which should be necessary, and ODS HTML is not showing these problems on the exact same code/data)

Other than the first reply, no one has addressed why the LOG shows ODS EXCEL is trying to write to the wrong folder where the folder name has pre-pended "C:\Users..."

Re: What is causing the error in ODS EXCEL?

DWilson — Tue, 29 Oct 2019 13:01:28 GMT

@PaigeMiller wrote:
@DWilson wrote:
@PaigeMiller wrote:
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.
That's all true but if you can get it to work by changing the temp folder or outputting to a different folder then that may help SAS Tech Support figure out what's going on.
Yes, and I have already stated that I have tried all reasonable debugging methods that I can think of, like different folder on a different drive, removing spaces from the folder name, etc. (none of which should be necessary, and ODS HTML is not showing these problems on the exact same code/data)

Other than the first reply, no one has addressed why the LOG shows ODS EXCEL is trying to write to the wrong folder where the folder name has pre-pended "C:\Users..."

It sounds like a bug. Like the one mentioned in the SAS notes I listed.

Is the pre-pended "C:\Users.." folder your SAS temp folder?

I may have missed it, but did you try specifying WORK= in your ODS EXCEL statement to tell SAS to use a different temp directory? Did you try specifying only a filename with file= and specifying the work directory as the directory where you want the file to be saved? If so, my apologies.

Btw, I'm not suggesting these things as a panacea. I'm suggesting these things to see if you can get SAS to produce the file you want where you want. If you can, then it may help SAS tech support narrow down the issue. Of course, they may already know about this issue and not need any help but, if they don't, I'm sure they'll have lots of the same questions you've seen here from other posters.

Re: What is causing the error in ODS EXCEL?

Tom — Tue, 29 Oct 2019 13:41:34 GMT

@PaigeMiller wrote:

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.

Please update the thread with any answers you get.

Most of the suggestions for basic debugging tips you are getting is because you did not present the issue in a way that made it look like you had done anything to test. For example I would normally expect to see a minimal set of code that reproduces the problem. But your code includes both macro code and complex proc report code. So is the issue with the macro code? If you had tested and eliminated the macro code why did you include it in the test? Is the issue with PROC REPORT? If you had tested and eliminated PROC REPORT as the issue then why did you include it in the code?

I just ran this little example program on SAS PC version 9.4 (TS1M5) (using single byte character encoding):

62    ods excel file="c:\downloads\ods.xlsx" options(sheet_name='Data');
63    proc print data=sashelp.class; run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.09 seconds
      cpu time            0.06 seconds


64    ods excel close;
NOTE: Writing EXCEL file: c:\downloads\ods.xlsx

Without any issues.

The one thing I did notice is that on PC SAS when the XLSX files was closed SAS automatically tried to open the file using Excel on my PC. That does not sound like the error you are receiving is caused by that process. Unless you had previously been trying to direct ODS EXCEL to a different file and running the new ODS EXCEL statement with a new output file caused it to close the old file and try to open it. If so the error is not with this ODS EXCEL statement but the one before it.

Re: What is causing the error in ODS EXCEL?

PaigeMiller — Tue, 29 Oct 2019 13:55:34 GMT

I may have found the problem by accident.

Although the code originally presented did not have a STYLE= option in the PROC REPORT statement, that seems to be the problem. Even if I remove the STYLE= from the PROC REPORT statement later in the same SAS session, somehow things are still screwed up and I get the error. And so I showed in my first post PROC REPORT with no STYLE= option.

So, restart SAS, never issue the STYLE= in the PROC REPORT statement and everything works fine.

Add STYLE= into the PROC REPORT statement, nothing works thereafter in that SAS session.

All very repeatable now. I have tested this a few times. Currently, I have restarted SAS and performed the ODS EXCEL/PROC REPORT part of the code without error, probably 20 times in the latest SAS session, and it works as long as I don't use STYLE=.

I'm not sure what is wrong with the STYLE= option that I was using, I think it was developed for ODS HTML and does not work in ODS EXCEL, but that's speculation.