topic Re: excel losing decimal place in SAS Health and Life Sciences

excel losing decimal place

wcw2 — Wed, 01 Sep 2021 16:25:29 GMT

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.

Re: excel losing decimal place

Reeza — Wed, 01 Sep 2021 16:38:44 GMT

Show your code. Which ODS?

Re: excel losing decimal place

wcw2 — Wed, 01 Sep 2021 16:42:36 GMT

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;

Re: excel losing decimal place

ballardw — Wed, 01 Sep 2021 16:56:07 GMT

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.

Re: excel losing decimal place

Reeza — Wed, 01 Sep 2021 17:00:53 GMT

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.

Re: excel losing decimal place

wcw2 — Wed, 01 Sep 2021 17:08:23 GMT

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

Re: excel losing decimal place

Reeza — Wed, 01 Sep 2021 17:15:54 GMT

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