Specifically Control Background Color of Merged Cells. ODS Excel and Proc Report

[djrisks]

Hello,

I have used ODS Excel and Proc Report to create an Excel File. And in the 1st image below, I would like columns A, B, C, D of row 1 to be green instead of blue. I would also like column E in row 1 to be blue. I am not sure how to control the colors of the individual columns in the first row. Please can you help? Ideally, I would like the 1st image to look like the 2nd image, which I created in Excel. Below also shows the code that I have used, and I have attached the dataset.

ods excel file="&export" options(flow="tables" sheet_name="QSORRES Mapping" absolute_column_width = "22, 23, 16, 60, 60, 13, 60, 65");
ods escapechar='~';
proc report data=input_data nowd spanrows wrap style(header)={textalign=left /*fontsize=10pt fontweight=bold*/ /*fontfamily="Albany AMT"*/} style(column)={textalign=left /*fontsize=10pt  *//*fontfamily="Albany AMT"*/};
     column ('~S={foreground=black}Raw'  FORMNAME_TEST FORMNAME_PRODUCTION ITEMNAME VALUE)  
            ('~S={foreground=black}SDTM' QSORRES QSSTRESN QSSTRESC Comments_Special_characters_);
     define FORMNAME_TEST / "FormName Test" display style(column)={vjust=c} style(header)={background=#E2EFDA foreground=black};
     define FORMNAME_PRODUCTION / "FormName Production" style(column)={vjust=c} style(header)={background=#E2EFDA foreground=black};
     define ITEMNAME / "ItemName" style(column)={vjust=c} style(header)={background=#E2EFDA foreground=black};
     define VALUE / "Value" style(column)={vjust=c tagattr='wraptext:yes' width=100%} style(header)={background=#E2EFDA foreground=black};

     define QSORRES / style(column)={vjust=c tagattr='wraptext:yes' width=100%} style(header)={background=D9E1F2} style(header)={background=#D9E1F2 foreground=black};
     define QSSTRESN / format=8. style(column)={vjust=c} style(header)={background=#D9E1F2 foreground=black};
     define QSSTRESC / style(column)={vjust=c tagattr='wraptext:yes' width=100%} style(header)={background=#D9E1F2 foreground=black};
     define Comments_Special_characters_/ "Comments (Special characters)" style(column)={vjust=c} style(header)={background=#D9E1F2 foreground=black};     
run;

ods excel close;

Many thanks,

Kriss 

[happy_sas_kitty · Accepted solution]

Would you like to try ODS RWI? It gives specific control over which cell should be displayed like what.

It's object orientated language but more like how we write HTML pages without a CSS file.

a sample code that can solve your problem looks like this:

( tweak the code for your dataset names etc., I'm using "work.test" )

To read more about this topic, you can google "ODS RWI" and read the Lex Jensen papers.

ods excel file="your_folder_location/test.xlsx" 
    options(flow="tables" sheet_name="QSORRES Mapping" absolute_column_width = "22, 23, 16, 60, 60, 13, 60, 65"
            frozen_headers='ON' autofilter="ALL");
ods escapechar='~';

data _null_;
    set work.test(obs=10) end=done;
    /* first two rows */
    if _N_ = 1 then do;
        declare odsout obj();
        obj.table_start();

        /* row 1 */
        obj.row_start(type:'H');
        obj.format_cell(
            data:'RAW',
            column_span: 4,
            just:'C',
            style_attr:'background=#E2EFDA');
        obj.format_cell(
            data:'SDTM',
            column_span: 4,
            just:'C',
            style_attr:'background=#D9E1F2');
        obj.row_end();

        /* row 2, headers */
        obj.row_start(type:'H');
            obj.format_cell(data:'FORMNAME_TEST', style_attr:'background=#E2EFDA');
            obj.format_cell(data:'FORMNAME_PRODUCTION',style_attr:'background=#E2EFDA');
            obj.format_cell(data:'ITEMNAME', style_attr:'background=#E2EFDA');
            obj.format_cell(data:'VALUE', style_attr:'background=#E2EFDA');
            obj.format_cell(data:'QSORRES', style_attr:'background=#D9E1F2');
            obj.format_cell(data:'QSSTRESN', style_attr:'background=#D9E1F2');
            obj.format_cell(data:'QSSTRESC', style_attr:'background=#D9E1F2');
            obj.format_cell(data:'COMMENTS_SPECIAL_CHARACTERS_', style_attr:'background=#D9E1F2');
        obj.row_end();

        obj.body_start();
    end;
    
    /* starts table body at data row 1, excel row 3 */
    obj.row_start();
        obj.format_cell(data:FORMNAME_TEST);
        obj.format_cell(data:FORMNAME_PRODUCTION);
        obj.format_cell(data:ITEMNAME);
        obj.format_cell(data:VALUE);
        obj.format_cell(data:QSORRES);
        obj.format_cell(data:QSSTRESN);
        obj.format_cell(data:QSSTRESC);
        obj.format_cell(data:COMMENTS_SPECIAL_CHARACTERS_);
    obj.row_end();

    if done then do;
        obj.body_end();
        obj.table_end();
    end;
run;


ods _all_ close;

Also note that, you need to specify your styles in each "format_cell" method.

Links below to help you quickly get started:

https://support.sas.com/rnd/base/ods/Tipsheet_RWI.pdf 

https://www.lexjansen.com/wuss/2019/210_Final_Paper_PDF.pdf

format_cell method link  https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsadvug/p12q0pykppxbz8n1200luexgbz4t.htm 

Hope this helps 🙂 

[djrisks]

Thank you @happy_sas_kitty , this helps 🙂

I just need to work on formatting the data cells now! 🙂

Thanks!