DATA Step, Macro, Functions and more

Colouring proc tabulate columns by column classification value

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 88
Accepted Solution

Colouring proc tabulate columns by column classification value

Hi All

 

I'm trying to colour the cells of a column based on the value of the class variable.  In spite of the zillion posts and papers on the subject I can't find the answer.

 

Here is a simplified illustration of the task:

 

proc format;
  value $sex
  'M'='lightblue'
  'F'='lightpink';
 run;
 
proc tabulate data=sashelp.class ;
class sex ;
classlev sex / style=[background=$sex.];
var weight height ;
table 
weight*mean height*mean
,
sex
;
run;

This changes the colour of the column heading background just fine, but I want the whole column to be the same colour too.

 

Having the analysis variables in the row dimension is a bit unusual but the format of my real world task is dictated by people way above my pay grade.   

 

Any ideas?

 

TIA,  Jerry

 


Accepted Solutions
Solution
‎04-26-2018 11:22 PM
SAS Super FREQ
Posts: 9,429

Re: Colouring proc tabulate columns by column classification value

Posted in reply to JerryLeBreton

Hi:

  Have a look at page 18 of this document: https://support.sas.com/resources/papers/stylesinprocs.pdf. You need

style=<parent>

with PROC TABULATE -- note that snippet is put into a code box because frequently the < + word + > gets messed up in the forum posting. What you want to do is possible.

style_parent_tabulate.png

 

  Unfortunately, to get what you want, you have to do what you specifically said you didn't want to do. You have to move the statistic into the COL dimension TABULATE.

 

  I can think of a few ways of getting around this, but they basically involve pre-summarizing the data -- and not using TABULATE. So here's something done with PROC REPORT:

hilite_proc_report.png

Instead of pre-summarizing the data, I just created a DATALINES and added Gender to show a different statistic. Here's that code:

data example;
  infile datalines;
  input varname $ Statname $ Fstat Mstat;
return;
datalines;
Weight Mean 90.11 108.95 
Height Mean 60.59 63.91 
Gender   N    9 10
;
run;

proc report data=example;
  column varname statname ('Sex' Fstat Mstat);
  define varname / "Variable" display style(column)=Header;
  define statname / "Statistic" display style(column)=Header;
  define Fstat / 'F' display f=6.2
         style(header column)={background=lightpink};
  define Mstat / 'M' display f=6.2 
         style(header column)={background=lightblue};
  compute Mstat;
     if varname = 'Gender' then do;
	    call define('Fstat','format','6.0');
		call define('Mstat','format','6.0');
	 end;
  endcomp;
run;

Hope this helps point you someplace useful.

 

Cynthia

View solution in original post


All Replies
Solution
‎04-26-2018 11:22 PM
SAS Super FREQ
Posts: 9,429

Re: Colouring proc tabulate columns by column classification value

Posted in reply to JerryLeBreton

Hi:

  Have a look at page 18 of this document: https://support.sas.com/resources/papers/stylesinprocs.pdf. You need

style=<parent>

with PROC TABULATE -- note that snippet is put into a code box because frequently the < + word + > gets messed up in the forum posting. What you want to do is possible.

style_parent_tabulate.png

 

  Unfortunately, to get what you want, you have to do what you specifically said you didn't want to do. You have to move the statistic into the COL dimension TABULATE.

 

  I can think of a few ways of getting around this, but they basically involve pre-summarizing the data -- and not using TABULATE. So here's something done with PROC REPORT:

hilite_proc_report.png

Instead of pre-summarizing the data, I just created a DATALINES and added Gender to show a different statistic. Here's that code:

data example;
  infile datalines;
  input varname $ Statname $ Fstat Mstat;
return;
datalines;
Weight Mean 90.11 108.95 
Height Mean 60.59 63.91 
Gender   N    9 10
;
run;

proc report data=example;
  column varname statname ('Sex' Fstat Mstat);
  define varname / "Variable" display style(column)=Header;
  define statname / "Statistic" display style(column)=Header;
  define Fstat / 'F' display f=6.2
         style(header column)={background=lightpink};
  define Mstat / 'M' display f=6.2 
         style(header column)={background=lightblue};
  compute Mstat;
     if varname = 'Gender' then do;
	    call define('Fstat','format','6.0');
		call define('Mstat','format','6.0');
	 end;
  endcomp;
run;

Hope this helps point you someplace useful.

 

Cynthia

Frequent Contributor
Posts: 88

Re: Colouring proc tabulate columns by column classification value

Posted in reply to Cynthia_sas

Thanks Cynthia!

 

Moving the mean to nest beneath the SEX column, is a perfect solution.

 

Unfortunately, my example rather understated my real task, in which I have a range of different statistics (including pctsum<denominator> types) to apply to a multitude of analysis variables, so making a Proc Tabulate solution impossible, I think.

 

I might just have to accept defeat - or consider a re-write with Proc Report. Smiley Sad

 

Thanks again though.  

 

Jerry 

SAS Super FREQ
Posts: 9,429

Re: Colouring proc tabulate columns by column classification value

Posted in reply to JerryLeBreton

Hi:
  Well, the nice thing about PROC REPORT is that you can calculate just about any numbers with any formula and not just pctsum<denom>.

  Here are a bunch of PROC REPORT and ODS papers that might help you:
Booth, Allison McMahill. 2010. "Evolve from a Carpenter’s Apprentice to a Master Woodworker: Creating a Plan for Your Reports and Avoiding Common Pitfalls in REPORT Procedure Coding."  
Link: http://support.sas.com/resources/papers/proceedings10/133-2010.pdf.
Booth, Allison McMahill. 2011. "Beyond the Basics: Advanced REPORT Procedure Tips and Tricks Updated for SAS 9.2."  
Link: http://support.sas.com/resources/papers/proceedings11/246-2011.pdf.
Booth, Allison McMahill. 2012. "PROC REPORT Unwrapped: Exploring the Secrets behind One of the Most Popular Procedures in Base SAS Software."   
Link: http://www.pharmasug.org/proceedings/2012/TF/PharmaSUG-2012-TF20-SAS.pdf.
Booth, Allison McMahill. 2007. "Beyond the Basics: Advanced PROC REPORT Tips and Tricks."  
Link: http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf.

Eslinger, Jane. 2015.  "The REPORT Procedure: A Primer for the Compute Block."  
Link: https://support.sas.com/resources/papers/proceedings15/SAS1642-2015.pdf.
Zender, Cynthia L. 2008. "Creating Complex Reports."
 Link: http://www2.sas.com/proceedings/forum2008/173-2008.pdf.

Zender, Cynthia L., and Allison M. Booth. 2013. "Turn Your Plain Report into a Painted Report Using ODS Styles."
Link: http://support.sas.com/resources/papers/proceedings13/366-2013.pdf.

Zender, Cynthia L. 2014. "Paper SAS388-2014, Sailing Over the ACROSS Hurdle in PROC REPORT."
Link: http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf.

Zender, Cynthia L. 2008. "Creating Complex Reports."  
Link:  http://www2.sas.com/proceedings/forum2008/173-2008.pdf
Zender, Cynthia L. 2017. "Paper SAS431-2017 Go Ahead and _BREAK_-down: Advanced COMPUTE Block Examples"
Link: https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf
Zender, Cynthia L. 2014. "That's All Right: More Complex Reports"
Link: http://support.sas.com/resources/papers/proceedings16/SAS5762-2016.pdf


Cynthia

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 151 views
  • 0 likes
  • 2 in conversation