BookmarkSubscribeRSS Feed
Yuriy2301
Calcite | Level 5
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 up:).

Thank you in advanced:)!
8 REPLIES 8
Cynthia_sas
SAS Super FREQ
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
Yuriy2301
Calcite | Level 5
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 lot:)!
Cynthia_sas
SAS Super FREQ
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
Peter_C
Rhodochrosite | Level 12
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
Yuriy2301
Calcite | Level 5
Hi Peter,
See your solution, I'll try to realize it.
Thanks!
Yuriy2301
Calcite | Level 5
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 resolving:)

Thanks!
data_null__
Jade | Level 19
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]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3811 views
  • 0 likes
  • 4 in conversation