I have a customer requirement to create a spreadsheet with the header rows color-coded and grouped. I need to have 2 header rows. I also must have a multi-sheet output, so using the excelxp tagsets destination is the only way I know to go. I am using SAS 9.3 (TS1M2). Tagsets v1.130, 05/01/2011 Here is a sample of what is wanted. There are more columns/groups, but this gives the idea; Customer requirement for headers I opened both a pdf and the excelxp destination prior to the proc report (code below). The pdf destination has the backgrounds in both heading rows as I intended (and expected). The excelxp destination has the first header row all with a white background. Help! Is there any way to accomplish this using the excelXP destination? The customer wants the header rows color-coded like this to visually group the data. They also want multiple worksheets (there are other reports) in the same workbook. Any ideas are most appreciated. The proc report code is below. I am attaching the 2 outputs and the full SAS code. I am creating bogus data in the code attached as the actual data is not important. It is the formatting of the headers that has me stumped. ods escapechar='^'; proc report data=item missing split='*' style(header)=[just=l font_face=arial font_size=9pt font_weight=bold] style(column)=[just=l font_face=arial font_size=9pt]; col ('^{style [background=cxFFEBCD] Part Information}' custname ship2nm2 duns cust8nm item_nbr part_nbr altponbr size lastmill) ('^{style [background=cxCCFFFF] Projected Available Balance}' prj1-prj6) ahedmelt ('^{style [background=light green] Physical Inventory}' wip op rdy invtotal) ('^{style [background=cxFFFFCC] Tons Required}' mth1req mth2req mth3req mth4req avg_req) wks_op wks_rdy; define custname / display 'Customer' style(header)=[background=very light yellow]; define ship2nm2 / display 'Ship To*Loc' style(header)=[background=very light yellow]; define duns / display 'Duns' style(header)=[background=very light yellow]; define cust8nm / display 'HTA*Office' style(header)=[background=very light yellow]; define item_nbr / display 'AK Item #' style(header)=[background=very light yellow] style(column)=[just=c] ; define part_nbr / display 'Part #' style(header)=[background=very light yellow]; define altponbr / display 'HES*Grade' style(header)=[background=very light yellow]; define size / display 'Ga X Wid' style(header)=[background=very light yellow]; define lastmill / display 'Finish*Mill' style(header)=[background=very light yellow]; define prj1 / display "&lbl1" style(header)=[background=very light grey] style(column)=[just=c] ; define prj2 / display "&lbl2" style(header)=[background=very light grey] style(column)=[just=c] ; define prj3 / display "&lbl3" style(header)=[background=very light grey] style(column)=[just=c] ; define prj4 / display "&lbl4" style(header)=[background=very light grey] style(column)=[just=c] ; define prj5 / display "&lbl5" style(header)=[background=very light grey] style(column)=[just=c] ; define prj6 / display "&lbl6" style(header)=[background=very light grey] style(column)=[just=c] ; define ahedmelt / display 'Ahead*Melt' style(header)=[background=very light grey] style(column)=[just=c] ; define wip / display 'Wip' style(header)=[background=yellow font_weight=bold font_face=arial font_size=9pt] style(column)=[just=c] ; define op / display 'OP' style(header)=[background=yellow font_weight=bold font_face=arial font_size=9pt] style(column)=[just=c] ; define rdy / display 'Rdy' style(header)=[background=yellow font_weight=bold font_face=arial font_size=9pt] style(column)=[just=c] ; define invtotal / display 'Total*Inv' style(header)=[background=yellow font_weight=bold font_face=arial font_size=9pt] style(column)=[just=c] ; define mth1req / display "&mth1hdr" style(header)=[background=cxCCFFFF font_weight=bold font_face=arial font_size=9pt] style(column)=[just=c] ; define mth2req / display "&mth2hdr" style(header)=[background=cxCCFFFF font_weight=bold font_face=arial font_size=9pt] style(column)=[just=c] ; define mth3req / display "&mth3hdr" style(header)=[background=cxCCFFFF font_weight=bold font_face=arial font_size=9pt] style(column)=[just=c] ; define mth4req / display "&mth4hdr" style(header)=[background=cxCCFFFF font_weight=bold font_face=arial font_size=9pt] style(column)=[just=c] ; define avg_req / display 'Avg Req*(wk)' style(header)=[background=cxCCFFFF font_weight=bold font_face=arial font_size=9pt] style(column)=[just=c] ; define wks_op / display 'Wks Covered*At OP' style(header)=[just=c background=very light grey] style(column)=[just=c] ; define wks_rdy / display 'Wks Covered*Rdy to Ship' style(header)=[just=c background=very light grey] style(column)=[just=c] ; compute wks_op; if . < wks_op < 1 then do; call define('wks_op',"STYLE","STYLE=[background=red]"); end; if 1 <= wks_op <= 2 then do; call define('wks_op',"STYLE","STYLE=[background=yellow]"); end; if wks_op > 2 then do; call define('wks_op',"STYLE","STYLE=[background=green]"); end; endcomp; compute wks_rdy; if . < wks_rdy < 1 then do; call define('wks_rdy',"STYLE","STYLE=[background=red]"); end; if 1 <= wks_rdy <= 2 then do; call define('wks_rdy',"STYLE","STYLE=[background=yellow]") end; if wks_rdy > 2 then do; call define('wks_rdy',"STYLE","STYLE=[background=green]"); end; endcomp; title justify=left 'AK Steel Customer Inventory by Part'; footnote justify=left height=8pt '/sas/scripts/hondastat.sas'; run;
... View more