Re: how to apply the format when export data into excel format is missing

Your posted data doesn't make sense to me. You say that these are the values for SALARY: 1,52,133 or 2,50,111 -- but why do you have commas in inappropriate places? Usually the commas stand as the THOUSANDS separator -- this is what COMMA31 will give you. So if you want 1,52,133, you will NOT get that from COMMA32 -- what you would get for 152133 is 152,133 and what you would get for 1234567 is 1,234,567; and what you would get for 123456789 is 123,456,789; and what you would get for 987654321123456789 is 987,654,321,123,456,789 so your representation of the data doesn't make sense. Second, even through EMPNO is fake, do you really have non-unique EMPNO values? Is this pre-summarized data? Do you have one ROW with a value AAA and then a second row with EXACTLY the same value of AAA? That doesn't make sense either.

Finally, what PROC REPORT code have you tried? What is your destination of interest? How are you making your file for Excel? Excel generally ignores any SAS formats that you send to it -- it uses a default column width and default "General" format for numbers. Excel doesn't like numbers stored as text and so without seeing some code, including your ODS statements, it is hard to comment in a constructive way.

Re: how to apply the format when export data into excel format is missing

The user-defined format will work for other ODS destinations, like RTF, PDF and HTML, but will rarely work for Excel. Instead of making a character variable or padding with a tab character, the OP could use CALL DEFINE. But without knowing what destination is being used to get the output into Excel -- could be CSV, could be HTML, could be XML -- there's no way to show the proper CALL DEFINE code. It would be useful for the OP to post some sample code and explain the anomalies in the comma format for the numbers that were posted.

Re: how to apply the format when export data into excel format is missing

As your using ods tagsets.excelxp then you will want a style with tagattr options:

proc report...;

define your_column / style={tagattr="format:#,##0.00"};

...

However looking through your text, it seems you want different formats on different cells. I don't think you can do this directly. SAS is a structured labguage, i.e. a column only has one format applied to all values. SAS != Excel. You could post process the data, or convert the numeric to text in the format you want. Then put the tagattr as text to avoid Excel converting it to number again.

Re: how to apply the format when export data into excel format is missing

But in your original posting, you show TWO variables: EMPNO and SALARY. Yet, in your PROC REPORT code, you do not show any COLUMN statement or show where you use the format you've created. You describe the data as having "Jan-15", but in your original data, you did not have a DATE variable. Can you post some sample data and ALL of your PROC REPORT code that shows how you are using the format. Here is a simple example using SASHELP.CLASS -- I know this is not structured like your data, but since you have described the data differently in several postings, it is impossible to understand what your data looks like. But this REPORT examples does show the only way to illustrate using different formats with TAGSETS.EXCELXP.

Re: how to apply the format when export data into excel format is missing

And, as I indicated in a previous posting: "The user-defined format will work for other ODS destinations, like RTF, PDF and HTML, but will rarely work for Excel." So the CALL DEFINE method is the one you will have to use to get Excel to respect any of your formats. I just included the formats for comma8.1, comma8.2 and comma8.3 to illustrate how you would change them. You could certainly make them comma32. instead.