What is causing the error in ODS EXCEL?

[DWilson]

---

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

[PaigeMiller]

---

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

[DWilson]

---

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

[Tom]

---

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

[PaigeMiller]

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.

[Krueger]

This goes over the different styles for SAS:

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3775-2019.pdf

Although it does sound like a bug and would be best left to SAS Tech it could have something to do with the version of Office you are running. You stated it worked in the past, did your Office version get updated recently? I worked technical support for 5 years for a Tax and Accounting Developer and we worked very closely with Microsoft. Literally every Month we were finding bugs in Office that were breaking Excel Worksheets (not our issue but our customers worked heavily in Office from WITHIN our program so it was perceived as our fault).

[Tom]

That is good news.

I would like to understand, if you can find out, two things.  First what is it about the style that is causing SAS to freak out? Second is there a way to clear the style that does not require restarting SAS?

[PaigeMiller]

When I get an answer, I will let you know. All I can tell you right now is that the one I used was a custom style created via PROC TEMPLATE, which worked fine with ODS HTML.