Excel XP Proc Report Borderstyle Proc Template

[deleted_user]

I researching for the last 5 hrs, to get dashed borders in the excel from a proc report. I saw many papers and threads on this but I am still not able to succeed. I am using sas9.2 and most recent XP tagset. I wish if it's as simple as it's in other ODS. Thanks for your help.

ods path work.templat(update) sashelp.tmplmst(read) ;

proc template;
define style styles.mystyle;
parent=styles.printer;
style hidden from data/ tagattr="wrap:yes" ;
replace fonts /
'TitleFont' = ("Arial",14pt,Bold)
'TitleFont2' = ("Times Roman",12pt,Bold Italic)
'StrongFont' = ("Times Roman",10pt,Bold)
'EmphasisFont' = ("Times Roman",10pt,Italic)
'headingEmphasisFont' = ("Times Roman",11pt,Bold Italic)
'headingFont' = ("Arial",10pt)
'docFont' = ("Arial",10pt)
'footFont' = ("Times Roman",13pt)
'FixedEmphasisFont' = ("Courier",9pt,Italic)
'FixedStrongFont' = ("Courier",9pt,Bold)
'FixedHeadingFont' = ("Courier",9pt,Bold)
'BatchFixedFont' = ("Courier",6.7pt)
'FixedFont' = ("Courier",9pt);

replace color_list /
'link' = blue
'bgH' = lightyellow
'bgT' = white
'bgD' = white
'fg' = black
'bg' = white;

replace Table from Output /
frame = hsides
rules = all
cellpadding = 4pt
cellspacing = 0.25pt
borderwidth = 0.75pt
borderbottomstyle = dashed
bordertopstyle = dashed;

style SystemFooter from SystemFooter /
font = fonts("footFont");

style Data from Data /
background=color_list('bgD');
end;
run;

%include 'c:\temp\excltags.tpl';

ods escapechar='^';
ods tagsets.excelxp file = 'c:\temp\test1.xls' style = mystyle ;
proc report data = sashelp.class
style (column) = [borderbottomstyle = dashed bordertopstyle = dashed];run;

[Cynthia_sas]

Hi:
Before I go down the road of a custom style template or a single destination I like to see what the default behavior is in a simple situation. So with this code, when I review the output, I see that the colors specified are used by all destinations--within limits -- with HTML, for example, a borderbottom is treated distinctly from a bordertop -- in other destinations, the borders are shared and so the bottom of one cell has a border that is "shared" with the cell underneath it. So the bottom of one cell and the top of another cell are essentially the same border.

I find, in the test code below, that only RTF really seems to use the different BORDERxxxSTYLE attributes -- none of the other destinations seem to use the dashed or dotted style, but they all use the colors to some extent.

So given that default behavior, it might be to your best advantage to work with Tech Support on this question. Given the default behavior with BORDERBOTTOMSTYLE and TAGSETS.EXCELXP, I am not sure that a custom style template will help here. If I change the program on pages 14, 15 and 16 of this paper http://analytics.ncsu.edu/sesug/2008/CS-050.pdf (shows a custom style template for changing borders) to use DASHED as the value, I still only see SOLID lines when I open the TAGSETS.EXCELXP output with Excel.

The fact that 2 tests -- one with a custom template and one without a custom template -- both show that DASHED is not used, leads me to believe that there is something about the Excel Spreadsheet Markup Language XML that makes Excel ignore the DASHED value for the attribute value. (I did not post the custom template code below -- only the first test program.)

If there is a template solution to achieving dashed divider lines, then you would have to work with Tech Support to find it.

cynthia
[pre]
ods tagsets.excelxp file = 'c:\temp\borderbottom.xls' style=sasweb;
ods html file='c:\temp\borderbottom.html' style=sasweb;
ods pdf file='c:\temp\borderbottom.pdf';
ods rtf file='c:\temp\borderbottom.rtf';
** if border bottom color, etc works, then other areas...;
** bordertopcolor, etc should also work;

proc report data=sashelp.class nowd
style(report)={frame=void rules=none cellspacing=0}
style(column)={borderbottomcolor=red borderbottomwidth=5 borderbottomstyle=dashed
bordertopcolor=green bordertopwidth=5 bordertopstyle=dotted
borderleftcolor=blue borderleftwidth=5 borderleftstyle=double
borderrightcolor=pink borderrightwidth=5 borderrightstyle=solid};

title "Compare Destinations with REPORT: borderbottomcolor= and borderbottomwidth";
column name age height;
run;

ods _all_ close;
[/pre]