Help using Base SAS procedures

How do I Change the color of column headers under an across variable in Proc Report

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How do I Change the color of column headers under an across variable in Proc Report

[ Edited ]

I want to change the color of the header background and foreground for one value of the variables under the across variable (Cases Rate). I used a compute block to change the column background to light gray using the absolute column references _c4_ and _c5_. How do I change the header style attributes for _c4_ and _c5_, for example, to background=gainboro and foreground=black? I know I can change the color of the across variable header row (disease=cold) with a format, but what about the cells that read "Cases" and "Rates" under "Cold?" Version 9.3 or 9.4, using ODS PDF and proc report within ODS layout. Moving this question from stack overflow based on a suggestion from a user there that I might find an anwser here (Thanks Joe). A sample SAS Proc report code is below.

 

data test;
length name $ 10 disease $ 10.;
infile datalines dsd;
input name $ disease cases rate;
datalines;
State,Fever,4847,25.16
State,Cold,25632,131.5
State,Flu,103825,535.82
Lincoln,Fever,3920,44.17
Lincoln,Cold,16913,190.18
Lincoln,Flu,62965,735.39
Washington,Fever,827,56.56
Washington,Cold,3609,234.26
Washington,Flu,16610,1078.8
Kings,Fever,1026,37.45
Kings,Cold,4984,181.85
Kings,Flu,18388,694.33
Sussex,Fever,1411,78.38
Sussex,Cold,5515,300.46
Sussex,Flu,13881,813.11
Queens,Fever,616,26.03
Queens,Cold,2496,107.75
Queens,Flu,12518,558.09
;
run;


proc report data=test nowd headline headskip
STYLE(Header)={background=charcoal foreground=white }
style(column)={background=gray foreground=black}
style(report)=[rules=rows bordercolor=white];
columns (name disease,(cases rate));
define name/group order=data 'County' style(column)={background=lightgray} style(header)=[bordertopcolor=gainsboro background=gainsboro foreground=black];
define disease/across '' order=data  ;
define cases/'Cases' format=comma9. ;
define rate/'Rate' format=comma12.1 ;
compute cases;
    call define('_c4_','style','style={background=lighttgray}');
    call define('_c5_','style','style={background=lightgray}');
endcomp;
run;
quit;
run;

 


Accepted Solutions
Solution
‎08-11-2016 09:18 AM
SAS Super FREQ
Posts: 8,966

Re: How do I Change the color of column headers under an across variable in Proc Report

Hi:
I just got back from teaching. I will take a look. The bottom line is that if you're not using PROC REPORT for any of the COMPUTE or LINE capabilities, this kind of column/cell inheritance is built into PROC TABULATE, which has the <parent> method of inheriting style attributes. See page 18's example here: https://support.sas.com/resources/papers/stylesinprocs.pdf they use red, green and yellow, but you could easily alternate colors.

cynthia

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,966

Re: How do I Change the color of column headers under an across variable in Proc Report

Hi:

  One method is described in this paper on actually using ACROSS variables to control the header color: http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

 

  Another method is shown in the example below with user-defined formats:

report_header.png

 

  Colors are a bit garish, I know, but you can see where every color is coming from in the code.

 

  Hope this helps.

 

cynthia

 

New Contributor
Posts: 4

Re: How do I Change the color of column headers under an across variable in Proc Report

Posted in reply to Cynthia_sas

Thanks Cynthia. I have several of your proc report papers. I think the format option would change all the headers that have the same value, correct? I want to change only one of the header columns, even though there are several that have the same value. The headers I want to change are highlighted below.

 

Capture.PNG

SAS Super FREQ
Posts: 8,966

Re: How do I Change the color of column headers under an across variable in Proc Report

Hi, you could make the format option work for the situation you show. But, CALL DEFINE will NOT touch the header rows directly. So, you'll have to use some other techniques. I think that Allison Booth and I had some examples in our Painted Report paper and I had an example in the ACROSS paper. I can't post anything else today during the day, but I will find an example and post it tonight.

cynthia
SAS Super FREQ
Posts: 8,966

Re: How do I Change the color of column headers under an across variable in Proc Report

Posted in reply to Cynthia_sas

Hi
2 questions...

what is your destination of interest? RTF, PDF or HTML?

And, what do you actually want for the County column -- everything the same color for Header and the county values and then a different color above the County header in the empty cell?
 
cynthia

New Contributor
Posts: 4

Re: How do I Change the color of column headers under an across variable in Proc Report

Posted in reply to Cynthia_sas

My destination is pdf, but I am also using ods layout to get other elements on the page. In terms of the color of the column headers, the same color as the column would be fine. I just want the colors to have a skip pattern like the columns themselves, to make it more readable. 

 

I will look at the papers you suggest. Thanks again

Solution
‎08-11-2016 09:18 AM
SAS Super FREQ
Posts: 8,966

Re: How do I Change the color of column headers under an across variable in Proc Report

Hi:
I just got back from teaching. I will take a look. The bottom line is that if you're not using PROC REPORT for any of the COMPUTE or LINE capabilities, this kind of column/cell inheritance is built into PROC TABULATE, which has the <parent> method of inheriting style attributes. See page 18's example here: https://support.sas.com/resources/papers/stylesinprocs.pdf they use red, green and yellow, but you could easily alternate colors.

cynthia
New Contributor
Posts: 4

Re: How do I Change the color of column headers under an across variable in Proc Report

Posted in reply to Cynthia_sas

Proc tabulate it is. I am using PROC REPORT LINE capability in several of the reports, but I can mix and match as needed. Thanks!

Contributor
Posts: 60

Re: How do I Change the color of column headers under an across variable in Proc Report

[ Edited ]
Posted in reply to Cynthia_sas

I would like to get an excel output working so that different across values are colored individually. I have attached an sample code on which I would like to color the var1 and var2 headers. I tried to follow Cynthia's instructions but couldn't get it working for some reason.


%let outputurl=;

proc format;
value $color "var1"='Gray'
"var2"='Yellow';
run;

data reportdata;
col1="var1";
col2="by1";
col3=1;
output;
col1="var1";
col2="by2";
col3=3;
output;
col1="var2";
col2="by1";
col3=9;
output;
col1="var2";
col2="by2";
col3=4;
output;
run;

data reportdata;
set reportdata;
dummy=1;
run;

/*options device=ACTXIMG;*/
/*ods excel file="&outputurl..xlsx"*/
/* options(sheet_interval="none" frozen_headers='2' frozen_rowheaders='1' gridlines='ON' */
/*orientattion='LANDSCAPE' suppress_bylines='ON' SHEET_NAME='Sheet1' autofilter='ALL');*/
proc report data=reportdata out=test;
col col1,(col2,col3) dummy;
define col1/across '' style(header)=Header{background=$color.};
define col2 /across '';
define col3/ '' nozero;
define dummy/noprint group;
run;

/*ods excel close;*/
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 1856 views
  • 0 likes
  • 3 in conversation