topic Re: ODS EXCEL Title (using ODS text in the next Excel sheet) in ODS and Base Reporting

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

rush_milo — Tue, 30 May 2017 01:44:45 GMT

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.

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

Reeza — Tue, 30 May 2017 04:33:05 GMT

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

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

Atennissa — Wed, 11 Oct 2017 18:03:49 GMT

Hi,

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

Thanks,

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

Mahult — Thu, 22 Mar 2018 13:47:01 GMT

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;

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

AimeeL — Wed, 28 Mar 2018 21:13:31 GMT

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?

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

Mahult — Tue, 03 Apr 2018 19:24:43 GMT

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;