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.
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.
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
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.
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.
Use PROC EXPORT, and then format the column in Excel. It's just a few mouseclicks, and only needed for optics.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: