The SAS Output Delivery System and reporting techniques

Export to Excel. Number fields in format COMMAXN.M become text field.

Reply
Contributor
Posts: 26

Export to Excel. Number fields in format COMMAXN.M become text field.

Hello,
I have small issue related with proc report exporting to Excel(2003).
So in target table exists fields with format COMMAXN.M and after exporting excel treat these fields as text.
For example number:100.245,05 was treated as text.
I founded reverse problem on this forum(String data converts into numeric) and corrections of this error was related with column style:
style(column)={HTMLSTYLE="mso-number-format:00000"};

But I didn't sow any info regarding this my issue...
Think solution must be similar to this one higher, but I spent one hour without big success and I almost gave upSmiley Happy.

Thank you in advancedSmiley Happy!
SAS Super FREQ
Posts: 8,744

Re: Export to Excel. Number fields in format COMMAXN.M become text field.

Hi:
PROC REPORT wouldn't "export" to Excel directly. Are you using ODS to create a file that Excel can open??? If you are using ODS, what is the destination you are using:
1) ODS CSV
2) ODS HTML, ODS MSOFFICE2K (or other HTML-based destinations)
3) ODS TAGSETS.EXCELXP????

When you use ODS to send output into an ASCII text file (such as HTML, CSV or XML file) that Excel can open and render, Excel renders the table data using Excel defaults for column width and column format and makes assumptions on whether a certain column is a "string" or a number.

There are ways (with HTML and XML files) to send a Microsoft format to Excel, but the method will vary depending on the destination you are using. The HTMLSTYLE method you show (which is the mso-number-format for displaying leading zeroes) will only work with HTML-based ODS destinations.

In order to supply the correct mso-number-format for the HTMLSTYLE attribute, you first have to know what the Microsoft format looks like. However, using a comma for the decimal separator and the period for the thousands separator may be an Excel "locale" specific setting. And in this instance, I don't know whether you actually can use ODS to override those locale specific settings with a Microsoft custom format. That would be a question for Tech Support. To open a track with Tech Support, fill out the form at this link:
http://support.sas.com/ctx/supportform/createForm

cynthia
Contributor
Posts: 26

Re: Export to Excel. Number fields in format COMMAXN.M become text field.

Hi,
Yes, I use ODS to create a file that Excel can open.
Destination - ODS TAGSETS.EXCELXP.

So as I understand from your letter this issue occurs because of local specific settings of excel?
So there is no simple solution and it's better to ask this question in Tech Support???

Thanks a lotSmiley Happy!
SAS Super FREQ
Posts: 8,744

Re: Export to Excel. Number fields in format COMMAXN.M become text field.

Hi:
When I did a Google search on "comma as decimal separator Excel" most of the hits indicated that there was an International setting you had to use to get a numeric cell with commas as the decimal separator and periods as the thousands separator. Here are only a few of the hits I found on Google:
http://digilander.libero.it/foxes/Excel/EXcel_How_to_change_%20international_setting.htm
http://www.pcuser.com.au/pcuser/hs2.nsf/lookup+1/96B4A5BCFEF4D61BCA256BC600123779
http://forums.macrumors.com/showthread.php?t=396298
http://www.proz.com/forum/general_technical_issues/34573-excel%3A_turning_comma_into_decimal_point_2...

This indicates to me that Excel expects the International/locale setting to be set appropriately. I doubt whether Tech Support will have any workaround for something that is an internal Excel setting, but you could open a track and ask them. You may just have to live with your SAS-formatted number being a Text field if there is no workaround. Or figure out a way to change the locale setting with an Excel macro or VB script.

cynthia
Contributor
Posts: 26

Re: Export to Excel. Number fields in format COMMAXN.M become text field.

Thank Cynthia!
Valued Guide
Posts: 2,175

Re: Export to Excel. Number fields in format COMMAXN.M become text field.

Yuriy2301

I used to get this problem "numbers as text" when I started using tagset.excelXP.
My solution was to upgrade to the latest version of tagsets.excelxp from the sas website at http://support.sas.com/rnd/base/ods/odsmarkup/index.html

You may be experiencing a different problem, because you are outputing from a SAS reporting procedure with format commaXw.d
This might not be recognised by (even) the latest version of tagset.excelXP, by default. However, you can encourage it with excelxp options documented by running the statement
ods tagsets.excelxp file='x.xml' options( doc='all' ) ;
among all the documentation output is a section for NLS [pre]

Currency_format: Default Value 'Currency'
The currency format specified for excel to use.
Another possible value is 'Euro Currency'.
Will be deprecated in a future release when it is
no longer needed.

Decimal_separator: Default Value '.'
The character used for the decimal point.
Will be deprecated in a future release when it is no longer needed.

Thousands_separator: Default Value ','
The character used for indicating thousands in numeric values.
Used for removing those symbols from numerics so excel will like them.
Will be deprecated in a future release when it is no longer needed.[/pre]

notice the reference to "deprecated in a future release"

so you might want among your tagsets.excelXP options(
Thousands_separator='.'
Decimal_separator=','


a simpler approach works if you don't want the same proc print to write ODS to other destinations at the same time as tagsetx.excelXP. Just send simple number formats from sas with a tagattr='format style something like this custom excel number format
/ style={tagattr='format:#.##0,00'}

unfortunately I cannot test this as these thousand and decimal separators are not the way my regional variations work

good luck
peterC
Contributor
Posts: 26

Re: Export to Excel. Number fields in format COMMAXN.M become text field.

Hi Peter,
See your solution, I'll try to realize it.
Thanks!
Contributor
Posts: 26

Re: Export to Excel. Number fields in format COMMAXN.M become text field.

Hello,
So I tried to use
...
"tagattr='format:#.##0,00'"
...
But it didn't help...
But I accidentally found another solution - I send to export number value in format COMMAW.D, not COMMAXW.D as was before, and in such case numbers 1,234,123.45(COMMAW.D format) in excel become numbers in appropriate type and format(1.234.123,45).

So looks that such small issue has same small resolvingSmiley Happy

Thanks!
Respected Advisor
Posts: 3,777

Re: Export to Excel. Number fields in format COMMAXN.M become text field.

Did you try this TAGSET option?

[pre]
Numeric_Test_Format: Default Value '12.'
Used for determining if a value is numeric or not.
Other useful values might be COMMAX or NLNUM formats.
Will be deprecated in a future release when it is no longer needed.
[/pre]
Ask a Question
Discussion stats
  • 8 replies
  • 1942 views
  • 0 likes
  • 4 in conversation