Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

[wcp_fnfg]

When I change from ODS tagset.excelxp to ODS Excel in the attachment, I lose my formatting like the dollar format and even the border lines for the data.  How can I keep the tagset formatting and styling?

[Reeza]

Have you tried removing the TAGATTR options?

[wcp_fnfg]

Then I wouldn't have my tagattr options.  I'm also losing the template formatting, but the tagattr options are important.

[Reeza]

A benefit of ODS Excel vs Tagsets is that:

 using SAS formats with the Excel destination to control cell formatting

See this paper here:

http://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf

So some of your tagattr may have to change when changing destinations. It's not a one to one conversion between the two...

[wcp_fnfg]

I'm largely concerned that the styles don't end up the same, particularly in this example when using the template.  The outputs don't look alike at all.

[Tim_SAS]

Try this. The main difference is that, in the destination for Excel, you have to prefix a custom format definition with the word "format:". I also changed the column widths and made a few other changes but that was the big one.

options dlcreatedir;
*libname newdir "/sas/data/Risk_Mgmt/users/pattowe/temp";
libname newdir '.';
/*filename testdir '/sas/data/Risk_Mgmt/users/pattowe/temp';*/
/*data _null_;*/
/*   rc=fdelete('testdir');*/
/*   put rc=;*/
/*   msg=sysmsg();*/
/*   put msg=;*/
/*run;*/


option compress=yes validvarname=upcase nolabel;

%let tag_dol = tagattr='format:$#,##0.0;[Red]($#,##0.0);-';
%let tag_comma = tagattr='format:#,##0.0;[Red](#,##0.0);-';
%let tag_pct = tagattr='format:0.00%;[Red]-0.00%;-';

proc template;
	define style styles.wesweb;
	parent = styles.sasweb;
	style rowheader from rowheader /
		background = #FFFFFF
		color = #000000
		font_weight= bold;
	class container /
		font_size = 10pt;
	end;
run;

*Embedded subtotal, Highlight Subtotals as Blue, Grand Total Row as Orange;
*TagAttr necessary to carry format to excel with ODS Tagsets.ExcelXp;

ods listing close;
ods html close;
title;
options missing = 0;
ods excel
file= "Tabulate_Test.xlsx"
style=wesweb
	options (
sheet_interval="none" 
absolute_column_width = '7,15,9,9,9,9,9,9,11' 
embedded_titles='yes'
embedded_footnotes='yes'
orientation = 'portrait'
center_horizontal = 'yes'
pages_fitwidth = '1'
pages_fitheight = '1'
print_header= "SASHelp Average Car Prices"
print_footer='&RPage &P of &N'
/*autofit_height='yes'*/
row_heights='26'
sheet_name = 'Prices')
;

proc tabulate data = sashelp.cars missing;
class origin make type;
var msrp;
table
(origin=''*{s={&tag_dol.}}
	* (make='' all='Total'*{s={font_weight=bold background=#B8CCE4 &tag_dol.}}) /*Blue Highlight, Bold*/
 all='Grand Total'*{s={font_weight=bold background=#FABF8F &tag_dol.}}) /*Orange Highlight, Bold*/

, (type='' all='Grand Total')
* (msrp=''*mean='' *f=comma22.2 ) /*Comma format for SAS report, does not carry to Excel*/
/box = {label='Mean MSRP' style={vjust=bottom}} row=float;
run;

ods excel close;

[wcp_fnfg]

This definitely handles the dollar formats, I didn't know about that extra syntax.

Any idea why I'm losing my border\cell lines with this style? It seems like proc template is interacting differently with this.

[Tim_SAS · Accepted solution]

Change your PROC TEMPLATE code to this:

proc template;
	define style styles.wesweb;
	parent = styles.sasweb;
	style rowheader from rowheader /
		background = #FFFFFF
		color = #000000
		font_weight= bold;
	class container /
		font_size = 10pt;
	class cell /
                borderstyle = solid
                borderwidth = 0.1pt
                bordercolor = cxCCCCCC;
	end;
run;

[wcp_fnfg]

Rock n roll. Thanks!

[wcp_fnfg]

How did you know that needed to be added\changed?  Is there somewhere in EG that I could've seen what would be impacted or changed?

[Tim_SAS]

Hmmm...that's hard to say. Your wesweb style has sasweb for its parent, so I examined the sasweb style to see how its various classes (table, output, etc.) are defined and what their parent-child relationships are. That gave me some things to try. After that it was just trial-and-error. What I posted was my 4th or 5th attempt.

(I'm not an EG user. I'm sufficiently old-school that I still use DMS.)