topic Re: Colouring proc tabulate columns by column classification value in SAS Programming

Colouring proc tabulate columns by column classification value

JerryLeBreton — Thu, 26 Apr 2018 23:03:26 GMT

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

Re: Colouring proc tabulate columns by column classification value

Cynthia_sas — Fri, 27 Apr 2018 00:53:06 GMT

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.

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:

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

Re: Colouring proc tabulate columns by column classification value

JerryLeBreton — Fri, 27 Apr 2018 03:31:54 GMT

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.

Thanks again though.

Jerry

Re: Colouring proc tabulate columns by column classification value

Cynthia_sas — Fri, 27 Apr 2018 16:46:44 GMT

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