topic Re: Disappearing Title? Tabulate and tagsets.excelxp in ODS and Base Reporting

Disappearing Title? Tabulate and tagsets.excelxp

deleted_user — Wed, 10 Nov 2010 14:45:49 GMT

[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

Re: Disappearing Title? Tabulate and tagsets.excelxp

Cynthia_sas — Wed, 10 Nov 2010 15:01:39 GMT

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

Re: Disappearing Title? Tabulate and tagsets.excelxp

deleted_user — Wed, 10 Nov 2010 15:11:15 GMT

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

Re: Disappearing Title? Tabulate and tagsets.excelxp

deleted_user — Wed, 10 Nov 2010 15:57:10 GMT

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

Re: Disappearing Title? Tabulate and tagsets.excelxp

Cynthia_sas — Wed, 10 Nov 2010 16:07:26 GMT

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]

Re: Disappearing Title? Tabulate and tagsets.excelxp

deleted_user — Wed, 10 Nov 2010 16:26:01 GMT

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