The SAS Output Delivery System and reporting techniques

ODS ExcelXP- borders in the title and headers

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

ODS ExcelXP- borders in the title and headers

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


Accepted Solutions
Solution
‎07-16-2012 02:58 PM
SAS Super FREQ
Posts: 8,862

Re: ODS ExcelXP- borders in the title and headers

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


no_error_sas93.pngnot_valid.png

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,862

Re: ODS ExcelXP- borders in the title and headers

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;


header_border.png
Occasional Contributor
Posts: 9

Re: ODS ExcelXP- borders in the title and headers

Posted in reply to Cynthia_sas

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;

Solution
‎07-16-2012 02:58 PM
SAS Super FREQ
Posts: 8,862

Re: ODS ExcelXP- borders in the title and headers

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


no_error_sas93.pngnot_valid.png
Occasional Contributor
Posts: 9

Re: ODS ExcelXP- borders in the title and headers

Posted in reply to Cynthia_sas

Thanks a lot Cynthia!

I will try to check with SAS support.

Lena

Occasional Contributor
Posts: 9

Re: ODS ExcelXP- borders in the title and headers

Posted in reply to Cynthia_sas

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!

SAS Super FREQ
Posts: 8,862

Re: ODS ExcelXP- borders in the title and headers

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 1292 views
  • 3 likes
  • 2 in conversation