Spanning Headers and Color Code Columns

omega1983 — Mon, 03 Jun 2013 20:53:24 GMT

I admit I have not utilized spanning headers and color coding columns in my short SAS career. Here is a sample of my desired results. I want to color code entire columns with colors based on the data. I attempt to plug in Details 1 through 3 in the code. Essentially the entire columns need to be color coded with spanning headers based on the logic I have below. I am getting error msgs so thought I would share with the group

Detail1		Detail2		Detail3
Loan_Num	L_Name	Employer	Salary	Region	Hire_Date
1122	Smith	Comp1	33000	West	5/1/1999
1123	Smith	Comp2	33001	West	5/2/1999

proc report data = test2;

colums Loan_num ('^S={Background=Blue} Detail1' )

L_Name('^S={Background=Blue} Detail1' )

Employer('^S={Background=Orange} Detail2' )

Salary('^S={Background=Orange} Detail2' )

Region('^S={Background=Green} Detail3' )

Hire_Date('^S={Background=Green} Detail3' );

run;

Re: Spanning Headers and Color Code Columns

Cynthia_sas — Tue, 04 Jun 2013 17:47:22 GMT

Hi:

First you have your spanning header syntax wrong. The spanning header goes inside parentheses and the variable that it is going to "sit on top of" is also inside the parentheses...so more like this without worrying about style overrides:

column ('Detail1' var1) ('Detail1' var2) ('Detail2' var3) ('Detail2' var4);

But, there are other ways to color the headers before moving into ODS ESCAPECHAR territory. And, as it stands, you don't show what destination you want. The solution for RTF and PDF is slightly different from HTML and of course, there is no way to color headers in the LISTING or CSV destinations. So the destination will make a difference.

There are 3 basic methods. Going from the simple to the complex, as shown in the program below. It uses SASHELP.SHOES to illustrate the point. Method 1 and Method 2 are OK for RTF and PDF. If you don't like the look of Method 2 in HTML, then you have to move to the most complex Method 3. BTW, Method 2 will give different results in SAS 9.1 vs 9.2/9.3. So even if Method 2 looks OK in SAS 9.1.3, by the time you get to SAS 9.2, you would need to move to Method 3 for HTML. Screenshot of Method1/Method2 is attached for 9.3.

cynthia

ods html file='c:\temp\makebands.html' style=sasweb;
ods pdf file='c:\temp\makebands.pdf';
ods rtf file='c:\temp\makebands.rtf';

ods escapechar='^';

** Method 1: Use Split character in header;

proc report data = sashelp.shoes(obs=10) nowd split='/';
column region subsidiary product sales inventory returns;
title 'Method 1';
define region / display 'Detail1/Region'
             style(header)={background=lightblue}
             style(column)={background=lightblue};
define subsidiary/ display 'Detail1/Subsidiary'
             style(header)={background=lightblue}
             style(column)={background=lightblue};

define product / display 'Detail2/Product'
             style(header)={background=lightorange}
             style(column)={background=lightorange};
define sales/ display 'Detail2/Sales'
             style(header)={background=lightorange}
             style(column)={background=lightorange};

define inventory / display 'Detail3/Inventory'
             style(header)={background=lightgreen}
             style(column)={background=lightgreen};
define returns/ display 'Detail3/Returns'
             style(header)={background=lightgreen}
             style(column)={background=lightgreen};
run;

** Method2;
** Will look OK in RTF and PDF -- will look different in HTML;
proc report data = sashelp.shoes(obs=10) nowd split='/';
column ('^S={background=lightblue}Details1' region subsidiary )
         ('^S={background=lightorange}Details2' product sales )
         ('^S={background=lightgreen}Details3' inventory returns);
title 'Method 2';
define region / display 'Region'
             style(header)={background=lightblue}
             style(column)={background=lightblue};
define subsidiary/ display 'Subsidiary'
             style(header)={background=lightblue}
             style(column)={background=lightblue};

define product / display 'Product'
             style(header)={background=lightorange}
             style(column)={background=lightorange};
define sales/ display 'Sales'
             style(header)={background=lightorange}
             style(column)={background=lightorange};

define inventory / display 'Inventory'
             style(header)={background=lightgreen}
             style(column)={background=lightgreen};
define returns/ display 'Returns'
             style(header)={background=lightgreen}
             style(column)={background=lightgreen};
run;

** Method 3;
** Need some helper variables if need HTML output;
** and want the spanning headers to be all the same color as the rest of the column;
data newshoes;
set sashelp.shoes;
d1='Details1';
d2='Details2';
d3='Details3';
extravar = 'x';
run;

proc report data = work.newshoes(obs=10) nowd split='/';
column extravar d1,(region subsidiary) d2,(product sales) d3,(inventory returns);
title 'Method 3';
define extravar / order noprint;
define d1/across ' ' style(header)={background=lightblue};
define region / display 'Region'
             style(header)={background=lightblue}
             style(column)={background=lightblue};
define subsidiary/ display 'Subsidiary'
             style(header)={background=lightblue}
             style(column)={background=lightblue};

define d2/across ' ' style(header)={background=lightorange};
define product / display 'Product'
             style(header)={background=lightorange}
             style(column)={background=lightorange};
define sales/ display 'Sales'
             style(header)={background=lightorange}
             style(column)={background=lightorange};

define d3/across ' ' style(header)={background=lightgreen};
define inventory / display 'Inventory'
             style(header)={background=lightgreen}
             style(column)={background=lightgreen};
define returns/ display 'Returns'
             style(header)={background=lightgreen}
             style(column)={background=lightgreen};
run;

ods _all_ close;

topic Re: Spanning Headers and Color Code Columns in SAS Programming

Spanning Headers and Color Code Columns

Re: Spanning Headers and Color Code Columns