Colouring spaning column headers in different colour with PROC REPORT

[sarunas]

Dear SAS Experts,
I need to color backgrounds of spanning headers columns in different colors. The code is as follows:
proc report data=&syslast ;

column ('Actual' Actual_Month Actual_YTD) ('Forecast' Forecast_Month Forecast_YTD);

define Actual_Month / display 'Actual Month';
define Actual_YTD / display 'Actual YTD';
define Forecast_Month / display 'Forecast Month';
define Forecast_YTD / display 'Forecast YTD';

quit;


This report produces two lines for headers: 1st line of “ACTUAL” that spans 2 columns: “Actual Month” and “Actual YTD” in the second line. The same with second set of headers.

I need to color the background of the first line in the header. I.e. Only background for “Actual” and “Forecast”. Is it feasible with PROC REPORT ? Many thanks.

Regards,
Sarunas

[Cynthia_sas]

Hi
I believe you can set a default for ALL headers in the PROC REPORT statement and then override the "lower" headers to be the alternate color. But this approach would impact ALL the spanning headers.

The other approach is to make a dummy variable in a dataset and then use that variable for an ACROSS usage and then it would have a DEFINE statement, which comes with an opportunity to set that header color and only that header. The mechanism to alter headers is quite straightforward:

style(header)={background=pink}

--on a PROC REPORT statement or a DEFINE statement--there have been quite a few examples posted.

You show all display usage variables -- are there any other variables GROUP or ORDER variables on your report??? Is this data really just 4 numbers?? How do you identify the rows??

Cynthia

[sarunas]

Hi Cynthia,
thanks for your answer. Indeed I need the upper (line 1st) header to have different colors.

The report itself has more columns, but conceptually is the same as in my example code. Basically it's a list report with grouped columns. The columns in the report are columns in data set and one row in a report is one record in the data set.

Basically I need to find a way how could I color the first line spanning or grouping headers in different colors.

How could I do that with dummy variable in data set and defined as across variable in pro report ? Thanks a lot.

Regards,
Sarunas

[Cynthia_sas]

Hi:
Here's an example. GRP is a fake grouping/ordering variable (which I need because the report can't start out with NAME under X); X, Y and Z are my fake spanning variables. Note how only the header for NAME is getting the colors from the PROC REPORT statement. You may adjust the other colors, or not, as you decide you want the appearance.

cynthia
[pre]
data class;
set sashelp.class;
grp = 1;
x = 'Span';
y = 'Actual';
z = 'Forecast';
run;

ods listing close;
ods html file='span_example.html' style=sasweb;

proc report data=class(obs=5) nowd
style(header)={background=pink foreground=black};
column grp x,name y,(sex age) z,(height weight);
define grp /order noprint;
define x / across ' '
style(header)={background=purple foreground=pink};
define name / display 'Name';
define y / across ' '
style(header)={background=white foreground=black};
define sex / display
style(header)={background=blue foreground=white};
define age / display
style(header)={background=green foreground=white};
define z/across ' '
style(header)={background=yellow foreground=black};
define height / mean
style(header)={background=yellow foreground=black};
define weight / mean
style(header)={background=cxdddddd foreground=black};
run;
ods html close;
[/pre]

[Ksharp]

Hi.Cynthia@sas 's code is great.
So that you need to change background color.The traffic light would be a good choice.

[pre]
proc format;
value $color
'Actual'='pink'
'Forecast'='green'
;
run;

ods html file='span_example.html' style=sasweb;

proc report data=sashelp.class nowd style(header)={background=$color.};
column('Actual' sex age) ('Forecast' height weight);
define sex/display;
run;
ods html close;

[/pre]



Ksharp

[sarunas]

Cynthia, Ksharp,

thank you very much for your help indeed. It worked! 🙂

Best regards,
Sarunas

[Cynthia_sas]

Hi:
That is a good approach, as long as you want all the other headers to be the same color as specified in the style template you use.

However, if you try to use spanning headers and also use the OTHER specification in the format to color the headers, it is possible to "miss" a header -- as in this example, below, where the cell above NAME stays blue, while all the other cells change to the expected colors. Also, I'm not sure why it happens, but for some of the headers you might also get this warning:
[pre]
WARNING: Wrong type of format for data type: $act.
[/pre]


cynthia
[pre]
proc format;
value $act 'Actual' = 'purple'
'Forecast' = 'white'
other = 'pink';
run;

ods listing close;
ods html file='span_example2.html' style=sasweb;

proc report data=sashelp.class(obs=5) nowd
style(header)={background=$act. foreground=cx888888};
column name ('Actual' sex age) ('Forecast' height weight);
define name / display 'Name';
define sex / display;
define age / display;
define height / mean;
define weight / mean;
run;
ods html close;

[/pre]

[Ksharp]

Hi.Cynthia.
That maybe you have null label for variable name which SAS treat is as the missing value of numeric , so assign a label 'name', That Warning would not appear again.
Or you can color it with the same background's color to hidden.

[pre]
proc format;
value $act 'Actual' = 'purple'
'Forecast' = 'white'
other = 'pink';

value $a 'na'='pink'
other='cx888888';
run;

ods listing close;
ods html file='span_example2.html' style=sasweb;

proc report data=sashelp.class(obs=5) nowd
style(header)={background=$act. foreground=$a.};
column ('na ' name) ('Actual' sex age) ('Forecast' height weight);
define name / display 'name ';
define sex / display;
define age / display;
define height / mean;
define weight / mean;
run;
ods html close;

[/pre]


Ksharp

Message was edited by: Ksharp

[Cynthia_sas]

Hi:
Many workarounds are possible. It really depends on what you want and your level of comfort with PROC REPORT and ODS.

cynthia

[Ksharp]

OK.

[pre]
proc format;
value $act 'Actual' = 'purple'
'Forecast' = 'white'
other = 'pink';

run;

ods html file='span_example2.html' style=sasweb;

proc report data=sashelp.class(obs=5) nowd
style(header)={background=$act. foreground=cx888888};
column ('09'x name) ('Actual' sex age) ('Forecast' height weight);
define name / display 'name ';
define sex / display;
define age / display;
define height / mean;
define weight / mean;
run;
ods html close;
[/pre]


Ksharp

[Cynthia_sas]

Hi:
As I said, many workarounds are possible. '09'x may not work across all destinations that support style or could have unintended consequences in other destinations that do not recognize '09'x. For example, if you have the LISTING destination or the CSV destination turned on when you submit this code you will see an unprintable character above the NAME header.

How someone gets the spanned header to be in a different color REALLY, REALLY depends on what they want to do. There is no single right method -- there are only different methods -- and each method comes with pros and cons that need to be considered.

I hope that this question has been answered enough times and different ways to satisfy the original poster and that we can move on to other questions.

cynthia