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;
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;
Thanks, Cynthia! Will do. Just wanted to make sure it wasn't something I was overlooking.
Best wishes,
Ben
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: