I am receiving an error similar to this previous post:
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.
I have seen something similar before , and the cause was special characters in the data - in my case   .
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)
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.
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')
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.