ods excel SAS 9.4 proc report format=commax12.2

[Eva]

Dear all,

I have a proc report in an html output. Now I'd like to do the same in ods excel. There I have a problem with the format commax12.2 in a define statement of a proc report:

ods excel file="C:\test.xlsx";

proc report data=work.mydata;
  columns one two;
  define one / group;
  define two / analysis sum format=commax12.2;
run;

ods excel close;

The programm runs fine if I leave out the format.

The whole program also runs fine if I run it in ods html.

Can anybody tell which format to use under ods excel?

Best wishes

Eva

[RW9]

Not sure about ods excel, but in ods tagsets.excelxp you would do:

ods excel file="C:\test.xlsx";

proc report data=work.mydata;
  columns one two;
  define one / group;
  define two / analysis sum format=commax12.2 style={tagattr="format:###,###,###"};
run;

ods excel close;

[Eva]

Dear RW9,

unfortunately not.

I previously used ods tagsets.msoffice2k and then format=best12.2 would work. But also not in ods excel.

Best wishes

Eva

[Reeza]

TAGATTR should work in ODS Excel. 

Are you saying that's not working? If not, please post your code. 

 Edit: I have vague recollections of having to apply BOTH the SAS format and the TAGATTR statement to get the format to work correctly in ODS Excel. 

[Tom]

So this code works fine.

ods excel file="C:\downloads\test_format.xlsx";

proc report data=sashelp.cars;
  columns make weight;
  define make / group;
  define weight / analysis sum format=comma12.2  style={tagattr="format:#,##0.00"};
run;

ods excel close;

To produce normal US formatting of numbers with periods for decimal place and commas for thousands separators.

Are you asking what is the Excel format to put into the TAGATTR style to get Excel to display the numbers using comma as decimal point and period as thousands separator?  Perhaps if you try formatting a cell in Excel and look and see what format Excel attaches to the cell?

If you are not also sending the report to other ODS destinations then you can change the FORMAT used to print the value from COMMA12.2 to something else.  For example you could use 32.2 instead if you are worried that 12 characters counting periods and commas and decimal digits is going to be enough for the size of your possible values.

[Eva]

Dear all,

thanks for your answers. With them I was now able to code the right format. This is it (just the define statement):

define two / analysis sum format=commax12.2 style={tagattr="format:###,###.##"};

I did indeed forget the format=commax12.2 with Excel. It is really needed.

And I chenged your suggestion for the tagattr so that I get the German format commax12.2

Best wishes

Eva

[RW9]

So, exactly what both myself and @Tom posted, but you mark your own post as answer.

[Tom]

Can you explain in more detail what you mean about the FORMAT= option being needed with Excel?

Is the FORMAT=commaX12.2 needed to insure that Excel reads the numbers properly? If you use FORMAT=comma12.2 does Excel interpret the numbers as character strings? What about if you just use FORMAT=12.2 instead? Does Excel read the numbers properly?  What if you used the FORMAT=numX12.2?

Or is FORMAT=commaX12.2 needed so that your OTHER active ODS destinations are displaying the formatted numbers as you want?

What about the Excel format that you are using.  Do you need to use style={tagattr="format:###,###.##"} or do you get the same results if you just use style={tagattr="format:#,###.##"}?  And does it display the decimal places and thousand separators properly in Excel using this format?  That is does Excel know you want to see 1.234,56 even though the format style is written as #,###.##?