topic Change the format of digits in Excel file in SAS Customer Intelligence

Change the format of digits in Excel file

SASdevAnneMarie — Thu, 28 Mar 2024 09:03:33 GMT

Hello Experts,

I joined the xlsx file to ma e-mail via excel with options ATTACH= ("XXX.xlsx" content_type="application/xlsx")

Do you know please how to change the point on comma in digits without changing the parameteres of my PC ?

There is some sas options ?

Thank you for your help !

Re: Change the format of digits in Excel file

Tom — Thu, 28 Mar 2024 13:46:02 GMT

You need to provide more information.

Are you talking about whether EXCEL uses a period or a comma to represent the decimal point in a number? For example whether one and one quarter is displayed as 1,25 instead 1.25?

It should not matter how you EMAIL the file, but how you create the file.

Did you create the file using SAS? If so what code did you use?

Do you know how Excel decides which style to use for decimal point? Perhaps you need to ask on an Excel forum?

Re: Change the format of digits in Excel file

SASdevAnneMarie — Thu, 28 Mar 2024 13:57:39 GMT

Thank you for your answer, Tom. Yes, I would like to have 1,25 instead 1.25. The file is created via proc export.

Re: Change the format of digits in Excel file

Tom — Thu, 28 Mar 2024 14:10:48 GMT

I doubt PROC EXPORT is putting any special Excel formatting instructions into the file.

In that case I would assume that the settings of the person opening the Excel file would make the choice of how numbers are displayed. You might try finding out how Excel makes the decision. Perhaps try having someone in the US open the file and someone how works in a region where comma is default and see if the file behaves differently. If they both see the same 1.25 then perhaps try changing the locale and/or language settings in the SAS session that is generating the file.

If you want more control of the Excel formatting then use ODS EXCEL and PROC PRINT to make the file. Then you can use style options to set Excel formatting.

Re: Change the format of digits in Excel file

SASdevAnneMarie — Thu, 28 Mar 2024 14:28:15 GMT

Thank you, Tom!
Do you know, please the option for comma in ods excel?

Re: Change the format of digits in Excel file

Tom — Thu, 28 Mar 2024 14:43:09 GMT

Looks to me like it is an Excel setting.

https://support.microsoft.com/en-us/office/change-the-character-used-to-separate-thousands-or-decimals-c093b545-71cb-4903-b205-aebb9837bd1e#:~:text=By%20default%2C%20Microsoft%20Excel%20uses,Click%20File%20%3E%20Options.

Re: Change the format of digits in Excel file

SASdevAnneMarie — Thu, 28 Mar 2024 14:48:30 GMT

Yes, my Excel setting is point. I’m creating the xlsx files with points. I’m asking if it is possible to create the file with comma without changing the settings.

Re: Change the format of digits in Excel file

Patrick — Thu, 28 Mar 2024 15:28:05 GMT

@SASdevAnneMarie wrote:
Yes, my Excel setting is point. I’m creating the xlsx files with points. I’m asking if it is possible to create the file with comma without changing the settings.

I believe the answer is no because Excel stores the value internally always the same.

It's Excel's regional setting that drives how the value gets displayed meaning it will depend on who opens your Excel on what they will see.

Re: Change the format of digits in Excel file

SASdevAnneMarie — Fri, 29 Mar 2024 12:49:48 GMT

Thank you, Tom !
So I can't do any modification in code, it is Excel settings ?