topic Re: ODS Excel output has errors in file in SAS Programming

ODS Excel output has errors in file

RyanJB — Fri, 02 Oct 2020 17:30:27 GMT

I'm following a (relatively old) SAS Institute paper for outputting fancy formatted tables to Excel sheets, specifically Example 3 (page 5), just the first sheet.

ods escapechar = "^";
ods excel file = "C:\sas_test1.xlsx" options(sheet_name = "My Fancy Sheet");
ods text = "New Report1";
proc report data = test1;
	column var1 var2 var3 var4 var5 var6;
run;
ods excel close;

SAS threw an error when I inserted the style block from the tutorial, so I dropped that for the time being.

Once I go to open the file, though, I get an error from Excel, which then generates an XML log detailing the issue:

<summary>Errors were detected in file 'C:\sas_test1.xlsx'</summary>
<removedRecords><removedRecord>Removed Records: Worksheet properties from /xl/worksheets/sheet1.xml part</removedRecord></removedRecords>
</recoveryLog>

Has anyone else encountered this or would be able to help me understand what I'm doing incorrectly?

Thanks!

Re: ODS Excel output has errors in file

Reeza — Fri, 02 Oct 2020 17:44:52 GMT

What does the log show?

Re: ODS Excel output has errors in file

jimbarbour — Fri, 02 Oct 2020 17:48:03 GMT

ODS is a wonderful tool, but sometimes it can drive you crazy. The older ExcelXP destination put out XML that you could (and sometimes had to) manually read and correct the problem. It's a little more reliable with the newer Excel destination, but when you do have a problem, it's tricky because you can't typically just open it in Notpad.exe and see what's wrong.

A shot in the dark: Take the spaces out of your sheet name. Replace them with underscores. If that doesn't work then maybe @Cynthia_sas or @Vince_SAS might be of more assistance.

Jim

Re: ODS Excel output has errors in file

RyanJB — Fri, 02 Oct 2020 17:50:29 GMT

Unfortunately, removing the space in the sheet name did not fix the error. Thanks for your response, though!

Re: ODS Excel output has errors in file

jimbarbour — Fri, 02 Oct 2020 17:54:29 GMT

@Reeza,

If what @RyanJB is experiencing is like what I have experienced, then the SAS log will show nothing. From SAS' perspective, everything went well. It's just that when you try to open the spreadsheet in Excel, it won't open. At least that's what I've experienced on numerous occasions. I've had to do a bit of SAS template coding to make things work. With ExcelXP, the infamous one was character strings containing all numeric digits that were longer than 32 in length. SAS, even if they had a $ definition, would try to render them as numbers, and Excel would balk. I had to edit the template to say if it's longer than 32 then handle it as character, period. It worked fine after I modded the template. Template code is funky. It's basically another language. It's SAS like, but it's not SAS.

Jim

Re: ODS Excel output has errors in file

jimbarbour — Fri, 02 Oct 2020 17:55:53 GMT

Told ya it was a long shot.

Hmm. Well, let me think about it. These things are at least in my experience difficult to debug.

Jim

Re: ODS Excel output has errors in file

RyanJB — Fri, 02 Oct 2020 18:07:21 GMT

Sorry, my mind was focused on the Excel problem rather than the SAS problem. That said, the log I initially posted (edited below) does show a warning.

Beyond that, what @jimbarbour said is true - just standard end-of-log output. Pasted below:

NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27 
28 ods excel file = "C:\sas_test1.xlsx";
WARNING: Unsupported device 'SVG' for EXCEL destination. Using default device 'PNG'.
29 *ods text = "New Report1";
30 proc report data = test1;
31 column var1 var2 var3 var4 var5 var6;
32 run;

NOTE: There were 192 observations read from the data set WORK.TEST1.
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.51 seconds
cpu time 0.45 seconds


33 ods excel close;
NOTE: Writing EXCEL file: C:\sas_test1.xlsx
34
35 %LET _CLIENTTASKLABEL=;
36 %LET _CLIENTPROCESSFLOWNAME=;
37 %LET _CLIENTPROJECTPATH=;
38 %LET _CLIENTPROJECTPATHHOST=;
39 %LET _CLIENTPROJECTNAME=;
40 %LET _SASPROGRAMFILE=;
41 %LET _SASPROGRAMFILEHOST=;
42
43 ;*';*";*/;quit;run;
44 ODS _ALL_ CLOSE;
45
46
47 QUIT; RUN;

Re: ODS Excel output has errors in file

jimbarbour — Fri, 02 Oct 2020 18:02:13 GMT

Just as I suspected, SAS thinks everything is fine.

By the way, @RyanJB, it makes the log a lot easier to read if you post it via the "Insert Code" feature here on the forum.

Let me think about this some more...

Jim

Re: ODS Excel output has errors in file

Vince_SAS — Fri, 02 Oct 2020 18:03:35 GMT

The file location displayed in the SAS log does not match the code that you initially posted. This isn't really a problem, unless the code that generated the log is different from the code that you initially posted.

Are you using data from SASHELP.PRDSALE as described in the paper, or your own data. If your own data, then there could be a character that is causing the error, especially if the value specified in the ESCAPECHAR option is in the data.

Vince DelGobbo

SAS R&D

Re: ODS Excel output has errors in file

RyanJB — Fri, 02 Oct 2020 18:04:06 GMT

I've updated that reply - I missed a warning (no idea how - I hate seeing them pop up and do everything I can to resolve them). It's the default device warning (WARNING: Unsupported device 'SVG' for EXCEL destination. Using default device 'PNG'.)

Re: ODS Excel output has errors in file

Vince_SAS — Fri, 02 Oct 2020 18:06:53 GMT

What is causing an image to be generated? I don't see that in your code.

Vince DelGobbo
SAS R&D

Re: ODS Excel output has errors in file

jimbarbour — Fri, 02 Oct 2020 18:07:17 GMT

@RyanJB,

You may already know this, but Excel files are really just zipped XML. Can you do the following:

Rename your sas_test1.xlsx file to sas_test1.zip.
Unzip the file with WinZip (or whatever zip too you usually use).
Post the relevant section of the XML here (if it's not confidential data!).

I'm in particular interested in the section that Excel said needed to be removed.

Jim

Re: ODS Excel output has errors in file

RyanJB — Fri, 02 Oct 2020 18:13:54 GMT

Replying to both of your posts:
I was trying to anonymize what I posted and do it quickly, and one of those didn't work out so well! The escape character does not show up in my data, and there shouldn't be other special characters. I'm going to check out the XML as @jimbarbour suggested and try running it on a small subset of data I can easily vet. It is my own data, though.

As to why it's trying to generate an image, I'm not sure. I did create a pdf with ODS earlier (without issue), but I closed it as well.

Re: ODS Excel output has errors in file

jimbarbour — Fri, 02 Oct 2020 18:17:15 GMT

If you're using an interactive SAS session like Enterprise Guide, Display Manager ("PC SAS"), etc., sometimes it's best to close the session and re-open it. A close and re-open will clear hard to reset things, things which might generate that PNG message (even though you're not using any images).

Jim

Re: ODS Excel output has errors in file

RyanJB — Fri, 02 Oct 2020 18:29:09 GMT

I am using EG, and the hard close/open seems to have solved it. I'm going to dig through the XML a little more and can let you know what differences I find between the corrupted one and the uncorrupted one, but I suspect it's to do with headers and footers included on the pdfs I generate earlier in the code (which I mentioned in a reply to @Vince_SAS). There's an odd (literally!) note in the XML file:

  <headerFooter>
    <oddHeader>*** lots of text ***&#13;even more text&#13;*** </oddHeader>
    <oddFooter>bottom text&#13;filename.pdf</oddFooter>
  </headerFooter>

I suppose if headers/footers are generated as images, that could be what the issue is.

Thank you very much for your help!

Re: ODS Excel output has errors in file

jimbarbour — Fri, 02 Oct 2020 18:32:55 GMT

Good job, @RyanJB, 👍

Sometimes it's the simple things that get us working again, right? 🙂

Jim

Re: ODS Excel output has errors in file

RyanJB — Fri, 02 Oct 2020 18:50:12 GMT

Sure is! And I confirmed it does seem to have been the title and footnote statements; clearing them before running the ODS Excel removed the warning and generated a good Excel file.

Now to clean up all these unpacked XML files... (that title/footnote bit was the only difference between the 2)

Re: ODS Excel output has errors in file

Reeza — Fri, 02 Oct 2020 19:27:41 GMT

Sometimes it does, sometimes it doesn't but it should be the first step. Generally my debugging steps for ODS EXCEL/XML
1. Check log
2. Check code works from known examples - ie create smallest reproducible example (using fake data)
3. Start adding features back in to see which breaks things.
4. Apply to actual data to see if it works.