The SAS Output Delivery System and reporting techniques

ODS EXCEL Title

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

ODS EXCEL Title

I'm trying to place a message above the table.  Is there a way to make the first sentence bold, but the following sentences not bold.

 

I have...

 

title1 j=left lspace=0 height=10pt font="&font." color=red BOLD "WARNING THIS IS EXPLORATORY" NOTBOLD? "REST OF THE PARAGRAPH";

 

Also, I see that when the title is really long, about a typical paragraph, there is a decent white gap between the top of the speadsheet and where the message starts.  Any ideas on thi as well?

 

Thank you!


Accepted Solutions
Solution
‎05-28-2017 08:36 PM
Frequent Contributor
Posts: 89

Re: ODS EXCEL Title

You could use PROC ODSTEXT.

 

ODS EXCEL FILE="/folders/myfolders/SAS Communities/add_titles_to_Excel.xlsx" 
  options(sheet_name="Use ODSTEXT"
          sheet_interval='none'
          embedded_titles='yes');

* h statement is new for PROC ODSTEXT in SAS 9.4M4. 
* but it does NOT show up in ODS EXCEL...;

proc odstext;
  *h "WARNING: THIS IS EXPLORATORY" / style=[color=red];
  p "WARNING: THIS IS EXPLORATORY." / style=[color=red font_weight=bold];
  p "Once the spreadsheet has passed the internal review procedures, it will be posted to the company internet site.";
  p "Please contact Jane Moneypenny for more details.  Thank you and have a nice day!";
run;

proc print data=sashelp.class;
run;

ODS EXCEL CLOSE;

PROC ODSTEXT can add text to a spreadsheet.jpg

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: ODS EXCEL Title

Well, for your first question:

title1 j=left lspace=0 height=10pt font="&font." color=red bold "WARNING THIS IS EXPLORATORY";
title2 ... "REST OF THE PARAGRAPH";

You can have up to 10 titles.  If it was RTF you were outputting to then you could use inline tags to aid, but that doesn't work in tagsets.excelxp output.

 

For your second point, you seem to mistake what a title is.  A title is a shorter text to give some overview to a report.  If you plan to put out paragraphs of text then put that in a dataset and proc report it out with notab split, i.e. so all the text appears in one printout then your other output, but all as one sheet.  In fact, if your dealing with spreadsheets in general titles are a bit pointless - spreadsheets are not word procecessors.

Contributor
Posts: 29

Re: ODS EXCEL Title

I apologize, but could you provide reference or an example on the proc report notab split?

Super User
Super User
Posts: 7,997

Re: ODS EXCEL Title

Its not the proc report but the ods options:

You might even be able to do:

ods tagsets.excelxp options(sheet_name="Sheet1");

proc report data=text...;

proc report data=your_data...;

ods tagsets.excelxp options(sheet_name="Sheet2");

...

 

Reference on options here:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

Super User
Posts: 11,343

Re: ODS EXCEL Title

You may want to consider that the Style used for your ODS output sets defaults for such things as title and footnote text appearance. If you use the same options repeatedly you may want to make a custom version of the style you use to set such things.

 

You may also want to use ODS Text instead of Title statements.

Solution
‎05-28-2017 08:36 PM
Frequent Contributor
Posts: 89

Re: ODS EXCEL Title

You could use PROC ODSTEXT.

 

ODS EXCEL FILE="/folders/myfolders/SAS Communities/add_titles_to_Excel.xlsx" 
  options(sheet_name="Use ODSTEXT"
          sheet_interval='none'
          embedded_titles='yes');

* h statement is new for PROC ODSTEXT in SAS 9.4M4. 
* but it does NOT show up in ODS EXCEL...;

proc odstext;
  *h "WARNING: THIS IS EXPLORATORY" / style=[color=red];
  p "WARNING: THIS IS EXPLORATORY." / style=[color=red font_weight=bold];
  p "Once the spreadsheet has passed the internal review procedures, it will be posted to the company internet site.";
  p "Please contact Jane Moneypenny for more details.  Thank you and have a nice day!";
run;

proc print data=sashelp.class;
run;

ODS EXCEL CLOSE;

PROC ODSTEXT can add text to a spreadsheet.jpg

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1108 views
  • 0 likes
  • 4 in conversation