When comes to traffic lighting in Proc Tabulate, SAS only allows you to highlight certain cells by columns using the option "style=(foreground=)" (or background). But what if I want to highlight a entire row base on the value of one column (or multiple columns)? I did some research on my own and it turns out there is no easy way to do such thing, other than doing it manually. Proc Report does highlighting by row but the two procedures don't work the same. Generally Speaking, "Do It Yourself" is good enough for most of situation. However, it could be tough in some cases such as generating tables with a same format over and over again or a very long table. I came up with a solution which utilizes Proc Format and its “cntlin=” option. Below is an example: A data set “estimates” contains six variables, varA, varB, var1, var2, var3 and var4. I want to generate a table and it looks like this in Proc Tabulate: table varA*varB, (var1 var2 var3 var4)*(mean sum ……); How to highlight a row based a certain statistic of var4? To understand the following part, first, you should be familiar with cntlin option in Proc Format and foreground/background in style option of Proc Tabulate, especially the fact that foreground/background can accompany a format. First of all, use “proc tabulate out= estimates” to store the desired table as a SAS data set. proc format; value highlight 0-0.05='Red' /*Color to use*/ 0.05-high='Black'; run; data temp; /*Create a new data set based on the data set of the table*/ set estimates; length fmtname $20.; length label $20; if var4_stat <= 0.05 then do; /*Selecting rows to highlight based on a statistics of var4*/ /*Creat a format for var1*/ fmtname="nameA"; /*Format Name*/ start=var1_stat; /*Start contains the value to give the special formatted value*/ label="red"; /*label contains formatted value which is the name of color to use for highlighting*/ output; /*Creat a format for var2*/ fmtname="nameB"; start=var2_stat; label="red"; output; /*Creat a format for var3*/ fmtname="nameC"; start=var3_stat; label="red"; output; end; keep fmtname start label; run; proc sort data=temp; by fmtname; run; proc format cntlin=temp; run; proc tabulate data=estimates; var var1_stat var2_stat var3_stat var4_stat; class varA varB; table varA*varB, (var1_stat *[style=[foreground=nameA.]] var2_stat *[style=[foreground=nameB.]] var3_stat *[style=[foreground=nameC.]] var4_stat *[style=[foreground=highlight.]])*sum=""; run; Any question, comment or suggestion is welcome at jiq@muohio.edu The following part is an example for testing /**********************************************************************************************/ data test; input vara $ varb $ var1 var2 var3 var4; datalines; a a 1 2 3 0.01 a b 4 5 6 0.02 a a 7 8 9 0.03 a b 10 11 12 0.04 b a 13 14 15 0.05 b b 16 17 18 0.06 b a 19 20 21 0.07 b b 22 23 24 0.08 ; run; proc tabulate data=test out=estimates; class vara varb; var var1 var2 var3 var4; table vara*varb, (var1 var2 var3 var4)*(mean); run; proc format; value highlight 0-0.05='Red' 0.05-high='Black'; run; data temp; set estimates; length fmtname $20.; length label $20; if var4_mean <= 0.05 then do; fmtname="nameA"; start=var1_mean; label="red"; output; fmtname="nameB"; start=var2_mean; label="red"; output; fmtname="nameC"; start=var3_mean; label="red"; output; end; keep fmtname start label; run; proc sort data=temp; by fmtname; run; proc format cntlin=temp; run; proc tabulate data=estimates; var var1_mean var2_mean var3_mean var4_mean; class varA varB; table varA*varB, (var1_mean*[style=[foreground=nameA.]] var2_mean*[style=[foreground=nameB.]] var3_mean*[style=[foreground=nameC.]] var4_mean*[style=[foreground=highlight.]])*sum=""; run;
... View more