ODS EXCEL Title (using ODS text in the next Excel sheet)

[rush_milo]

Hi there,

I have a problem where the title using ODS text is not appearing in the second worksheet but instead appearing at the bottom of Sheet 1.

My code is as follows:

**Start of code;

ods Excel file ="&PATH2./Output/OptoutReport.xlsx" style=HTMLblue options(sheet_interval="none" sheet_name="Sheet1");

ods escapechar='~';

ods text="~S={font_size=14pt font_weight=bold}~Sheet 1";

proc tabulate data=pct;

.

.

.

run;

**Dummy table;

***********;

ods excel options(sheet_interval="table");

ods exclude all;

data _null_;

file print;

put _all_;

run;

ods select all;

***********;

　

ods Excel options(sheet_interval="none" sheet_name="Sheet2");

ods escapechar='~';

ods text="~S={font_size=14pt font_weight=bold}~Sheet 2";

proc tabulate data=pct;

.

.

.

run;

ods Excel close;

**end of code;

Oddly enough, when I placed this code --> ods text="~S={font_size=14pt font_weight=bold}~Sheet 2"; just before this line of code--> ods Excel close; the text would then appear after the table generated using proc tabulate data.

Appreciate if anyone can give a pointer to solve this issue.

Thank you.

[Reeza]

You should contact tech support. But that also assumes you're using SAS 9.4 M3+. Anything before that was pre-production. 

[Atennissa]

Hi,

I have the same problem. Does the tech support give you a solution ?

Thanks,

[Mahult]

I think PROC ODSTEXT might work for you:

ods Excel file ="c:\temp\Thursday.xlsx" style=HTMLblue options(sheet_interval="none" sheet_name="Sheet1");

ods escapechar='~';

ods text="~S={font_size=14pt font_weight=bold}~Sheet 1";

proc print data=sashelp.class;run;

**Dummy table;

***********;

ods excel options(sheet_name="sheet2" sheet_interval="proc");

***********;

proc odstext;

p "~S={font_size=14pt font_weight=bold}~Sheet 2";

run;

ods excel options(sheet_name="sheet2" sheet_interval="none");

proc print data=sashelp.class;run;

ods excel close;

[AimeeL]

When I try this method, I get three total spreadsheets in my excel document:

1 - Sheet 1,  with 'Sheet 1' in the first row followed by the output from the proc print

2 - sheet 2,  with 'Sheet 2' in the first row only

3 - sheet2 2, with the output from proc print

Is there any way to get the output from proc odstext and the proc print into just one spreadsheet?

[Mahult]

You should be able to get what you want by removing this statement:

ods excel options(sheet_name="sheet2" sheet_interval="proc");

In fact, you can reduce it a bit more. When I run the following, I get one sheet, with output from both procs:　

ods excel file ="c:\temp\Tuesday.xlsx" style=HTMLblue options(sheet_interval="none" sheet_name="Sheet1");

ods escapechar='~';

ods text="~S={font_size=14pt font_weight=bold}~This is the first bit of output";

proc print data=sashelp.cars(obs=10);

run;

ods text="~S={font_size=14pt font_weight=bold}~This is the second bit of output";

ods excel options(sheet_name="sheet2" sheet_interval="none");

proc print data=sashelp.class;

run;

ods excel close;