Custom Format not being used by ODS EXCEL

[PaigeMiller]

I find that sometimes using ODS EXCEL to produce reports, my custom format doesn't seem to work, and ODS EXCEL puts something else in the field. Why is this happening, and how can I fix it?

Example where it works as expected

proc format;
    value amountf low-10000='<=10K' 10000<-20000='10K-20K';
run;

data a;
    amount=7500; x=2; output;
    amount=16666; x=3; output;
    format amount amountf.;
run;

ods excel file="test.xlsx";
proc report data=a;
    columns amount x;
    define amount/group order=internal;
    define x/display;
run;
ods excel close;

this produces the following correct output in Excel

but what I really want is a format with dollar signs 

proc format;
    value amountf low-10000='<=$10K' 10000<-20000='$10K-$20K';
run;
ods excel file="test2.xlsx";
proc report data=a;
    columns amount x;
    define amount/group order=internal;
    define x/display;
run;
ods excel close;

and this is what I see in the output EXCEL file, note that my format with dollar signs seems to appear properly for <=$10K but not for $10K-$20K:

I also show the appearance of data set A in SAS viewtable, which does apply the format properly:

[A_Kh]

I've tried style options with tagattr=format attribute and it worked, but with a minor excel issue.  Excel file doesn't open automatically, instead it shows a notification 'We found an issue.. Do you want us to recover the file.." and when clicked 'Yes' the issue is resolved and the file opens with proper format. 

This might give your some direction how to resolve the issue.

ods excel file="test.xlsx";
proc report data=a;
    columns amount x;
    define amount/group style(column)={tagattr='format:amountf.'} order=internal;
    define x/display;
run;
ods excel close;

excel.

result.

[Chevell_sas]

This appears to be a defect. The first character of "$" in the format is causing an issue where the values are passed through rather than applying the formatted value.  I will get a defect entered on this problem so we can get this fixed. Feel free to open a case with Technical Support and I will have more details after this is fully debugged.

[A_Kh · Accepted solution]

Actually, I found a paper which explains how to export formats properly into Excel. When using tagattr='format:text' style option it will export all special characters as intended.  So, the below code will create Excel file without issue:

ods excel file="test.xlsx";
proc report data=a;
    columns amount x;
    define amount/group style(column)={tagattr='format:text'} order=internal;
    define x/display;
run;
ods excel close;

[PaigeMiller]

Thanks to both @A_Kh and @Chevell_sas 

[PaigeMiller]

The solution here from @A_Kh also solves this problem: https://communities.sas.com/t5/ODS-and-Base-Reporting/Picture-Format-not-working-in-ODS-EXCEL/m-p/67... unfortunately that thread is locked or I would add it there.

@ChrisHemedinger is there any chance this solution can be added to the older thread mentioned above?

[ChrisHemedinger]

I added a link from that older topic back to here for those who need it.