ODS Excel Proc Report - Spanned Header Background Colour

[Jamie_H]

I have a proc report outputting through ODS Excel in SAS 9.4.  The report has three across columns and I am trying to have a different background colour for the 3 header rows beneath each of the 3 columns.  I am able to set the background colour for Row 1 with a "style(header)=" statement under the proc report.  And I am also able to change the background colour for the 3rd row by using a "style(header)=" statement within the define statement.

i am struggling however to change the background colour of the cell for the spanned header that is named within the ( ) of the Column statement.   Some things i've seen online suggest that I can use ods escapechar='^'; and then a ^S={background=lightyellow} within the column statement, but that doesn't seem to work. 

Does anyone have any suggestions?   here is the code in full:

proc format;

value $color

"&master_org_name." = '#EDF2F9'

"&comp1_report_title." = '#D9D9D9'

"All Other &Comparison_group2." = '#F5CFE3'

other = '#EDF2F9';

run;

ods escapechar='^';

ods excel file= "temp" options(

frozen_headers="3"

frozen_rowheaders="1"

sheet_name = "Quotes"

absolute_column_width='5.43,4.57,9.71,4.57,9.71,4.57,9.71,3.71,8.86,3.71,8.86,6.14,6.14,4,7,

4.57,9.71,4.57,9.71,4.57,9.71,3.71,8.86,3.71,8.86,6.14,6.14,4,7,

4.57,9.71,4.57,9.71,4.57,9.71,3.71,8.86,3.71,8.86,6.14,6.14,4,7'

);

PROC REPORT DATA=WORK.PCT LS=164 PS=48 SPLIT="/" NOCENTER HEADLINE

style(header)={font_face="Calibri" font_size=9pt background = $color. }

style(column)={font_face="Calibri" font_size=8pt background=white}

/*style(summary)={background=$color.}*/;

options missing = ' ';

COLUMN /*APPYRMNTH*/ App_date

("&master_org_name."

("Quotes" MasterDash_AllQuote Masterallquote_val)

("Score Eligible" MASTERDash_ScoreEligible_Quote MasterScore_eligible_val)

("Shawbrook Accept" MasterDash_InitialAgreeRef MasterinitialAgreeRef_Val)

("Applications" MASTERDASH_APP MASTERAPP_VAL)

("Cust. Accept" MASTERDASH_ACCEPT MASTERACCEPT_VAL)

("App 2 Accept Rate" MastVolPct MastValPct)

("Pipeline" MasterPipeline MasterPipeline_val)

)

/*(%tslit(All Other &Comparison_group1.) */

(%tslit(&comp1_report_title. )

('Quotes' Comp1_Dash_AllQuote Comp1_allquote_val )

('^S={background=lightyellow}Score Eligible ' Comp1_Dash_ScoreEligible_Quote Comp1_Score_eligible_val )

('^S={background=lightyellow}Shawbrook Accept ' Comp1_Dash_InitialAgreeRef Comp1_initialAgreeRef_Val )

("Applications" Comp1_DASH_APP Comp1_APP_VAL)

("Cust. Accept" Comp1_DASH_ACCEPT Comp1_ACCEPT_VAL)

("App 2 Accept Rate" Comp1_VolPct Comp1_ValPct)

("Pipeline" Comp1_Pipeline Comp1_Pipeline_val)

)

(%tslit(All Other &Comparison_group2.)

("Quotes" Comp2_Dash_AllQuote Comp2_allquote_val)

("Score Eligible" Comp2_Dash_ScoreEligible_Quote Comp2_Score_eligible_val)

("Shawbrook Accept" Comp2_Dash_InitialAgreeRef Comp2_initialAgreeRef_Val)

("Applications" Comp2_DASH_APP Comp2_APP_VAL)

("Cust. Accept" Comp2_DASH_ACCEPT Comp2_ACCEPT_VAL)

("App 2 Accept Rate" Comp2_VolPct Comp2_ValPct)

("Pipeline" Comp2_Pipeline Comp2_Pipeline_val)

)

;

DEFINE /*APPYRMNTH*/ App_date / group display WIDTH=5 SPACING=2 RIGHT "Month" order = data format=monyy5.;

DEFINE MasterDash_AllQuote / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header )={background=lightyellow};

DEFINE Masterallquote_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow}/*style(column)={background=lightyellow}*/;

DEFINE MASTERDash_ScoreEligible_Quote / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow};

DEFINE MasterScore_eligible_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow};

DEFINE MasterDash_InitialAgreeRef / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow};

DEFINE MasterinitialAgreeRef_Val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow};

DEFINE MASTERDASH_APP / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow};

DEFINE MASTERAPP_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow};

DEFINE MASTERDASH_ACCEPT / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow};

DEFINE MASTERACCEPT_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow};

DEFINE MastVolPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow};

DEFINE MastValPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow};

DEFINE MASTERPipeline / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow};

DEFINE MASTERPipeline_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow};

　

DEFINE Comp1_Dash_AllQuote / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp1_allquote_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp1_Dash_ScoreEligible_Quote / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp1_Score_eligible_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp1_Dash_InitialAgreeRef / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp1_initialAgreeRef_Val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp1_DASH_APP / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp1_APP_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp1_DASH_ACCEPT / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp1_ACCEPT_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp1_VolPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Vol" ;

DEFINE Comp1_ValPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp1_Pipeline / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp1_Pipeline_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

　

DEFINE Comp2_Dash_AllQuote / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp2_allquote_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp2_Dash_ScoreEligible_Quote / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp2_Score_eligible_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp2_Dash_InitialAgreeRef / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp2_initialAgreeRef_Val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp2_DASH_APP / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp2_APP_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp2_DASH_ACCEPT / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp2_ACCEPT_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp2_VolPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Vol" ;

DEFINE Comp2_ValPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Val" ;

DEFINE Comp2_Pipeline / display FORMAT=5. WIDTH=6 RIGHT "Vol" ;

DEFINE Comp2_Pipeline_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ;

RUN;quit;

[Cynthia_sas]

Hi:
Hard to run your code without any data. Have you tried anything from this paper: http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf discussion of headers starts on page 11.

When I wrote the paper, ODS EXCEL was not available, so I haven't tested Output 14 over there, but I would expect it to work.

cynthia

cynthia

[Jamie_H]

Hi Cynthia,

Thanks for the response, but i'm really struggling how to utilise how to use fake variables on this.  I'm just not getting the concept from your paper.  Do you have any more information on why you need to do this, and what SAS is doing in the background to make this work?

[Cynthia_sas]

Hi:

  Here are some screen shots that might explain what I mean. I see you are trying the format technique for spanning header changes. I wrote my paper BEFORE the format technique for spanning headers worked.And I have stuck with this older method because it is generally reliable for me, while there have been times when the format method was acting wonky.

  Here's an example of 3 outputs and the data that was used.

Notice how the DATA step creates 4 new variables. Their values will be the same for EVERY row in the data. I named the variables for the column they will be used with. I show HTML and ODS EXCEL output in the screen shot. By default, with this technique there are 3 header rows at the top of the table.

In output #1, everything is showing in the header area, the values for the ACROSS and the values for the variables under them.

Then, in output #2, I change the colors for 4 of the 5 columns, but still show everything on 3 header rows. In output #3, the name of each "helper" variable (agex, sexx, htx and wtx) are suppressed on the DEFINE statement, reducing the header rows to 2 rows. Finally, in REPORT #3, the NAME cell and the cell above it are changed to very light purple using a style override on the PROC REPORT statement.

Here's the code that created the above output (using the data created with the "helper" variables).

Next, a modification of that techniques is used for spanning 2 columns. Instead of 4 helper variables, one for each column, now there are 2 helper variables:

So, the above example can be extended to more columns -- I generally use it when folks want to have "sets" of columns highlighted in a certain color in the header area. You might be able to make the format technique work -- if you are having issues with your code, you might have to simplify the code and the macro so you can open a track with Tech Support.

  What I know is that that this method that I call the "fake across" method has worked from 9.1, through 9.2, 9.3 and 9.4. I hope this simpler set of examples clarifies what I was trying to explain in the paper.

 cynthia