ODS excel mixes up titles and tabs

[lindi_lindi]

Hello,

Here's an example of a problem I'm trying to solve:

%let path=path_here\;
ods noproctitle;
ods Excel file="&path.sashelp.xlsx" style=analysis options (sheet_interval="none" embedded_titles="yes");
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;;
ods excel options(sheet_name="birthweight");
title "race";
proc freq data=SASHELP.BIRTHWGT;
  tables race/missing nocol nocum norow nopercent;
run;
title "married";
proc freq data=SASHELP.BIRTHWGT;
  tables married/missing nocol nocum norow nofreq;
run;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;;
ods excel options(sheet_name="citiyr,cntainer" sheet_interval="now");
title "CITIYR";
proc print data=SASHELP.CITIYR noobs;
title "CNTAINER";
proc print data=SASHELP.CNTAINER noobs;

ods excel options(sheet_name="birthweight2" sheet_interval="now");
title "race";
proc freq data=SASHELP.BIRTHWGT;
  tables race/missing nocol nocum norow nopercent;
run;
ods excel close;

As you can see on the last tab, one of the tables gets pushed to the wrong tab, and gets the wrong title. I've tried so many things, especially with the sheet_interval, but I'm always getting mixed-up tables.

[ballardw]

I think that you may misunderstanding how Sheet_interval='Now' works.

From the documentation at https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm  (emphasis in Red added by me)

NOW

creates a new worksheet. When used, the next output object starts on a new sheet. After SHEET_INTERVAL='NOW' is executed, the SHEET_INTERVAL option reverts to the previous setting.

Which means that after the single table is output from the Now the table output uses the previous setting, which is almost certainly 'Table', meaning that the Cntainer set is on the next page as that is what the default would do. If you want Cntainer on the same page as Citiyr then you need to insert a Sheet_interval='None' before Cntainer though I would likely place it after the Now.

Then the Title probably got "confused" as the previous title had no place to go on the previous sheet and the "Now" before the Birthweight.

I don't spend much time trying to force stuff in Excel but I have found that the page controls for RTF and sheet controls for Excel sometimes require using two ODS statements, one to stop a previous behavior and a second to start the new. I do see that you used Sheet_interval='NONE' for the first two tables

[lindi_lindi]

Thanks, that makes sense. However when I try the double statement, either before or after, it's still generating non-desirable results:

ods noproctitle;
ods Excel file="&path.sashelp.xlsx" style=analysis options (sheet_interval="none" embedded_titles="yes");
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;;
ods excel options(sheet_name="birthweight");

title "race";
proc freq data=SASHELP.BIRTHWGT;
  tables race/missing nocol nocum norow nopercent;
run;
title "married";
proc freq data=SASHELP.BIRTHWGT;
  tables married/missing nocol nocum norow nofreq;
run;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;;
ods excel options(sheet_interval="none");
ods excel options(sheet_name="citiyr and cntainer" sheet_interval="now");
ods excel options(sheet_interval="none");

title "CITIYR";
proc print data=SASHELP.CITIYR noobs;

title "CNTAINER";
proc print data=SASHELP.CNTAINER noobs;

ods excel options(sheet_interval="none");
ods excel options(sheet_name="birthweight2" sheet_interval="now");
ods excel options(sheet_interval="none");

title "race";
proc freq data=SASHELP.BIRTHWGT;
  tables race/missing nocol nocum norow nopercent;
run;
ods excel close;

[lindi_lindi · Accepted solution]

Apparently the problem was missing run statements at the end of each proc print. It will display fine inside SAS, but the run is needed for it to correctly go into excel.