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?
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;
Have you tried removing the TAGATTR options?
Then I wouldn't have my tagattr options. I'm also losing the template formatting, but the tagattr options are important.
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...
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.
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;
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;
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?
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.)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.