topic Problem with content in Excel using ODS EXCEL and PROC REPORT in ODS and Base Reporting

Problem with content in Excel using ODS EXCEL and PROC REPORT

TaniaRTP — Wed, 14 Nov 2018 22:36:59 GMT

I am receiving an error similar to this previous post:

https://communities.sas.com/t5/SAS-Analytics-U/Proc-print-to-an-Excel-file-produces-corrupt-file/td-p/330048

I am using PROC REPORT and ODS EXCEL to create a .xlsx file but when I go to open it, I receive this message.

If I click YES, and open, then I get output which contains only the fields which are numeric.

My results window within SAS Studio do show that the characters printed.

I don't think there's an issue with the data (but I could be wrong), because I can use PROC EXPORT and all character variables are present & populated. I just can't use EXPORT because I need highlighting & formatting.

Here is the code:

ods excel file="&outfile" options(sheet_name="x") ;

proc report data=annot nowd headline headskip split='*' missing spacing=1
style(header)=[font=('Arial',8pt)]
style(column)=[font=('Arial',8pt)];
define color / display noprint;
compute color;
if color='Green' then call define(_row_, "style", "style=[background=lightGreen]");
else if color='Red' then call define(_row_, "style", "style=[background=lightRed]");
else if color='Yellow' then call define(_row_, "style", "style=[background=Yellow]");
else if color='White' then call define(_row_, "style", "style=[background=White]");
endcomp;
run;
ods excel close ;

Does anybody have suggestions on how I should proceed or what is causing my error?

Many thanks in advance.

Re: Problem with content in Excel using ODS EXCEL and PROC REPORT

34reqrwe — Wed, 14 Nov 2018 23:21:20 GMT

I have seen something similar before , and the cause was special characters in the data - in my case &nbsp .

If you want to test this possibility -

try only writing 1 observation and see if the issue still occurs.

proc report data=annot (obs=1) ...

If the issue still occurs , try adding columns one at a time and see if error still occurs

e.g. proc report data=annot(obs=1 keep=SiteNumber)

Re: Problem with content in Excel using ODS EXCEL and PROC REPORT

TaniaRTP — Thu, 15 Nov 2018 16:41:15 GMT

I was able to pinpoint it as observation #405 out of 473 by restricting observations. Because all of my datasets were doing similar, I tried using another dataset to determine if was the same subject or site causing the issue. There doesn't appear to be a clear culprit here.

Re: Problem with content in Excel using ODS EXCEL and PROC REPORT

34reqrwe — Thu, 15 Nov 2018 17:42:57 GMT

so you can now try restricting columns . you should be able to identify which column/columns are causing the problem .

If you find that it is a character column , try removing non printable characters and see if that works

i.e

new_col = compress( old_col, , 'wk')