BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wcp_fnfg
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tim_SAS
Barite | Level 11

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

10 REPLIES 10
Reeza
Super User

Have you tried removing the TAGATTR options?

wcp_fnfg
Obsidian | Level 7

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

Reeza
Super User

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
Obsidian | Level 7

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
Barite | Level 11

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
Obsidian | Level 7
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
Barite | Level 11

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
Obsidian | Level 7
Rock n roll. Thanks!
wcp_fnfg
Obsidian | Level 7

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
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2351 views
  • 4 likes
  • 3 in conversation