topic Currency Format Without Decimal Fraction When Output to Excel By Tagset in ODS and Base Reporting

Currency Format Without Decimal Fraction When Output to Excel By Tagset

c8826024 — Tue, 21 Jun 2011 14:27:27 GMT

I use PROC REPORT to create a report and dump into Excel by tagsets.ExcelXP. I define the culumn by:
define a9 /analysis sum format=dollar10. 'Sale' style(column)={background=lightyellow font_size=1.5};

It comes out something like $100,789.00 or $200.00. How can I use a correct format to get rid of the decimal fraction?

Thanks.

Re: Currency Format Without Decimal Fraction When Output to Excel By Tagset

Eric_SAS — Tue, 21 Jun 2011 16:57:57 GMT

By default the format for currency in Excel puts decimal places.

If you want Excel to do something else you'll have to provide an excel format on the column. The tagattr style attribute can do this for you.

Add the following to your style over rides on the define statement.

tagattr=format:$###,###

Re: Currency Format Without Decimal Fraction When Output to Excel By Tagset

Ksharp — Mon, 27 Jun 2011 08:10:07 GMT

Did you try format dollar10.0 ?

Ksharp

Currency Format Without Decimal Fraction When Output to Excel By Tagset

Cynthia_sas — Mon, 27 Jun 2011 14:13:46 GMT

Hi:

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

cynthia

Re: Currency Format Without Decimal Fraction When Output to Excel By Tagset

benfirst321 — Fri, 28 Jun 2013 21:21:29 GMT

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.

Re: Currency Format Without Decimal Fraction When Output to Excel By Tagset

Cynthia_sas — Sat, 29 Jun 2013 03:25:50 GMT

Hi:

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.

cynthia

data testit;

infile datalines dlm=',';

input grpvar ordvar type $ num;

return;

datalines;

1,1,Sales,.

1,2,Total,2222.22

1,3,%,.5436

2,1,Returns,.

2,2,Total,1111.11

2,3,%,.4564

;

run;

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;

compute num;

if type = 'Total' then do;

call define(_col_,'style','style={tagattr="Format:$#,###,#00."}');

end;

else if type = '%' then do;

call define(_col_,'style','style={tagattr="Format:##0.00%"}');

end;

endcomp;

run;

ods tagsets.excelxp close;