excel losing decimal place

[wcw2]

In going from SAS (Proc Tabulate) to Excel (via ODS), whenever there is a ".0" (from tabulate) it is lost in Excel.

Example: "2.0" from tabulate is shown as "2.0" in the excel table, but is actually shown as "2" in the formula bar and in subsequent formula bar output. Anything other than ".0" is OK. Thought I had a workaround for this. Thanks so much.

[Reeza]

Show your code. Which ODS?

[wcw2]

ods excel file='......xlsx';
OPTIONS orientation=landscape nodate;
proc tabulate data=s31cm.cm_coded_post_s format=10. S=[foreground=black just=c] out=post_table;
class level4 medpnt arm; keylabel N=' ';
table level4=''*(medpnt='') all='Overall',
arm=''*(n='' colpctn=''*f=8.1 )
(n='' colpctn=''*f=8.1 )/misstext='0';
where level4 in ('OTHER ANTIHISTAMINES FOR SYSTEMIC USE, R06AX' 'PROTON PUMP INHIBITORS, A02BC');
run;
ods excel close;

[Reeza · Accepted solution]

You probably need to use TAGATTR style options then.
See Page 9 here:
https://support.sas.com/resources/papers/proceedings17/1482-2017.pdf

You'll need to find the correct format for Excel though, their example is slightly different but the syntax is the same.

[ballardw]

Since "2" and "2.0" calculate exactly the same I would not worry about differences in the "formula" bar of Excel. It does the same thing when you manually enter data.

[wcw2]

this is for a table to present....here's 2 columns in Excel, counts and percentages (1 decimal place).....there needs to be a ".0" added for the 2 and 100 percentage values

Arm 1: 2RHZE/4RH
0	0
2	3.9
5	9.8
1	2
14	27.5
7	13.7
15	29.4
6	11.8
1	2
51	100

[Reeza]

The issue is from Excel side, it autoformats the data. Either way, using TAGATTR as I suggested will resolve the issue.