Hi Cynthia,
According to the method "f=comma12.2*{s={tagattr="format:###,###,###"}}; ;"
you suggested to use in Excelxp tabulate formatting.
I did get the right result in the first tabulate of my code, but failed in the second tabulate.
These 2 tabulate are almost the same.
Do you have any idea what is the reason accounts for this issue?
Hi:
Without seeing your code, it is just a guess about what the issue is. If the TAGATTR works in one TABULATE and not the other, some of the possibilities might be:
1) the TAGATTR string is applied in the ROW dimension, for example and the COL dimension format is overriding it (the fix for this is to use STYLE_PRECEDENCE=ROW); or
2) the TAGATTR format is wrong (have you made sure it is correct? In the back of this paper, I show how to "reverse engineer" finding out whether you are using the correct value for TAGATTR. http://support.sas.com/resources/papers/proceedings11/266-2011.pdf) or
3) you are applying the style override to the wrong variable, wrong crossing or wrong piece of the table (such as using TAGATTR in a CLASS statement instead of a TABLE statement) or
4) "almost the same" is just different enough to cause your issue, or
5) Excel has problems using your format (related to #2, but you are asking for a custom format that Excel doesn't like), or
6) something else....depending on your data or your code.
Can you post a simplified version of your code, perhaps using SASHELP.SHOES, as shown in one of the earlier posts? If you can't replicate your issue using SASHELP.SHOES, then the problem might be with your data. If you can replicate your issue using SASHELP.SHOES, then you've provided code that someone might be able to run in order to see whether they can help you or you've got code that you can send to Tech Support.
cynthia
**Sample code to start with;
** SASHELP.SHOES has 3 numeric vars: sales, inventory and returns;
** with region, product and subsidiary as all possible CLASS variables.;
** Table below uses only region, product and sales.;
ods listing close;
ods tagsets.excelxp file='c:\temp\usetagattr.xml'
style=sasweb;
proc tabulate data=sashelp.shoes;
where region in ('Canada', 'United States');
class region product;
var sales;
table region*product,
sales*sum*f=comma12.2*{s={tagattr="format:###,###,###"}}
/box='TABLE stmt' ;
run;
ods tagsets.excelxp close;
Cynthia,
Thanks for your quick reponse.
I solved the problem after including "excltags.tpl" downloaded from sas website.
libname tagslib "/DIR_HOME";
ods path(prepend) tagslib.templat(update);
%inc "/DIR_HOME/excltags.tpl";
But I do not really understand the reason.
Could you give me an insight into this issue?
Hi:
Excltags.tpl is the underlying PROC TEMPLATE code that 1) builds the tagset template which 2) instructs ODS how to write the XML markup language, as dictated by the Microsoft specification. Spreadsheet Markup Language is what Microsoft invented in 2002/2003 of Office in order to have an XML definition for a workbook with spreadsheets. Apparently the issue you ran into involved an older version of the tagset template and was fixed by installing/downloading/updating the version to be the most current version of the tagset template.
cynthia
Thanks a lot for your specifications
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.