Cell-colors in Proc Report depending on metadata

[Jan1204]

Hi,

I am using Proc Report and I want to add a text- and background-colour to a column, depending on the value of another (hidden) column.

I used the next compute-block (it works) as a first trial:
compute some_text;
if mapping = 0 then do;
call define (_col_, "style", "style=[background=#dd3355 color=#000000]");
end;
endcomp;

This works, for all rows where mapping has the value=0.
Off course mapping can have another value (between 0 and 8). Now I can repeat the IF MAPPING statement; I believe it shall work.
What I really want, is making 'background' and 'color' metadata-driven. I don't want an "if mapping" statement, but a generic statement once; something like "style=[background=some_back_colour color=some_text_color]".
The values for 'some_back_colour' and 'some_text_color' will be stored in a dataset and could be changed if necessary.

How should I do this?

[Cynthia_sas]

Hi:
To the extent that you can use a user-defined format to set style attribute values, you could put your formatting information into a SAS dataset. It is sort of overkill for just 8 values to build a dataset for that purpose, though.

One feature of the CALL DEFINE statement is that the 3rd argument can be a variable, as well as a text string. So in the program below, I make 2 user-defined formats -- one for background color (BCK) and the other for foreground color (FORE). Then, I use those formats in 2 different ways:
1) directly, as shown on the DEFINE statement for AGE
2) indirectly, as shown in the COMPUTE block for HEIGHT

In the DEFINE statement for age, the user-defined format is specified directly in the STYLE= override
[pre]
define age / display
style(column)={background=bck. foreground=fore.};
[/pre]

So whatever the value for AGE -- when the style attribute is needed by ODS, it will apply the value found in the user-defined format as the attribute -- in this way, the user-defined format is acting like a "lookup list" for the purpose of setting the attribute value in the DEFINE statement.

However, for HEIGHT, I am not using the value for HEIGHT to set the style attributes, I want to use the value for AGE to set the attributes for the HEIGHT column. So I have to use a CALL DEFINE statement. In your example, you did the conditional processing with an IF statement. In my example, I am going to let the user-defined format and the automatic lookup take the place of the IF statement. So, when AGE is 11, the PUT function will result in a value of PINK to be put into the STYLVAR variable for background and a value of BLACK for foreground colors. Then I use the STYLVAR variable in the CALL DEFINE statement as the third argument. One nice thing about using a variable value in the CALL DEFINE is that PROC REPORT knows that STYLVAR is a character variable, so I only need to specify the variable name in the CALL DEFINE statement and as long as my format has accounted for all the possible values for AGE, the STYLVAR variable will always have a correct string for the style attribute values.

Back to the issue of a dataset, you -could- keep this information in a SAS dataset and then use the CNTLIN option of PROC FORMAT to create your user-defined format. However, for only 8 values, personally, I wouldn't bother. There are very good examples of making a format from a SAS dataset in the PROC FORMAT documentation, however, so if you really need to populate the style attribute values from a dataset, it is possible.

Remember that if you are using this PROC REPORT code in the context of the SAS Enterprise Intelligence Platform (or the BI platform) it is possible that not all client applications will be able to use this PROC REPORT style-override technique. For example, Web Report Studio has its own method of doing conditional highlighting and while this method (below) might work in a stored process, it would not work, for example in Web Report Studio Edit Report mode.

cynthia
[pre]
proc format;
value bck 11='pink'
12='cyan'
13='yellow'
14='cxcccccc'
15='red'
16='cx336699';

value fore 11='black'
12='black'
13='black'
14='black'
15='yellow'
16='white';
run;

ods html file='c:\temp\hilite.html' style=sasweb;
proc report data=sashelp.class nowd;
column name age sex weight height;
define name / order;
define age / display
style(column)={background=bck. foreground=fore.};
define weight /sum;
define height / sum;
compute height;
length stylvar $100;
stylvar = catx(' ',"style=[background=",put(age,bck.),"color=",put(age,fore.),"]");
call define (_col_, "style",stylvar);
endcomp;
run;
ods html close;
[/pre]

[Jan1204]

Cynthia, thanks.

This will help me.
I'm gonna try it.