ODS PROC Report outlining a group of cells

[Petersi]

Is there a way to create a bolded outline of a group of cells using a style statement without having each individual cell's internal borders affected? For example, I could have a group of cells in a spreadsheet from A1 to B2. I want a bolded outline around the group, but not on the inside of the group.

[Cynthia_sas]

Hi, Peter:
You might try looking at this Tech Support FAQ:
http://support.sas.com/rnd/base/topics/templateFAQ/repoption.html

Because if I use some of these techniques in ODS HTML and/or ODS MSOFFICE2K, then I can see my highlighted border (for example) in the browser. However, when I go to open these files with Excel, the border instructions are not honored. Ditto for TAGSETS.EXCELXP -- the Spreadsheet ML file that's created does not use the HTMLSTYLE attribute at all. So the news is mixed -- sometimes these techniques work in one destination, but if you open the output file with another software product (such as Excel), Excel does not render the other file's instructions.

Sometimes, you can reverse engineer how Excel "wants" to have things, by building a sample spreadsheet, doing the highlighting or formatting you want and then seeing how Excel specifiies your formatting (either in HTML tags or XML tags) -- this sometimes works because you might be able to alter the SAS TAGSET template in order to supply the right tags in the right place. Sometimes, however, it doesn't work.

This is the point at which you have to draw a line in the sand and decide whether or if one destination or another will get you closer to what you want when you open the file in Excel. So, for example, if you are already using ODS HTML or ODS MSOFFICE2K to create your file, then you might consider reverse engineering from an Microsoft-HTML file (saved from Excel). If, however, you are already using ODS TAGSETS.EXCELXP, then you might consider reverse engineering from a Spreadsheet ML file (XML -- saved from Excel).

And, this is the point at which you might have to go to Tech Support for more help, since they have the most experience working with PROC TEMPLATE for tagset templates and with SAS to Microsoft issues.

For example, when I went into Excel and saved a small spreadsheet with a purple border around 4 cells (a1..b2), I discovered that Microsoft created 4 separate CSS style selectors:[pre]
.xl24
{mso-style-parent:style0;
border-top:none;
border-right:.5pt solid purple;
border-bottom:.5pt solid purple;
border-left:none;}
.xl25
{mso-style-parent:style0;
border-top:.5pt solid purple;
border-right:.5pt solid purple;
border-bottom:none;
border-left:none;}
.xl26
{mso-style-parent:style0;
border-top:.5pt solid purple;
border-right:none;
border-bottom:none;
border-left:.5pt solid purple;}
.xl27
{mso-style-parent:style0;
border-top:none;
border-right:none;
border-bottom:.5pt solid purple;
border-left:.5pt solid purple;}
[/pre]
and then USED a different STYLE for each of my 4 cells in these <TD> tags with class=.
[pre]
<td height=17 class=xl26 width=64 style='height:12.75pt;width:48pt'>aaa</td>
<td class=xl25 width=64 style='width:48pt'>wombat</td>
<td height=17 class=xl27 style='height:12.75pt'>aaa</td>
<td class=xl24 align=right x:num>111</td>
[/pre]

On the other hand, when I saved my little spreadsheet with the purple border on 4 cells as XML, then this is what Microsoft did:
[pre]

<Style ss:ID="s31">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#800080"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#800080"/>
</Borders>
</Style>
<Style ss:ID="s33">
<Borders>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#800080"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#800080"/>
</Borders>
</Style>
<Style ss:ID="s35">
<Borders>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#800080"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#800080"/>
</Borders>
</Style>
<Style ss:ID="s36">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#800080"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#800080"/>
</Borders>
</Style>


<Row>
<Cell ss:StyleID="s35"><Data ss:Type="String">aaa</Data></Cell>
<Cell ss:StyleID="s33"><Data ss:Type="String">wombat</Data></Cell>
<Cell><Data ss:Type="String">koala</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s36"><Data ss:Type="String">aaa</Data></Cell>
<Cell ss:StyleID="s31"><Data ss:Type="Number">111</Data></Cell>
<Cell><Data ss:Type="Number">222</Data></Cell>
</Row>
[/pre]

In my opinion, this has turned into a question for Tech Support, because based on testing the code below, we know that SAS/ODS is not currently producing the kind of style information that Excel is happy with -- so somehow you have to get from point A (what ODS is producing now) to point B (what Excel wants to have) and the only way I know how to get there is to change the tagset template that ODS is going to use. There's a remote chance (in my mind) that designing a custom CSS file for use with HTML destinations MIGHT work with the ODS HTMLCLASS attribute -- but that method I don't think will work for ExcelXP.

cynthia

The code I tested to find out that Excel didn't like the HTML or the XML that ODS created by default.
[pre]
proc sort data=sashelp.class out=class;
by sex name;
run;

ods html3 file='c:\temp\hilite_border_ht3.html' style=sasweb;
ods msoffice2k file='c:\temp\hilite_border_mso.html' style=sasweb;
ods tagsets.excelxp file='c:\temp\hilite_border_xp.xml' style=sasweb;

proc report data=class nowd
style(column)={background=_undef_ }
style(report)={rules=none frame=void background=_undef_};

column sex name age height weight;

compute name;
if name = 'Alice' then do;
call define('sex','style',
'style={htmlstyle="border-top:solid double green; border-left:solid double green;"}');
call define('name','style',
'style={htmlstyle="border-top:solid double green; border-right:solid double green;"}');
end;

if name = 'Barbara' then do;
call define('name','style',
'style={htmlstyle="border-bottom:solid double green; border-right:solid double green;"}');
call define('sex','style',
'style={htmlstyle="border-bottom:solid double green; border-left:solid double green;"}');
end;
endcomp;

run;

ods _all_ close;

[/pre]