topic Re: Unable to get comma seperated values in proc report after every 3 digits in excel in SAS Programming

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

santhosh — Tue, 20 Feb 2018 05:17:22 GMT

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

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

Tom — Tue, 20 Feb 2018 20:07:42 GMT

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.

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

santhosh — Wed, 21 Feb 2018 01:14:03 GMT

Hello Tom,

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

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

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

Haikuo — Wed, 21 Feb 2018 21:20:20 GMT

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;

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

Tom — Wed, 21 Feb 2018 21:41:06 GMT

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.

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

SuryaKiran — Wed, 21 Feb 2018 21:52:51 GMT

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®