topic creating thicker borders around the outside of a report using excelxp in ODS and Base Reporting

creating thicker borders around the outside of a report using excelxp

BenConner — Wed, 23 Apr 2014 17:18:58 GMT

Hi,

I've been trying different options to create a 3pt border around proc report output going to an excel spreadsheet and am coming up empty. I need the entire report surrounded by a 3pt border.

The code I've been using is below. I thought I would be able to get it with the style(report) option but that had no effect. The other border thicknesses are working fine (between columns, vendors, etc.)

Thanks!

--Ben

ods tagsets.excelxp file='c:\test.xml' style=default;

proc report data=in.table3 nowd style(hdr)={background=darkblue borderwidth=1pt bordercolor=black borderstyle=solid
                                            font_face=Calibri fontsize=12pt color=white tagattr='rotate:-90'}
                                style(report)={borderbottomwidth=3pt}
                                style(column)={backgroundcolor=white borderwidth=1pt bordercolor=black borderstyle=solid};

where question <=24;

columns ( ' ' vendor_id) ('Month' month ) ( 'Records' records) question,count dummy;

     define vendor_id                        / group order=data '' style={borderleftwidth=3pt borderrightwidth=3pt};
     define month                            / group '';
     define records                          / analysis max '' style(column)={tagattr='format:#,##0'};
     define question                         / across order=data '';
     define count                            /   ''            style(column)={tagattr='format:#,##0'};
     define dummy                            / computed noprint;

     compute dummy;
        if vendor_id ^= ' ' then do;
           call define(_row_,'style','style={bordertopwidth=3pt}');
           end;
     endcomp;

     format vendor_id    $vendors.
            question     question.
            month        month.
            count        comma9.;
run;
ods tagsets.excelxp close;

Re: creating thicker borders around the outside of a report using excelxp

Cynthia_sas — Wed, 23 Apr 2014 22:38:46 GMT

Hi, when I see questions like this, I always stop and ask myself how do the various controls work in other destinations, such as HTML to try to figure out whether the issue is with Excel and its peculiarities or whether the issue is with SAS. So, if I run a modified version of your code and make both an HTML output and an XML output, I get what is shown in the attached screen shot. As you can see, the HTML output respects the various border colors and settings. I made some of the settings lighter colors (such as for the headers) so I could see the impact of the cyan bordercolor. As you can see, the cyan and thicker line clearly shows in the HTML output when viewed in the browser, but not in the XML output created by TAGSETS.EXCELXP. Now that I know the HTML output uses the colors, my next step is to open the HTML file with Excel using File--> Open from inside Excel. As you can see some, but not all, of the HTML borders are respected by Excel.

To me this means that the colors/widths/borders not showing up the way you want is due to Excel not respecting the SAS style. My recommendation is that you work with Tech Support on this question. I doubt there is a way to get the TAGSETS.EXCELXP output to look "exactly" as you want, given the different treatment of the HTML file by Excel. It could be that TAGSETS.EXCELXP will just not support the style changes OR it could be that to get TAGSETS.EXCELXP to support the style changes you have to alter the style template you use for the output. Tech Support will know for sure which one of these alternatives is the case.

Cynthia

*** the code;

ods html file='c:\temp\show_border_html.html' style=default;
ods tagsets.excelxp file='c:\temp\show_border_xp.xml' style=default;

proc report data=sashelp.prdsale nowd
     style(hdr)={background=lightyellow borderwidth=1pt bordercolor=black borderstyle=solid
                 font_face=Calibri fontsize=12pt color=black tagattr='rotate:-90'}
     style(report)={borderbottomwidth=3pt
                    bordercolor=cyan borderspacing=0}
     style(column)={backgroundcolor=white borderwidth=1pt bordercolor=black borderstyle=solid};

where quarter=1 and year = 1993;

columns ( ' ' country) ('Month' month ) ( 'Records' predict) division,actual ;

     define country / group order=data ''
            style(column)={borderleftwidth=3pt borderrightwidth=3pt};
     define month   / group '' order=data f=monyy7.;
     define predict / analysis max '' f=comma6.
            style(column)={tagattr='format:#,##0'};
     define division/ across order=data '';
     define actual /   '' f=comma6.
            style(column)={tagattr='format:#,##0'};

     compute country;
        if country ^= ' ' then do;
           call define(_row_,'style','style={bordertopwidth=3pt}');
           end;
     endcomp;

run;
ods _all_ close;

Re: creating thicker borders around the outside of a report using excelxp

BenConner — Wed, 23 Apr 2014 22:50:49 GMT

Thanks, Cynthia! Will do. Just wanted to make sure it wasn't something I was overlooking.

Best wishes,

Ben

Re: creating thicker borders around the outside of a report using excelxp

BenConner — Thu, 24 Apr 2014 17:53:28 GMT

Wanted to follow up on the original question. Apparently that functionality is broken in the ExcelXP tagset at this time, so I'll need to handle it with VBA.

--Ben