topic Re: Custom Format not being used by ODS EXCEL in ODS and Base Reporting

Custom Format not being used by ODS EXCEL

PaigeMiller — Tue, 28 Nov 2023 13:13:22 GMT

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:

Re: Custom Format not being used by ODS EXCEL

A_Kh — Tue, 28 Nov 2023 14:08:31 GMT

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.

Re: Custom Format not being used by ODS EXCEL

Chevell_sas — Tue, 28 Nov 2023 14:19:52 GMT

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.

Re: Custom Format not being used by ODS EXCEL

A_Kh — Tue, 28 Nov 2023 14:25:55 GMT

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;

Re: Custom Format not being used by ODS EXCEL

PaigeMiller — Tue, 28 Nov 2023 14:26:12 GMT

Thanks to both @A_Kh and @Chevell_sas

Re: Custom Format not being used by ODS EXCEL

PaigeMiller — Tue, 28 Nov 2023 15:03:13 GMT

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/678103 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?

Re: Custom Format not being used by ODS EXCEL

ChrisHemedinger — Tue, 28 Nov 2023 15:00:14 GMT

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