topic Re: decimal separator in ods csv in 9.2.2 in ODS and Base Reporting

decimal separator in ods csv in 9.2.2

Andre — Fri, 11 Jun 2010 13:04:16 GMT

In follow of this answer
[pre]
http://support.sas.com/forums/thread.jspa?messageID=38213镅
[/pre]
i confirm that delimiter=';' option in well working under 9.2.2 even under a foreign language
system options regulation.
BUT
what about the decimal_separator defined as comma

[pre]
data a ;
a=213.978; b="Nullité";
run;
ods csv file='d:\tabsas\csvtest1.csv'
options( delimiter=";" decimal_separator=',');
proc print data=a;
run;
ods csv close;
[/pre]
the result from an ordinary text editor is

[pre]

"Obs";"a";"b"
"1";213.978;"Nullité"
[/pre]

Did i forgot something?
The tagsets.csv is from origin of version 9.2.2

Andre

Message was edited by: Andre

Message was edited by: Andre

Message was edited by: Andre Message was edited by: Andre

Re: decimal separator in ods csv in 9.2.2

Cynthia_sas — Fri, 11 Jun 2010 14:02:06 GMT

Hi:
You might want to open a track with Tech Support. I can duplicate the behavior, but I note in the SAS log, when I use doc='Help' that it says:
[pre]
Decimal_separator: Default Value '.'
The character used for the decimal point.
Will be deprecated in a future release when it is no longer needed.
[/pre]

And I'm not sure what that note about the possible deprecation means. This would be what you'd want to check with Tech Support. If there's a workaround for the behavior, they will know what it is. (Other than writing your own DATA step program.)

cynthia

Re: decimal separator in ods csv in 9.2.2

Peter_C — Mon, 14 Jun 2010 13:21:09 GMT

> what about the decimal_separator defined as comma

> options( delimiter=";" decimal_separator=',');

this option helps the tagsets code recognise a decimal separator in the data it reads (from procedure output). It is not about passing the value to the "output" of the tagset.
Achieve control of tagset output with the preferred SAS format for adapting the thousand or decimal separators, i.e. numXw.d or commaXw.d or even nlNumw.d

Although there are (in SAS913) formats bestXw. and nlNumW. neither provide the objective of an equivalent of the way format bestW. provides only the relevant decimal fraction.
Adapting Andre's example to demonstrate these formats:
data a ;
a=213.978; b="Nullité"; c= 567.891 ; d= c; e=c ;
label a='a numX20.4' b='string B' c='c nlNum20.4' d='d best.' e='e bestX.' ;
run;
filename andr 'csvtest1.csv.txt' lrecl=10000 ;
option ls=95;* dfLang= French ;
ods csv file= andr
options( delimiter=";" decimal_separator=',');
proc print data=a width= full label split='=' ;
format a numX20.4 c nlNum20.4 d best. e bestX.;
run;
ods csv close;
proc fslist file= andr ;
run;
[/pre] the results on my english platform with DFlang=French is[pre]+FSLIST: C:\Applications\UK.Liquidity2\code\csvtest1.csv.txt-------+
|"Obs","a numX20.4","string B","c nlNum20.4","d best.","e bestX." |
|"1","213,9780","Nullité","567.8910","567.891","568" |
| |
| *** END OF FILE *** |
+------------------------------------------------------------------+[/pre]
However, when I run this code after running the CSV tagsets update at http://support.sas.com/rnd/base/ods/odsmarkup/csvtags.tpl I get the semicolon delimiters, but lose the first comma in the data !!!
[PRE]+FSLIST: C:\Applications\UK.Liquidity2\code\csvtest1.csv.txt-------+
|"Obs";"a numX20.4";"string B";"c nlNum20.4";"d best.";"e bestX." |
|"1";2139780;"Nullité";"567.8910";"567.891";568 |
| |
| *** END OF FILE *** |
+------------------------------------------------------------------+[/PRE]

Re: decimal separator in ods csv in 9.2.2

Andre — Mon, 14 Jun 2010 14:09:15 GMT

Thanks for testing, Peter

You test give me this results under french sas version 9.2.2 with native tpl
[pre]

"Obs";"a numX20.4";"string B";"c nlNum20.4";"d best.";"e bestX."
"1";2139780;"Nullité";5678910;"567.891";568

[/pre]

nlNum20.4 format provoke another transformation

I find this disturbing as the delimiter option has explicit consequence into the txt file
as requested but the other "deprecated" ? is painfull in its revival

So in practise,
1) i shall report the technical support answer here when received
and
2) of course make the correct adjustement to the dot=decimal regulation
in the excel2007 BEFORE opening the csv file
as it is more difficult to check which variables need which kind of numx formats.

My last question is why in a nls version has this nlNum the effect of suppression the
comma?

Andre

Re: decimal separator in ods csv in 9.2.2

Andre — Thu, 17 Jun 2010 14:48:19 GMT

The answer of the developper is about this parameter

"Used for detection of currency formats and for
removing those symbols so excel will like them.
Will be deprecated in a future release when it is
no longer needed."

My question was not about a currency value!

Anyway, the sas programmer must apply before a format
as Peter C indicates it previously.

Andre

Re: decimal separator in ods csv in 9.2.2

SR_FR — Thu, 30 Dec 2010 14:44:54 GMT

a little "up" on this problem

imposing a format is possible when using PROC PRINT but it's of no use when you're using for instance PROC MEANS

So?

Re: decimal separator in ods csv in 9.2.2

Cynthia_sas — Thu, 30 Dec 2010 15:42:40 GMT

Hi,
You can alter the format used for PROC MEANS results by changing the table template for BASE.SUMMARY template.

Or, probably easier, you can create an output dataset from PROC MEANS using the OUTPUT statement or ODS OUTPUT and then use your format of choice on the resulting dataset -- which you can then use with PROC PRINT.

cynthia

Re: decimal separator in ods csv in 9.2.2

SR_FR — Thu, 30 Dec 2010 17:29:55 GMT

hi,

thanks for your answer

by modifying the BASE.SUMMARY template, you mean modifying the use_format_defaults; instruction by something like "use_format_user" that could allow a FORMAT instruction inside the PROC MEANS that could modify the way data are represented ?

Or inserting something inside say :

define mean;
header = "Moyenne";
generic;
end;

but
[mode JOKE ON]
what about a system option (NLS ?) to set ',' as the decimal separator for your poor French users ?
[mode JOKE OFF]

Re: decimal separator in ods csv in 9.2.2

Cynthia_sas — Thu, 30 Dec 2010 23:00:15 GMT

Hi:
You're partly right. Although the TABLE template syntax does have USE_FORMAT_DEFAULTS, as described in the documentation:
http://support.sas.com/documentation/cdl/en/odsug/61723/HTML/default/a001023977.htm

you can see that there is no "USE_FORMAT_USER" as you suggest.

However, the way to modify BASE.SUMMARY would be do add a FORMAT statement (for example) to the DEFINE block for the MEAN statistic (and any other column/statistic you wanted to alter):
[pre]
define mean;
header = "Moyenne";
generic;
format=commax12.2;
end;
[/pre]

I do not know why there is not a global system option for changing decimal separators. I suspect, but it's only a guess, that the use of SAS-defined formats, picture format masks and user-defined formats was considered a robust way to alter the display of variable values. After all, -if- there was a global option, such as you suggest, then EVERY SAS-defined format and possibly EVERY user-defined format might be impacted, in an adverse way, by the global option.

At the very least, implementing an option like this at the global level would impact ALL the numeric values the same way. So in a report that displayed US dollars and currency that needed a comma for the decimal separator, how would you now get the US dollars to have the period as a separator once your (desired) global option went into effect??

cynthia

Re: decimal separator in ods csv in 9.2.2

SR_FR — Fri, 31 Dec 2010 09:14:22 GMT

Hi

and thanks again for your answer

the global option won't be that usefull since the only problem we (French users) have is when we export our results out of SAS in a CSV format. A real "decimal_separator" option could be usefull here. We have ways to fix that but it's inside Excel by modifying Excel options.

Now, EXCELXP tagset is doing a perfect job so...

best regards (and a happy new year)

Sébastien

PS : the DEFINE TABLE syntax is not having a USE_FORMATS_DEFAULTS instruction but it is in the table attributes

Re: decimal separator in ods csv in 9.2.2

SR_FR — Fri, 31 Dec 2010 13:39:05 GMT

update...

EXCELXP tagset used to do a perfect job - not anymore

I've just update my EXCELXP tagset (I was using the 1.94 version)

with the 1.94 version, in the output sent to Excel, the comma was the decimal separator in my (French) Excel.
Not anymore with 1.116 version.

(and the DECIMAL_SEPARATOR option is not functioning in this case too)

If it was possible with the 1.94, it certainly could be possible with the 1.117

no?

best regards

Sébastien

Re: decimal separator in ods csv in 9.2.2

Cynthia_sas — Fri, 31 Dec 2010 17:56:02 GMT

Hi:
I'll have to let someone else comment on the ExcelXP issue that you found (or you may want to open a track with Tech Support on this).

At for the USE_FORMATS_DEFAULT being a table attribute...yes, it is a TABLE attribute, which is specified inside the DEFINE TABLE/END; block (shown here with UNDERLINE, OVERLINE and DOUBLE_SPACE table attributes):

[pre]
proc template;
define table mycustomtable;
column one two three four;

use_formats_default=on; <-- these are all table attributes,
double_space=on; specified in the TABLE template DEFINE block.
overline=on;
underline=on;

define column one;
generic;
header = 'One';
.... more code ;
end;
....... more code;
end;
run;
[/pre]

So when I said that "TABLE template syntax does have USE_FORMAT_DEFAULTS", I did not mean to imply that the attribute actually went on the DEFINE TABLE statement. The table attributes that you can specify for a particular table template are listed in the DEFINE TABLE section of the PROC TEMPLATE syntax documentation.

Since, the table attribute is specified within the DEFINE TABLE/END block of code, I think that a more precise statement would have been: "In general, TABLE template syntax does allow the USE_FORMAT_DEFAULTS table attribute to be specified within the DEFINE TABLE/END block (as shown in the documentation); however there is no "USE_FORMAT_USER" table attribute as you suggest.

cynthia

Re: decimal separator in ods csv in 9.2.2

SR_FR — Sat, 01 Jan 2011 16:36:04 GMT

thanks again for your precise reply

let see now what tech support will tell me about the ExcelXP issue...

best regards

Sébastien