Format column variable to numeric

[kumarsandip975]

Dear Community,

I have data inputs, volume1 column is numeric. Sharing print and contents results. 

Expected output I am looking , convert volume column with 9 decimal places and convert .(dot) with ,(comma).

I managed to fix it with below piece , like making 9 decimal places and also convert .(dot) with ,(comma) but problem it is coming with character, and I know that is obvious. 

My question is - Is there any possibilities to have the values(format) with numeric, because it needs to export into xlsx, and keeping character column, xlsx does have calculation for character values. 

data output1;
set input1;
volume = put(volume1,20.9);
volume=tranwrd(volume,'.',',');
run;

Xlsx, does not shows sum , because of character values. 

[Tom]

I cannot understand what you mean. 

Why did you change the values to text if you wanted them as numbers?

Do you want to PRINT the values using COMMA as the decimal place character?   Why would it matter what character you use to display the decimal point when all of the values are INTEGERS?

The bottom of the post seems to be talking about EXCEL files.

Are you asking how to have convert your SAS dataset into an EXCEL file?  What methods did you try?

Are you asking how to tell EXCEL to display the numbers using COMMA as the decimal place?  I suspect that is normally handled by the country settings of the user that is viewing the EXCEL file, but perhaps not.

Anyway once you know what EXCEL formatting you want for the cells in the spreadsheet you can use the TAGATTR style options.

https://www.google.com/search?q=%40sas.com+TAGATTR+EXCEL+format

[kumarsandip975]

See, original number is coming as numeric, for example
12.000
1234.2345
9999
1
123456.0000000000000
It means I need to first convert with 9 decimal places,
12.000000000
1234.234500000
9999.000000000
1.000000000

why I am making as text, because business is looking the same output (as 9 decimal places), but just replace decimal(.) to comma(,), because of European standard.

Since, I already made it as text(9 decimal places with comma), if I export same in excel, and when business select to see sum from execl, they see , ahhh sum is not coming.

So, ultimately, I need 9 decimal with comma in excel so that business people can select and see the summation.

[Kurt_Bremser]

As long as values are numeric, the display format (number of decimal places) is totally irrelevant for summation or other calculations.

data test;
input x;
format x commax20.9;
datalines;
12.000
1234.2345
9999
1
;

proc export
  data=test
  file="~/test.xlsx"
  dbms=xlsx
  replace
;
run;

ods excel file="~/test_ods.xlsx";

proc print data=test noobs;
run;

ods excel close;

In both Excel files, the numbers are numeric, and in the ODS result you already have the 9 decimals.

if the thousand-separator dot bothers you, roll your own format with PROC FORMAT.

[kumarsandip975 · Accepted solution]

Thanks, I tried regarding ods option, When I am creating execl with ods/proc print. It's taking lot of time to run the program . Looks like it tries to print the values as well and taking long time. The output format looks good with ods . Any suggestions to reduce the execution time. 

[Kurt_Bremser]

Use PROC EXPORT, and then format the column in Excel. It's just a few mouseclicks, and only needed for optics.

[Reeza]

COMMAX (note the X) is the European format for data. So apply the commax format to the NUMERIC variable with the appropriate decimal places needed.

This should be honoured in the export, but it may also depend on how you export the data. You do not need any conversion steps, only a FORMAT statement.

[Tom]

Is the goal to provide the users with a REPORT?

If so then perhaps you should just use the NLNUM format as it will adjust its behavior based on the LOCALE setting in SAS.

Example:

data have;
  input value @@ ;
cards;
12.000 1234.2345 9999 1 123456.0000000000000
;
options locale='EN_US';
proc print;
  title "LOCALE=%sysfunc(getoption(locale))";
  format value nlnum11.3;
run;

options locale='IT_IT';
proc print;
  title "LOCALE=%sysfunc(getoption(locale))";
  format value nlnum11.3;
run;

Result

LOCALE=EN_US   

OBS          value

 1          12.000
 2       1,234.235
 3       9,999.000
 4           1.000
 5     123,456.000

LOCALE=IT_IT       

OBS          value

 1          12,000
 2       1.234,235
 3       9.999,000
 4           1,000
 5     123.456,000

If the goal is to make an EXCEL file and have the EXCEL file apply a similar display style to the cells that the values end up in then you will have to use ODS to generate the XSLX file so that you can attach the display format.

I am not an EXCEL expert, but when I try to format a cell to have numbers like that in EXCEL it uses a custom format string like ###,##0.000 in my US English language EXCEL version.  So to attach that display format to the cells you could use something like:

ods excel file='c:\downloads\en_us.xlsx';
ods select none;
ods excel select print;
proc print noobs;
  var value / style={tagattr='###,##0.000'} ;
  format value 10.3 ;
run;
ods excel close;
ods select all;

Result

You can do your own EXCEL research to find what you need to specify for the TAGATTR value to force EXCEL to use comma for decimal place instead of period. 

[Astounding]

There's an easy way to get a result similar to what you ask. Perhaps the extra periods won't bother you. Try it and see. When printing the data, add:
format volume1 commax21.9;
Not knowing more about your data, I had to guess that the width of 21 would be a good choice.