Unable to get comma seperated values in proc report after every 3 digits in excel

[santhosh]

Hello all,

I am using LibreOffice Calc for  excel output

unable to get comma separated by 3 digits default it is taking 12,34,678.00

can I get values as 12,345,678.00

when ever I am applying any formats or picture formats or convert into character its not working

either we are getting 12,34,678.00  or 1234678.00 

[Tom]

Why are you posting a LIbreOffice question on a SAS Forum?  Does this have anything to do with SAS? If so then please show the SAS code you used.

[santhosh]

Hello Tom,

We have  LIbreOffice instead of excel we are writing program in sas for output

eg 

data test;

input id $ amount;

datalines;

101 123456789

102 987456123

103 456789123

;

run;

ods excelxp tagsets file='path/test.xls';

proc report data=test nowd;

columns id amount;

define id / 'EMP ID';

define amount / 'Amount' format=comma20.2;

run;

i am getting values in output as default in excel / LIbreOffice 2 digits seperated

101 12,34,56,789

102 98,74,56,123

103 45,67,89,123

i want output as complete 3 digit numeric seperaot

101 123,456,789

102 987,456,123

103 456,789,123

[Haikuo]

ODS tagsets.excelxp became obsolete after ODS EXCEL . ODS EXCEL handles SAS format a lot better, if you are running SAS 9.4, then the following code should meet your need:

ods excel file='path/test.xlsx';

proc report data=test nowd;

columns id amount;

define id / 'EMP ID';

define amount / 'Amount' format=comma20.2;

run;

[Tom]

The SAS format you use will control how the output looks in normal destinations like listing or html.  But if you want to tell Excel how to format the values then you need to use the ODS STYLE option to pass in the Excel format attribute to attach to the cells in the Excel document.  Something like this:

style={tagattr='format:#,##0'}

You might also just check your country code setting for your machine. I suspect that LibreOffice thinks you are in India and so is defaulting to use commas every two digits instead of every three digits.

[SuryaKiran]

This is a common requirement when creating Excel files from SAS. Take a look at TAGATTR which gives more control.

ODS tagsets.ExcelXP file='path/test.xlsx';
proc report data=test nowd;
columns id amount;
define id / 'EMP ID';
define amount / 'Amount'  style(Column)={tagattr='format:#,##0.00'};
run;
ODS tagsets.ExcelXP CLOSE;

Check out these useful papers for more control over data in Excel.

Don’t Gamble with Your Output: How to Use Microsoft Formats with ODS

Maintaining Formats when Exporting Data from SAS® into Microsoft® Excel®