BookmarkSubscribeRSS Feed
omega1983
Calcite | Level 5

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

 

Detail1Detail2Detail3
Loan_NumL_NameEmployerSalaryRegionHire_Date
1122SmithComp133000West5/1/1999
1123SmithComp233001West5/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;

1 REPLY 1
Cynthia_sas
SAS Super FREQ

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;


method1_compare_method2_html_93.png

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1361 views
  • 0 likes
  • 2 in conversation