How to color proc report when using across columns

[SASAlex101]

I'm using a couple of across columns to formulate a report. and I want to traffic light the grid. However I only seem to be able to traffic light the aggregated variable as shown and not the rest of the values under the across variables. 

ods results=on;
ods select all;
proc SQL;
DROP TABLE WORK.ALL_Data;
quit;

Data WORK.All_Data;
Length ID_COL $100. GroupCol1 $100. GroupCol2 $100.;
INFILE DATALINES DELIMITER="#" dsd;
INPUT ID_COL $ NumCol GroupCol1 $ GroupCol2 $;
datalines;
A#1#G1_A#G2_A
A#2#G1_A#G2_B
B#3#G1_A#G2_C
B#6#G1_B#G2_A
B#4#G1_B#G2_B
B#0#G1_B#G2_C
C#4#G1_C#G2_A
C#9#G1_C#G2_B
C#2#G1_C#G2_C
C#1#G1_D#G2_A
A#1#G1_A#G2_A
A#2#G1_A#G2_B
B#3#G1_A#G2_C
B#6#G1_B#G2_A
B#4#G1_B#G2_B
B#0#G1_B#G2_C
C#4#G1_C#G2_A
C#9#G1_C#G2_B
A#1#G1_A#G2_A
A#2#G1_A#G2_B
A#3#G1_A#G2_C
B#6#G1_B#G2_A
B#4#G1_B#G2_B
B#0#G1_B#G2_C
C#4#G1_C#G2_A
C#6#G1_D#G2_B
C#8#G1_D#G2_C
A#3#G1_E#G2_A
D#2#G1_E#G2_B
D#7#G1_E#G2_C
A#7#G1_F#G2_A
E#8#G1_F#G2_B
F#3#G1_F#G2_C
C#9#G1_C#G2_B
C#6#G1_D#G2_B
A#8#G1_D#G2_C
D#3#G1_E#G2_A
D#2#G1_E#G2_B
D#7#G1_E#G2_C
D#7#G1_F#G2_A
E#8#G1_F#G2_B
B#3#G1_F#G2_C
F#4#G1_A#G2_A
F#9#G1_A#G2_B
A#3#G1_A#G2_C
F#6#G1_B#G2_A
F#3#G1_B#G2_B
F#9#G1_B#G2_C
F#3#G1_C#G2_A
F#7#G1_C#G2_B
F#2#G1_C#G2_C
G#0#G1_D#G2_A
G#8#G1_D#G2_B
G#9#G1_D#G2_C
G#3#G1_E#G2_A
B#2#G1_E#G2_B
G#2#G1_E#G2_C
B#8#G1_F#G2_A
H#4#G1_F#G2_B
H#6#G1_F#G2_C
G#9#G1_D#G2_C
B#3#G1_E#G2_A
G#2#G1_E#G2_B
G#2#G1_E#G2_C
H#8#G1_F#G2_A
B#0#G1_B#G2_C
C#4#G1_B#G2_A
C#9#G1_C#G2_B
C#2#G1_C#G2_C
C#1#G1_D#G2_A
C#6#G1_B#G2_B
C#8#G1_D#G2_C
D#3#G1_E#G2_A
D#2#G1_E#G2_B
D#7#G1_E#G2_C
C#4#G1_F#G2_B
H#6#G1_F#G2_C
run;

Proc Report data=WORK.All_Data()
nowd
style(header)={height=40 vjust=middle just=center};
columns
ID_COL	
NumCol
NumCol_N
(GroupCol2,GroupCol1)
;
define ID_COL / group "ID";
define NumCol / noprint analysis n;
define GroupCol1 / across  "GroupCol1";
define GroupCol2 / across  "GroupCol2";
define NumCol_N / computed;

compute NumCol_N;
NumCol_N=NumCol.n;
if NumCol_N eq 2 then call define(_col_,"style","style={background=cx11DD22}"); 
endcomp;

run;
title;

this is what I'm trying to achieve....

I wanted to know if this can be done in proc report with the across columns. I have used a work around by tabulating this table manually and then using helper columns to define each color for each column, but if I could do it using just the simple proc report code and raw data, that would be more efficient coding. 

[ballardw]

Like this?

proc format library=work;
   value mycolor
   2 = 'cx11DD22'
   ;
run;

Proc Report data=WORK.All_Data()
   nowd
   style(header)={height=40 vjust=middle just=center}
;
   columns ID_COL(GroupCol2,GroupCol1) ;
   define ID_COL / group "ID";
   define GroupCol1 / across  "GroupCol1" style={background=mycolor.};
   define GroupCol2 / across  "GroupCol2";
run;
title;

This uses the formatted value of the result to set the background color for the cell. So different values, or ranges of values, can be applied.

[SASAlex101]

thanks! and is there a way to apply different formats to different values in the actoss variables? i.e perhaps G2_A needs to have a dufferent format than thresholds than those under G2_B.

[ballardw]

ACROSS variables add a great deal of complexity as in I think the solution would have to reference each created column separately using the column number notation.

https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

Has a sort of example if you dig through it.

Unless @Cynthia_sas has a better idea

(my other approach would be data transformation to create multiple variables instead of the single second row across variable)

[Cynthia_sas · Accepted solution]

Thanks, @ballardw for referencing my paper. The "sort of" example in that paper was Example 10 on pages 10 and 11. The difference between what I show in the paper and what I think you want to do is that in Example 10, I used the same color to apply traffic lighting to the other columns under the ACROSS item. But it seems like this example they want to apply the traffic-lighting to different single columns under an ACROSS item. The only way to do that is by figuring out the absolute column number as shown in the paper. It is sort of a pain, but here's a simple example using your data, but cutting down the number of columns, just to make it easier to figure out the absolute column numbers that will need to be referenced in the COMPUTE block:

  So notice that there's a pattern: I have 3 columns for GroupCol1  under each unique value of GroupCol2. Based on this and counting from left to right, the column for G1_A under G2_A will be referred to internally by PROC REPORT as _C3_ and likewise, the column for G1_B under G2_A will be referred to internally by PROC REPORT as _C4_ and the next column for G1_C under G2_A will be referred to as _C5_ and then we keep counting _C6_, _C7_, _C8_ as shown above.

  Using this code, now I know which column I need to test if I want G1_A under G2_A to be a different color than G1_A under G2_B:

  In the COMPUTE block, I MUST refer to the absolute columns under the ACROSS variable(s) by the absolute column name. For purposes of this example, since you wanted the ACROSS variable to play a role in the traffic lighting, there was no way around doing something like this in PROC REPORT. You could pre-summarize and create a helper variable to set the colors, that might seem easier. 

  I may have misunderstood what it is you want to do. You didn't need to create NumCol_N, as far as I could tell, so I didn't use it.

  Hope this helps,

Cynthia

** Another example:

Change both format and style:

And how to do trafficlighting and change the format for the entire column (changed _c6_, _c7_ and _c8_):

[SASAlex101]

this is excellent. thank you to everyone.

Looks like in order to format the cells under the across variable you must refer to the absolute column numbers as per Cynthia's solution. The only other way is to tabulate the data first then add helper columns for proc report to read in.