ODS tagesets.excelxp

[deleted_user]

Hi,

I'm creating *.xml reports using the ODS tagsets.ExcelXP.

I want the numbers to be formatted in 12,345.
However, the number format is not working....all the numbers in *xml are look like 12345.

Example:

PROC TABULATE DATA=TEST ;
CLASS STATE;
VAR RESP;
TABLE STATE ALL,
RESP*SUM=' '*F=COMMA10./RTS=20 MISSTEXT='0' PRINTMISS;
RUN ;

Any suggestions?

Thanks.

[Cynthia_sas]

Hi:
As you have discovered, when you use ODS to create files for Excel to open, Excel generally ignores most of the SAS formats for columns that are numbers. If you want to have Excel format the numbers, such as you show (with a comma between the thousands), you must send a Microsoft format from ODS to Excel.

The method by which you send a Microsoft format is through the use of STYLE= overrides, specifically, for TAGSETS.EXCELXP, the TAGATTR style attribute override. There are many previous forum postings on the use of TAGATTR and this one shows both TAGATTR and has a sample program that shows how the various forms of style overrides in PROC TABULATE will impact which cells (header or calculated data):
http://support.sas.com/forums/thread.jspa?messageID=36690轒

A search of support.sas.com or the forum search should get you more results on the use of TAGATTR with TAGSETS.EXCELXP.

cynthia