BookmarkSubscribeRSS Feed
rush_milo
Obsidian | Level 7

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.

 

 

5 REPLIES 5
Reeza
Super User

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

Atennissa
Calcite | Level 5

Hi,

 

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

 

Thanks,

Mahult
SAS Employee

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
Calcite | Level 5

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
SAS Employee

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4930 views
  • 1 like
  • 5 in conversation