06-21-2011 10:27 AM
06-21-2011 12:57 PM
06-27-2011 10:13 AM
Excel treats all numbers using a general format. Usually, when you create output for Excel using ODS, Excel will mostly respect date values, but will otherwise display numbers and even some character values (like zip codes), as numbers. As Eric explained, using the TAGATTR= style attribute is the way to control which Microsoft format should be used by Excel to display the data once the ODS file is opened in Excel. This was the topic of one of my SAS Global Forum papers this year: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf (complete with before/after screen shots and code examples).
06-28-2013 05:21 PM
Is there a way to use TAGATTR conditionally. I used proc format to create a nested format so percents show up as percents and dollars show up as currently. This is because my report displays both in in the same row demension. It works great in SAS but when I run an ODS statement to excel, I have the same issue with excel adding the decimal.
06-28-2013 11:25 PM
Without seeing what procedure you're using when you say "my report displays both (percent and dollar) in the same row dimension", it is hard to comment. It might be possible using PROC REPORT and a CALL DEFINE. I'm not sure it's possible with TABULATE or PRINT. It sort of depends on the code of your FORMAT, too. The bottom line is that Excel has its own default way of formatting cells and, usually, your SAS formats are not respected or used by Excel.
That means you need to specify TAGATTR values with the proper FORMAT: value for TAGATTR. So my recommendation would be to figure out what the right TAGATTR is for a simple situation and then branch into whether it can be done conditionally using your nested formats. Since you did not post your PROC FORMAT code, any sample data or your other PROCEDURE code, it's hard to comment in more detail. But I pretty much am sure that your SAS nested format will NOT work "out of the box" for Excel.
Perhaps the sample program below will get you pointed in the right direction for using TAGATTR. In the future it is helpful to start a new track instead of piggybacking on an already answered track. Then if you want to refer folks to the earlier track, you can just copy that tracks URL into your new track. I almost didn't read down to the end, because I saw that it was originally from 2011. If you need more help using TAGATTR for your code, then you might want to consider opening a track with Tech Support.
infile datalines dlm=',';
input grpvar ordvar type $ num;
options missing = ' ';
ods tagsets.excelxp file='c:\temp\testfmt.xml' style=sasweb;
proc report data=testit nowd;
column grpvar ordvar type num;
define grpvar / group noprint;
define ordvar / order noprint;
define type / display style(column)=Header;
define num / display;
if type = 'Total' then do;
else if type = '%' then do;
ods tagsets.excelxp close;