topic Re: ODS ExcelXP- borders in the title and headers in ODS and Base Reporting

ODS ExcelXP- borders in the title and headers

LenaS — Mon, 16 Jul 2012 15:45:00 GMT

I am using the following code to create an output in excel :

ods tagsets.ExcelXP style=sansprinter path="C:\test" file="1stStep.xls"

options(embedded_titles='yes'

sheet_interval='bygroup'

);

proc report data=test nowd;

by country;

columns Country

District

Transit

UserID

( "YTD"

Credit_Life

Optional_Health_Insurance

Optional_Unemployment_Insurance

);

title 'Insurance products';

run;

How can I put Thick Box Border around the Title and the headers?

Thanks a lot in advance!

Lena

Re: ODS ExcelXP- borders in the title and headers

Cynthia_sas — Mon, 16 Jul 2012 17:13:24 GMT

Hi:

Usually, with TAGSETS.EXCELXP, if you want to change the title, you have to use a STYLE template. Otherwise, to just put borders around the headers, you can sometimes just use a STYLE= override, as shown below for PROC REPORT (something similar would also work for PRINT and TABULATE). For example, the code below shows how to make the borders for the headers thicker using PROC REPORT. For help with a style template, you might want to open a track with Tech Support or look for some user group papers about creating style templates for use with TAGSETS.EXCELXP.

The reason I recommend working with Tech Support is that the code you use for making your style template will change based on whether you have SAS 9.2 or higher or whether you are still using SAS 9.1.3.

cynthia

ods _all_ close;

ods tagsets.excelxp file='c:\temp\headerborder.xml'

style=sansprinter

options(embedded_titles='yes');

proc report data=sashelp.class(obs=2) nowd

style(header)={borderstyle=solid borderwidth=5pt bordercolor=cyan cellpadding=4pt vjust=m};

title 'This is My Title';

run;

ods _all_ close;

title; footnote;

Re: ODS ExcelXP- borders in the title and headers

LenaS — Mon, 16 Jul 2012 18:37:41 GMT

Thanks Cynthia!

But it's giving me this error :

73093 ods _all_ close;

73094

73095 title; footnote;

73096 ods _all_ close;

73097

73098 ods tagsets.excelxp file='C:\Lena\headerborder.xls'

73099

73100 style=sansprinter

73101

73102 options(embedded_titles='yes');

NOTE: Writing TAGSETS.EXCELXP Body file: C:\Lena\headerborder.xls

NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.28, 08/29/05). Add options(doc='help') to the

ods statement for more information.

73103

73104

73105

73106 proc report data=sashelp.class(obs=2) nowd

73107

73108 style(header)={borderstyle=solid borderwidth=5pt bordercolor=cyan cellpadding=4pt

-----------

180

73108 ! vjust=m};

ERROR 180-322: Statement is not valid or it is used out of proper order.

73109

73110 title 'This is My Title';

73111

73112 run;

NOTE: There were 2 observations read from the data set SASHELP.CLASS.

NOTE: PROCEDURE REPORT used (Total process time):

real time 0.04 seconds

cpu time 0.01 seconds

73113

73114 ods _all_ close;

73115

73116 title; footnote;

Re: ODS ExcelXP- borders in the title and headers

Cynthia_sas — Mon, 16 Jul 2012 18:58:23 GMT

Hi:

I'd recommend working with Tech Support. It's possible that your version of SAS does not support borderstyle, etc changes. Those were introduced in SAS 9.2, I think. Also, there might be something odd about your version of TAGSETS.EXCELXP. This date in your log looks very old to me:

NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.28, 08/29/05).

Version 1.28 isn't even listed on this web site of versions for TAGSETS.EXCELXP -- so there could be something wrong there:

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

I've attached my log, run in SAS 9.3 (see no_error_sas93.png screenshot). You can see I do NOT have an error. However, I can replicate your error, by using an attribute name (FRED) which is not a valid style attribute (see not_valid.png screenshot).

Tech Support can collect the relevant information and help you figure it out. As you can see from my screenshots posted previously, you can change the header the way you want. I'm not sure what about your code is breaking, but Tech Support can help you figure it out. To open a track with Tech Support, fill out the form at this link: http://support.sas.com/ctx/supportform/createForm

cynthia

Re: ODS ExcelXP- borders in the title and headers

LenaS — Mon, 16 Jul 2012 19:24:16 GMT

Thanks a lot Cynthia!

I will try to check with SAS support.

Lena

Re: ODS ExcelXP- borders in the title and headers

LenaS — Tue, 17 Jul 2012 15:48:52 GMT

Hi Cynthia,

I have contacted Tech support and they told me that the option borderstyle= is not a valid attribute in SAS 9.1.

So I need to use just borderwidth=5pt.

But if I want to create the same border around the title , how do I do it?

style(title)={ borderwidth=5pt bordercolor=cyan cellpadding=4pt vjust=m}; doesn't work

Thanks!

Re: ODS ExcelXP- borders in the title and headers

Cynthia_sas — Tue, 17 Jul 2012 16:22:38 GMT

Hi:

STYLE(TITLE) is not valid syntax for PROC REPORT, since the SAS TITLE is set in a GLOBAL statement and is not linked to any particular procedure. As I explained in my original post, you generally have to change the style template in order to change the style of the title. There's an example of changing the title in my paper on style templates.

http://support.sas.com/resources/papers/proceedings10/033-2010.pdf (see page 10) However, that paper features SAS 9.2 syntax, not 9.1 syntax.

There were significant syntax changes for style templates between SAS 9.1 and SAS 9.2. Lauren (Haworth) Lake showed some of the 9.1 syntax in her papers on style templates: http://www2.sas.com/proceedings/sugi30/132-30.pdf and http://www2.sas.com/proceedings/sugi29/125-29.pdf, but she focussed on ODS PDF and ODS RTF, not ODS TAGSETS.EXCELXP.

Again, if you work with Tech Support, they can help you with a style template and syntax that would work in SAS 9.1 for TAGSETS.EXCELXP.

cynthia