Need to create multiple background colors on first header row in ExcelXP output

[MarySue]

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;                                                               

[Cynthia_sas · Accepted solution]

Hi:
I can't open your attachments for the XLS file, got an error. But in looking at your code, my guess is that the style overrides, using ODS ESCAPECHAR are working as you want in PDF, but NOT working as you want in HTML or TAGSETS.EXCELXP.

Without using your code, I can illustrate what I think is the issue.You can see this for yourself in this example that was done using your technique and headers on SASHELP.PRDSALE data:

ODS PDF is fine with the style overrides using ODS ESCAPECHAR style functions, but HTML, EXCELXP and ODS EXCEL all ignore the style overrides. But, with a different technique (making some FAKE headers to use with ACROSS items, you can get the look you want.

Here's some code to test.

data make_fake_headers;
length fake_hdr1 fake_hdr2 fake_hdr3 fake_hdr4 $30;
  set sashelp.prdsale;
  fake_group=1;
  fake_hdr1 = 'Part Information';
  fake_hdr2 = 'Projected Available Balance';
  fake_hdr3 = 'Physical Inventory';
  fake_hdr4 = 'Tons Required';
run;
 
 
ods html(id=1) path='c:\temp' file='fake_headers.html';
ods pdf(id=2) file='c:\temp\fake_headers.pdf' notoc;
ods tagsets.excelxp(id=3) file='c:\temp\fake_headers_XP.xls' style=sasdocprinter rs=none
    options(embedded_titles= 'Yes'
            embedded_footnotes= 'Yes'
            Orientation= 'Landscape'
            skip_space='1,0,0,0,1'
            autofit_height= 'yes'
            autofilter= 'yes'
            sheet_name='Customer'
            );
ods excel(id=4) file='c:\temp\fake_headers_X.xlsx' style=htmlblue;

 ods escapechar='^';
 proc report data=make_fake_headers(obs=5) 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];
  column fake_group (fake_hdr1,(region division country))
      (fake_hdr2,(prodtype product quarter))
      (fake_hdr3,(year month))
      (fake_hdr4,(actual predict));
  define fake_group/ order  noprint;
  define fake_hdr1 / across ' '
         style(header)=[background=cxFFEBCD] ;
  define fake_hdr2 / across ' '
         style(header)=[background=cxCCFFFF] ;
  define fake_hdr3 / across ' '
         style(header)=[background=lightgreen] ;
  define fake_hdr4 / across ' '
         style(header)=[background=cxFFFFCC] ;
  define region / display 'Customer'
         style(header)=[background=very light yellow];
  define division / display 'Ship To*Loc'
         style(header)=[background=very light yellow];
  define country     / display 'Duns'
         style(header)=[background=very light yellow];
  define prodtype     / display "Product Type"
         style(header)=[background=very light grey]
         style(column)=[just=c] ;
  define product     / display "Product"
         style(header)=[background=very light grey]
         style(column)=[just=c] ;
  define quarter     / display "Quarter"
         style(header)=[background=very light grey]
         style(column)=[just=c] ;

  define year      / display 'Wip'
         style(header)=[background=yellow font_weight=bold
                        font_face=arial font_size=9pt]
         style(column)=[just=c] ;
  define month       / display 'OP'
         style(header)=[background=yellow font_weight=bold
                        font_face=arial font_size=9pt]
         style(column)=[just=c] ;
  define actual  / display "Actual"
         style(header)=[background=cxCCFFFF font_weight=bold
                        font_face=arial font_size=9pt]
         style(column)=[just=c] ;
  define predict  / display "Predict"
         style(header)=[background=cxCCFFFF font_weight=bold
                        font_face=arial font_size=9pt]
         style(column)=[just=c] ;
run;
ods _all_ close;

Note that you cannot use ACROSS variables without a GROUP or ORDER item in the leftmost column. So in addition to the 4 fake header variables, I made a fake_group variable and then used it on the report with NOPRINT.

  For more information about using ACROSS variables, especially for headers, look at the example on page 12-13 in this paper: http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf .

cynthia

[MarySue]

Thank you! I confess that I have been programming with SAS for years, but have never needed the across variable before and so that option never occurred to me. This works beautifully and now I have learned something new. Thank you again.