How to Achieve Highlight and Font Color Change in a Cell
Paper PO20
How to Achieve Highlight and Font Color Change in a Cell?
Bella Feng, Amgen Inc. Thousand Oaks, CA
ABSTRACT
This paper expands from a WUSS presentation by the author "Font Color or Highlight? You Name It!". Combining the power of PROC FORMAT, ODS and PROC REPORT, this paper enables the reader not only to achieve the font color and highlight change, but also to make the above changes to a specific column, a specific row and even to a specific cell. These font color changes and highlighting can be conditioned upon values from the same column/row or other column/columns.
THE POWER AND LIMITATION OF ODS
ODS makes the transfer between SAS output and Microsoft Excel, Word or PowerPoint as easy as two lines of code before and after the SAS procedure.
ods rtf file="D:\My Documents\WUSS\test1.doc"; proc print data=sashelp.class; run; ods rtf close;
To change the file into pdf format or html format, all you need to do is to change the blue-colored part into pdf, and html respectively. Similarly, to get the output into MS excel, the following code will do the trick:
ods html file="D:\My Documents\WUSS\test2.xls"; proc print data=sashelp.class(obs=10); run; ods html close;
Opening the file D:\My Documents\WUSS\test2.xls in excel, you see the following:
Obs
Name Sex Age Height Weight
1
Alfred M
14
69 112.5
2
Alice F
13 56.5
84
3
Barbara F
13 65.3
98
4
Carol F
14 62.8 102.5
5
Henry M
14 63.5 102.5
6
James M
12 57.3
83
7
Jane F
12 59.8 84.5
8
Janet F
15 62.5 112.5
9
Jeffrey M
13 62.5
84
10
John M
12
59 99.5
The only perceivable limitation with ODS is that if we need to change the look of the dataset in excel, we have to resort to proc format and proc report.
COMBINING WITH PROC FORMAT AND PROC REPORT
To achieve format changes without going to the template of SAS procedures, the easy way is to combine the power of ODS with that of proc format and proc report. For example, to have different highlights for the different weight categories in the above dataset, proc format and proc report should be used to change the look of the dataset in excel. Suppose we want to highlight different weight range with different colors, for the above dataset printed from proc print. For weight less than 100 lbs, you want to use yellow highlight, between 100 and 110 green, and pink for above 110lbs.
proc format; value color low-100='yellow' 100-110='green' 110-high='pink'; run;
proc report data=sashelp.class nowd; columns Name Sex Age Height Weight; define weight/style={background=color.}; run;
The first ten observations look like this:
Name Sex Age
Alfred
M
14
Alice
F
13
Barbara
F
13
Carol
F
14
Henry
M
14
James
M
12
Jane
F
12
Janet
F
15
Jeffrey
M
13
John
M
12
Height 69
56.5 65.3 62.8 63.5 57.3 59.8 62.5 62.5
59
Weight 112.5 84 98 102.5 102.5 83 84.5 112.5 84 99.5
If for some reason, your client wants the font color instead of the background to be different. All you need to do is change the word "background" in the code into "foreground" and the first ten observations will change to this:
Name Alfred Alice Barbara Carol Henry James Jane Janet Jeffrey John
Sex Age Height Weight
M
14
69 112.5
F
13 56.5
84
F
13 65.3
98
F
14 62.8 102.5
M
14 63.5 102.5
M
12 57.3
83
F
12 59.8 84.5
F
15 62.5 112.5
M
13 62.5
84
M
12
59 99.5
Similarly, if you are running many statistical tests and want to compare the different results and highlight the p-value that is smaller than 0.05, the above code can be modified to do so. In the above code the name of the colors "yellow", "green" and "pink" are used. The following are the colors that you can use by name: black, blue, brown, charcoal, cream, cyan, gold, gray, green, lilic, lime, magenta, maroon, olive, orange, pink, purple, red, rose, salmon, steel, tan, violet, tan, violet, white, and yellow. To get the exact color that you want, use the RGB notation for the different colors, which is documented in SAS/GRAPH.
Answer to a New Challenge A new challenge was raised during the author's presentation at WUSS, 2006. The question is how the font color or highlight can be changed according to the value of other column or columns. The way out is to use compute block that comes with proc report.
Suppose we have patients' data that shows the dates of their first, second and last visit. We want to highlight the second visit if the second visit is not between the first and the last visits. The following is the test dataset:
data dsn; input firstvisit ddmmyy10. @ 12 secondvisit ddmmyy10. @ 23 lastvisit ddmmyy10. ; cards; 21/09/2005 30/09/2006 10/10/2006 21/09/2005 30/09/2006 10/10/2005
21/09/2005 30/09/2005 10/10/2005 21/09/2005 30/09/2007 10/10/2006 21/09/2005 30/09/2004 10/10/2006 ; run;
Given the above data, we want our SAS code to highlight the second, fourth and fifth records as they all have a second visit date that is not between the first and last visits. To achieve this, we will need to have a computed variable that distinguishes these three records from the others. However, we do not want the variable to be shown in the report. Therefore, we define the variable with the "noprint" characteristic. The key part to achieve the highlighting is the compute block for this variable. In the compute block, based on the condition to select the records that we want to highlight, a call define statement can be used with three parameters: the column to be highlighted, `style', and `style={background=yellow}'. The following code is used:
ods html file ="C:\Documents and Settings\gfeng\My Documents\Personal\test3.xls"; proc report data=dsn nowd; columns firstvisit secondvisit lastvisit flag ; define flag/computed noprint; define firstvisit/display format=date9.; define secondvisit/display format=date9.; define lastvisit/display format=date9.; compute flag; flag=(secondvisit-firstvisit)*(lastvisit-secondvisit); if flag ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- color rank count name controlling it all in proc report
- how to apply conditional formatting to a cell
- excel vba code snippet
- excellence with excel quiz questions
- how to achieve highlight and font color change in a cell
- 224 2008 proc report in color what s your style
- how you use excel in banking amazon web services
- excel formatting best practices in financial models important
Related searches
- how to use than and then
- how to write cause and effect
- how to study effectively and efficiently
- how to install home and student 2019
- how to calculate principal and interest
- how to sell home and buy another
- how to find actual and theoretical yield
- how to figure principal and interest payment
- how to use your and you re
- how to calculate principal and interest excel
- how to use have and has
- how to find theoretical and actual yield