Changing from ODS excelXP to ODS EXCEL

[LuAnnS]

I'm updating my proc report/ODS ExcelXP code to proc report/ODS excel so I can gain the benefit of having an .xlsx output instead of .xlm.   I have multiple sheets and have converted everything perfectly, but I'm having trouble with my notes 'tab'.  I'm using proc report and titles1-titles8 to get a sort of reader's notes tab in excel.  it worked fine with excelXP but now my notes are coming out in excel wrapped, in one cell, with a row height of 450, instead of spreading it across the page.  Here is my code:

%macro SSE_Notes;

%if &SSE_NOTE=y %then
%do;

data want;
format a b $90.;
do _n_=1 to 2;
output;
end;
run;

OPTIONS MISSING=' ' nobyline topmargin=.1IN bottommargin=.1IN leftmargin=.1IN rightmargin=.1IN;
Ods excel options (sheet_name='SSE Notes' ORIENTATION="LANDSCAPE" Center_Horizontal= 'yes'
embedded_titles = "yes" fittopage="ON");

proc report data=want nowd split="*"
STYLE(HEADER)={FOREGROUND=White BACKGROUND=White}
STYLE(COLUMN)={FOREGROUND=White BACKGROUND=White}
STYLE(REPORT)={FOREGROUND=White BORDERWIDTH=1 BORDERCOLOR=White BACKGROUND=White};

title1 bcolor=white j=left font='Arial Bold' height=14pt "blah blah";
title2 bcolor=white j=left font='Arial' height=10pt "1.blah blah";
title3 bcolor=white j=left font='Arial' height=10pt "blah blah.";
title4 bcolor=white j=left font='Arial' height=10pt "2.blah blah";

%if &PLAT_STAT = y %then
%do;
title5 bcolor=white j=left font='Arial' height=10pt "3. blah blah.";
title6 bcolor=white j=left font='Arial' height=10pt "4. blah blah";
%end;

%if &STND_STAT = y %then
%do;
title5 bcolor=white j=left font='Arial' height=10pt "3. blah blah.";
title6 bcolor=white j=left font='Arial' height=10pt "4. blah blah";
title7 bcolor=white j=left font='Arial' height=10pt "5. blah blah ";
title8 bcolor=white j=left font='Arial' height=10pt "blah blah";
%end;
column (a);
quit;

%end;

%mend SSE_Notes;

[Cynthia_sas · Accepted solution]

Hi:

  I simplified your code and made slightly different fake data to make sure that every cell was visible in the output. I also changed the font to Arial Unicode MS because if I think I may share code between HTML, RTF, PDF and Excel, I find that using Arial Unicode MS as the font name works best in all of those destinations instead of just Arial. I also turned the header back on for column A and gave it some text so that the data cells would be visible. I also "un-macro'd" the code because the code you posted did not have any ODS close statements. Here's the changed/simplified code:

And, here's the output in a side-by-side comparison:

  And, except for the fact that ODS EXCEL seems to use more white space in the cells (or cell padding), the output looks the same to me.

  My rule of thumb is to get the basic look and feel of the program working outside of a macro program and then to put that working code inside a macro program. It looked from the way your macro program was written that you might be invoking this proc report multiple times? I notice your ODS EXCEL statement did not have a FILE= option and that you did not have an ODS EXCEL CLOSE either -- so it was hard to understand the full context of what you were trying to do or to understand what wrapping you're observing. You said you were trying to use the TITLE statements for reader's notes. My tendency would be to take another approach to reader's notes as you describe. My tendency is to make them a SAS dataset and then to use PROC REPORT to display the notes because I have more control in PROC REPORT over the style characteristics than I do in the TITLE statement:

  If you have ongoing issues with your current approach then your best resource would be to open a track with Tech Support and send them ALL of your code and some sample data so they can see how you are creating the files and they can compare the EXCELXP and ODS EXCEL output using the SAME data and equivalent options and suboptions.

Cynthia

[LuAnnS]

thank you so much!!!!!!!!!!!