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.

Google Online Preview   Download