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
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
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;
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;
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
Thanks a lot Cynthia!
I will try to check with SAS support.
Lena
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!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.