topic Re: proc export Excel decimal issue in SAS Programming

proc export Excel decimal issue

Siwon — Thu, 21 Dec 2023 06:17:44 GMT

Hello!
First of all, I'm sorry for my poor English.
The column [amt] is 10 decimals,
when exported it comes to 7 decimals.
In EG, 10 decimals.
In Client, 7 decimals.
Do I have to change the setting?
I think there is a way with ods excel, so I tried it, but it says there is no module.
Please let me know the syntax that I can do with proc export.

- sum(amt) / 100000000 as amt format=comma10.10
- proc export data=out.prod_sales
outfile = '/user/product/output'
dbms =xlsx
replace;
sheet=sales_amt;
results(below)
- EG : 1.5211661500
- Client : 1.5211662

Re: proc export Excel decimal issue

andreas_lds — Thu, 21 Dec 2023 07:22:17 GMT

This could be one of the fancy data optimizations implemented in Excel itself.

But: Are you sure, that you have applied the format comma10.10? If I try to do so, the log displays:

The decimal specification of 10 must be less than the width specification of 10.

Changing the format to comma15.10 fixed the issue.

Re: proc export Excel decimal issue

Kurt_Bremser — Thu, 21 Dec 2023 08:47:23 GMT

Maxim 2: Read the Log.

It will alert you to the fact that you used an invalid format specification. The number to the left of the dot must accommodate

the number to the right
the decimal dot
an eventual sign
at least one digit to z'the left of the decimal dot, but more if your data needs it

I suggest to use 20.10 as a start and see what it does.