The SAS Output Delivery System and reporting techniques

Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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?


Accepted Solutions
Solution
‎10-07-2016 01:02 PM
Super Contributor
Posts: 394

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

Have you tried removing the TAGATTR options?

Frequent Contributor
Posts: 84

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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

Super User
Posts: 19,770

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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...

Frequent Contributor
Posts: 84

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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.

Super Contributor
Posts: 394

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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;
Frequent Contributor
Posts: 84

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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.
Solution
‎10-07-2016 01:02 PM
Super Contributor
Posts: 394

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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;
Frequent Contributor
Posts: 84

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

Rock n roll. Thanks!
Frequent Contributor
Posts: 84

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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?

Super Contributor
Posts: 394

Re: Losing Styles converting ODS Tagset.ExcelXP to ODS Excel

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.)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 774 views
  • 3 likes
  • 3 in conversation