BookmarkSubscribeRSS Feed
deleted_user
Not applicable
[pre]

ods tagsets.ExcelXP file='j:\hl7\chemistry\tabulate_output.xml' options(Orientation='Landscape');
proc tabulate data = HL7.chlamydia_nodupes;
class Service_Category Sex_Category Race_Ethnicity Age_Category Combined_Result
/style = [background=white color=black fontweight=medium width=150 height=25];
classlev Service_Category Sex_Category Race_Ethnicity Age_Category
/ Style = [Width=400 background=white color=black fontweight=medium height=25];
classlev Combined_Result / style = [Width=150 background=white color=black fontweight=medium];
keyword all / style = [Width=150 background=white color=black fontweight=medium];
table All="Total"
Service_Category="Service" Sex_Category="Sex"
Race_Ethnicity="Race/Ethnicity" Age_Category="Age",
(Combined_Result="Test Results" All="Total" )
* N="" * [style=[background=white color=black tagattr='format:##,##0' height=25]]
/
Box = [label="Number of Tests" style=[background=white color=black fontweight=medium height=25 width=250]]
misstext = "0" ;
format service_category service. Sex_Category Sex. Race_Ethnicity $Race_Ethnicity. Age_Category $Age_Category.
Combined_Result $Combined_Result.;
title "Number and Results of Tests";
run;
ods tagsets.ExcelXP close;
[/pre]

I have a title specified in the proc, I'd also like to be able to add footers. But the title doesn't show up when I open up the file in Excel.

The title is in the XML file:



Can anyone tell me why I can't see the title? Thanks!

Jeff
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
Hi:
Have you looked in PRINT PREVIEW mode??? Without any other instructions from you, the SAS title goes into the Header area of the worksheet.

There is an explicit sub-option...embedded_titles='yes' ... which will put the titles into the worksheet, but, by default, TAGSETS.EXCELXP works the same way that ODS RTF works - -titles go into the Header area.

To find out about the suboptions, use this syntax:
[pre]
ods tagsets.ExcelXP file='j:\hl7\chemistry\tabulate_output.xml'
options(doc='Help' Orientation='Landscape');
[/pre]

and the list of sub-options will be in the SAS log.

cynthia
deleted_user
Not applicable
Thanks for your very quick help!

Now I have to format that title to match the rest of the table. Using color and bcolor in the title statement:

title "Number and Results of Tests" bcolor= white color = black;

This works in the output window but not in the spreadsheet.

Jeff Message was edited by: JCS
deleted_user
Not applicable
I've tried a couple of different options but still can't control the color and background of the title as it shows up in the Excel file. Do I need to figure out a TAGATTR for this? Thanks!

Jeff
Cynthia_sas
SAS Super FREQ
Hi:
It is much easier to change the title in one of these ways (listed in order by easiest to hardest method):
1) change the style you use style=printer, style=minimal, style=sasweb to name just a few all have white backgrounds to the titles.

2) Use SAS/GRAPH options in the TITLE statement to alter title attributes (but the attribute change must be specified BEFORE the text string you are changing -- if you specify the changes AFTER the text string, they will have no impact.) Some attribute options you can change are:
-- foreground color -- COLOR= or C=
-- font value -- FONT= or F=
-- font size -- HEIGHT= or H=
-- font weight -- BOLD (single word attribute)

3) Use ODS ESCAPECHAR to specify TITLE string changes. I show the 9.1.3 syntax in TITLE1 and 9.2 syntax in TITLE2 in the #3 example below.

4) Change the Style template that you use

The first 3 methods are illustrated in the code posted at the end of this response. I do not generally recommend changing the style template just for the titles, since there are other, simpler, ways to accomplish that task.

cynthia
[pre]
ods listing close;
** Method 1: just use a different style;
ods tagsets.excelxp file='c:\temp\testtitle1.xls'
style=printer options(embedded_titles='yes');

title1 "1) Number and Results of Tests";
title2 "Change the style you use so titles are black and white";

proc tabulate data=sashelp.class;
class age;
var height;
table age,
height*(min mean median max);
run;
ods tagsets.excelxp close;

** Method 2: use title attribute options;
ods tagsets.excelxp file='c:\temp\testtitle2.xls'
style=sasweb options(embedded_titles='yes');

title1 color=black font='Courier New' bold h=12pt "2) Number and Results of Tests";
title2 "Title is normally Blue";

proc tabulate data=sashelp.class;
class age;
var height;
table age,
height*(min mean median max);
run;
ods tagsets.excelxp close;

** Method 3: Use ODS ESCAPECHAR;
ods tagsets.excelxp file='c:\temp\testtitle3.xls'
style=sasweb options(embedded_titles='yes');

ods escapechar='^';
title1 "^S={color=black font_size=12pt font_face='Courier New' font_weight=bold}3) Number and Results of Tests";
title2 "^{style[color=red font_weight=bold]Title is normally Blue but this title is red}";

proc tabulate data=sashelp.class;
class age;
var height;
table age,
height*(min mean median max);
run;
ods tagsets.excelxp close;

title;
[/pre]
deleted_user
Not applicable
Thanks! That did it. I was trying option 2, but it didn't work becasue I put the title text before the modifiers. Once I fixed that everything works!

Jeff

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
  • 1024 views
  • 0 likes
  • 2 in conversation