topic Re: Need Final Steps for PROC TABULATE Traffic Lighting with PROC FORMAT in SAS Procedures

Need Final Steps for PROC TABULATE Traffic Lighting with PROC FORMAT

MelissaM — Sat, 26 Feb 2022 13:50:45 GMT

Hello SAS Community!

Objective

Create a table which highlights the cells using the following rules:

4cyl cars which begin with "A": color the cells blue

NOT 4cyl cars that do NOT begin with "A": color the cells yellow.

I’d also like the total (105 for this subset) to show up, if possible.

My SAS attempt is below (doesn't work).

My Excel desired output is also displayed.

data cars;
	set sashelp.cars;

if substr(make, 1, 1) in ("A" "B" "C");

if (cylinders = 4) and (substr(make,1,1) = "A") THEN BG1F=0;else
IF (clyinders NE 4) and (substr(make,1,1) ne "A") then BG1F=1;


keep make type origin cylinders;

run;

proc format;
	value 	bg1f	0=blue 1=yellow;
 
run;

proc tabulate data=cars style={  background=bg1f.};
	class make type origin cylinders;
	classlev make;
	tables (all='')*(make)*(type),(origin)*(cylinders)*all/nocellmerge;
run;

Re: Need Final Steps for PROC TABULATE Traffic Lighting with PROC FORMAT

sbxkoenk — Sat, 26 Feb 2022 13:29:16 GMT

Hello,

You want an example on color formats?

Proc format;
value $sexfmt
'M'='light blue'
'F'='pink';
run;

ods html path='C:\WorkShop\' body='printout.html';
proc print data=sashelp.class noobs;
where Age = 15;
var Name Height;
var Sex / style=[background=$sexfmt. font_weight=bold];
run;
ods html close;
/* end of program */

Koen

Re: Need Final Steps for PROC TABULATE Traffic Lighting with PROC FORMAT

MelissaM — Sat, 26 Feb 2022 13:45:40 GMT

It didn't work for me, unfortunately.
I updated my post to show the desired output (in MS Excel), which shows blue & yellow cells.

Re: Need Final Steps for PROC TABULATE Traffic Lighting with PROC FORMAT

mkeintz — Sat, 26 Feb 2022 16:42:53 GMT

The OP's traffic lighting needs for highlighting selective cells are not dependent on the values in the cells. Instead, they are dependent on row and column values, so I do not see how a format utilized in a PROC TABULATE can do the job.

I wonder if some of the PROC REPORT experts on this forum could produce the desired results.

Re: Need Final Steps for PROC TABULATE Traffic Lighting with PROC FORMAT

ballardw — Sun, 27 Feb 2022 02:53:15 GMT

I managed to do something like that with Proc Tabulate one time but the amount of custom coding, pre-summarizing data, writing custom formats for not-actually-values displayed was such that it would be faster to manually set colors. That approach involved creating unique values for the cells that display, by that I mean to display a blue 2 the value was actually like 2.1 and for a yellow 2 it was 2.2 so that a single format could be assigned. Not interested in going there.

I also see some serious complications with Proc Report because of the multiple nestings in both row and column and how to address the column values.

If I were to attempt such a thing again I would 1) summarize the data first (possibly with Proc Tabulate creating an output data set) and then 2) look at the data step Report Writing Interface which is going to let you examine multiple variables to set properties for cells.

Re: Need Final Steps for PROC TABULATE Traffic Lighting with PROC FORMAT

MelissaM — Fri, 04 Mar 2022 07:02:26 GMT

You are both correct.
I was able to use formatting to assign values within the cells, but that didn't work for my actual data because:
1. I want the program to be dynamic so the highlighting updates when the data is updated, and
2. For example, this data had multiple cells with 1's. I only wanted to highlight a single cell with a 1.
Here's a link with much more information on the topic:
https://communities.sas.com/t5/SAS-Programming/Colouring-proc-tabulate-columns-by-column-classification-value/td-p/457986