topic use of style to apply color to spanned columns in SAS Procedures

use of style to apply color to spanned columns

LLOUKINE — Fri, 27 Sep 2024 20:48:28 GMT

Hi, I am working on the report and I need help with applying the same colors to the headers of the spanning columns. For example I want to add “azure” color to the header cell 'PUBLISHABLE w. FLAGS'. Also, how I can add a color to the background for the 'Cutoffs Percentages' cell? Below is my SAS code and the output I got so far. I am looking for a suggestions how I can modify my code to color other header cells in the table below that are not colored now. Thank you!

proc report data=TestData (keep=cycle entity case Frequency GeoMean

Deficient_Frequency Deficient_Percent Marginal_Frequency Marginal_Percent);

column ('PUBLISHABLE w. FLAGS' cycle entity case) ('Geometric Results' Frequency GeoMean )

('Cutoffs Percentages' ( 'Deficient (<148 pmol per L)' Deficient_Frequency Deficient_Percent)

('Marginal (148-220 pmol per L)' Marginal_Frequency Marginal_Percent )) ;

define cycle/display style(header)={background=azure};

define entity/display style(header)={background=azure};

define case/display style(header)={background=azure};

COMPUTE cycle;

CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=azure]");

ENDCOMP;

COMPUTE entity;

CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=azure]");

ENDCOMP;

COMPUTE case;

CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=azure]");

ENDCOMP;

*geomeans;

define Frequency/display 'N' style(header)={background=PAY};

define GeoMean/display style(header)={background=PAY};

define LowerCLGM/display style(header)={background=PAY};

COMPUTE Frequency;

CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=PAY]");

ENDCOMP;

COMPUTE GeoMean;

CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=PAY]");

ENDCOMP;

*Deficient Percent;

define Deficient_Frequency/display style(header)={background=GWH};

define Deficient_Percent/display style(header)={background=GWH};

COMPUTE Deficient_Frequency;

CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=GWH]");

ENDCOMP;

COMPUTE Deficient_Percent;

CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=GWH]");

ENDCOMP;

*marginal Percent;

define Marginal_Frequency/display style(header)={background=PWH};

define Marginal_Percent/display style(header)={background=PWH};

COMPUTE Marginal_Frequency;

CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=PWH]");

ENDCOMP;

COMPUTE Marginal_Percent;

CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=PWH]");

ENDCOMP;

run;

Re: use of style to apply color to spanned columns

ballardw — Fri, 27 Sep 2024 21:27:34 GMT

Very strong suggestion: Only include enough variables to demonstrate the issue.

Best is to include some data.

Or use one of the SAS training data sets like SASHELP.CLASS that only has a few observations and variables to start with.

Consider the following, which is code that you should be able to run as you should have the SASHELP.CLASS data set:

proc format library=work;
value $hdrcolor
      'Student name and sex'='lightgreen'
      'Measurements' = 'gold'
;
run;

proc report data=sashelp.class
   style(header)= [background=$hdrcolor.]
   ;
   columns ('Student name and sex' name sex) ('Measurements' height weight);
   define name /    style=[background=lightgreen];
   define sex /     style=[background=lightgreen];
   define height/   style=[background=gold];
   define weight/   style=[background=gold];
run;

The format is used to assign a color to the background of any header that has the shown text (which means it may not be the best for ACROSS values). The text appearing in the header must be exactly the same as the text in the first column of the definition for the $hdrcolor format.

This same approach can be used to modify other characteristics such as the text color, font , font size or other characteristics using multiple formats and the appropriate style element.

I used lightgreen instead of azure because on my monitor I have a very hard time seeing if "azure" was even applied.

Re: use of style to apply color to spanned columns

LLOUKINE — Tue, 01 Oct 2024 13:33:44 GMT

Hi Ballardw, Thanks for your suggestion. However, I tried to apply the format as in your example before and it did not work for me. That is why I posted my question. When I run your code below this morning I also do not see that the spanning columns headers are colored according to the format statement.

Re: use of style to apply color to spanned columns

ballardw — Tue, 01 Oct 2024 13:51:02 GMT

Please show the LOG from running the Proc format and report. Copy the text from the log including the code and all the messages from both and paste into a text box opened with the </> icon above the message window.

My results:

Student name and sex		Measurements
Name	Sex	Height	Weight
Alfred	M	69	112.5
Alice	F	56.5	84
Barbara	F	65.3	98
Carol	F	62.8	102.5
Henry	M	63.5	102.5
James	M	57.3	83
Jane	F	59.8	84.5
Janet	F	62.5	112.5
Jeffrey	M	62.5	84
John	M	59	99.5
Joyce	F	51.3	50.5
Judy	F	64.3	90
Louise	F	56.3	77
Mary	F	66.5	112
Philip	M	72	150
Robert	M	64.8	128
Ronald	M	67	133
Thomas	M	57.5	85
William	M	66.5	112

Also, the bit that your PICTURE shows the words "page break" means that you have sent the output to something besides the results window. Which may mean you need to include any code that did that in case any options there override the default behavior of Proc report.

@LLOUKINE wrote:

Hi Ballardw, Thanks for your suggestion. However, I tried to apply the format as in your example before and it did not work for me. That is why I posted my question. When I run your code below this morning I also do not see that the spanning columns headers are colored according to the format statement.

Re: use of style to apply color to spanned columns

LLOUKINE — Tue, 01 Oct 2024 13:48:59 GMT

here is the LOG for the programm you suggested.

The SAS System 09:27 Tuesday, October 1, 2024

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HTMLBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome2/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 proc format library=work;
27 value $hdrcolor
28 'Student name and sex'='lightgreen'
29 'Measurements' = 'gold'
30 ;
NOTE: Format $HDRCOLOR has been output.
31 run;

NOTE: PROCEDURE FORMAT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

32
33 proc report data=sashelp.class
34 style(header)= [background=$hdrcolor.]
35 ;
36 columns ('Student name and sex' name sex) ('Measurements' height weight);
37 define name / style=[background=lightgreen];
38 define sex / style=[background=lightgreen];
39 define height/ style=[background=gold];
40 define weight/ style=[background=gold];
41 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE REPORT used (Total process time):
real time 2.39 seconds
cpu time 0.04 seconds

42
43
2 The SAS System 09:27 Tuesday, October 1, 2024

44 GOPTIONS NOACCESSIBLE;
45 %LET _CLIENTTASKLABEL=;
46 %LET _CLIENTPROCESSFLOWNAME=;
47 %LET _CLIENTPROJECTPATH=;
48 %LET _CLIENTPROJECTPATHHOST=;
49 %LET _CLIENTPROJECTNAME=;
50 %LET _SASPROGRAMFILE=;
51 %LET _SASPROGRAMFILEHOST=;
52
53 ;*';*";*/;quit;run;
54 ODS _ALL_ CLOSE;
55
56
57 QUIT; RUN;
58

and RE the picture I just did the screen shot..

Re: use of style to apply color to spanned columns

ballardw — Tue, 01 Oct 2024 14:16:02 GMT

I'm going to go out on a limb a say that appears to be a BUG in EG 7.1, which was replaced a while ago.

I just happen to have an EG 7.1 install that duplicates that output but does not in the other EG 8.3 or Foundation SAS 9.4.7.

Might be something buried in the Tagsets.sasreport13 or the style sheet.

Suggest trying sending the output to a file such as with ODS RTF, PDF or EXCEL and see the result:

Replace the "x:\" with a path, starting at a drive or mount point, that is valid in your system.

Also might consider upgrading to EG 8.3 or later.

Or run the code in SAS foundation directly instead of EG7.1

ods rtf file="x:\trial.rtf";

proc format library=work;
value $hdrcolor
      'Student name and sex'='lightgreen'
      'Measurements' = 'gold'
;
run;

proc report data=sashelp.class
   style(header)= [background=$hdrcolor.]
   ;
   columns ('Student name and sex' name sex) ('Measurements' height weight);
   define name /    style=[background=lightgreen];
   define sex /     style=[background=lightgreen];
   define height/   style=[background=gold];
   define weight/   style=[background=gold];
run;

ods rtf close;

Re: use of style to apply color to spanned columns

LLOUKINE — Tue, 01 Oct 2024 20:24:51 GMT

Unfortunately I cannot update to other version of SAS EG as it is what we have but I will try to look at your other suggestions.

Thank you!