Programming the statistical procedures from SAS

conditional cell formating

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

conditional cell formating

hi,

i got a table like this one:

TreatmentInfection1Infection2p_value
A 3.454.640.683
B4.329.170.04
C2.991.040.02

and i would like to apply the backgroundcolor=green for the p_value if infection2 is lower then infection1 AND backgroundcolor=red if infection2 is higher than infection1. do somebody know a simple way to change the cell style based on these two conditions?

kind regards

emin


Accepted Solutions
Solution
‎01-21-2015 02:12 AM
Grand Advisor
Posts: 9,452

Re: conditional cell formating

This would be better for proc report.

and next time , post such questions at ODS and Base Reporting could get a good answer.

data have;
input Treatment     $ Infection1     Infection2     p_value     ;
cards;
A     3.45     4.64     0.683
B     4.32     9.17     0.04
C     2.99     1.04     0.02
;
run;
ods listing close;
ods html file='c:\temp\x.html' style=sasweb;
proc report data=have nowd;
columns Treatment  Infection1 Infection2 p_value;
define Treatment/display;
define Infection1/display;
define Infection2/display;
compute p_value;
 if Infection1 gt Infection2 then call define(_col_,'style','style={background=green}');
  else  call define(_col_,'style','style={background=red}');
endcomp;
run;
ods html close;
ods listing;

x.png

XIa Keshan

View solution in original post


All Replies
Occasional Contributor
Posts: 10

Re: conditional cell formating

i forgot to say that i use proc print. but if it is complicated for proc print procedure i can use proc report too.

thanks,

emin

Esteemed Advisor
Esteemed Advisor
Posts: 6,987

Re: conditional cell formating

Hi,

There are a few papers on changing cell style using proc report and compute blocks.  Here is one for starters:

http://support.sas.com/resources/papers/proceedings09/273-2009.pdf

Occasional Contributor
Posts: 10

Re: conditional cell formating

thanks RW9!

i have to say that all examples which i read until now changes the cell format based on the value of the cell itself, and my problem is that the formatting of the cell (p_value) should be based on two conditions: infection2>infection1 and p_value<0.05. can i implement two conditions in the same time in proc template, in proc report or elsewhere?

Esteemed Advisor
Esteemed Advisor
Posts: 6,987

Re: conditional cell formating

Yes, you should be able to do that with if statements in the compute, I am not work to test, but something along the lines of:

compute p_value;

     if infectiom2 > infection and p_value < 0.05 then call define('p_value','style','style=[background=green]');

endcomp;

Worst comes to worst, do the logic in a datastep, have a noprint variable with binary 1 or 0 and use that in your if statement.

You can reference specific columns/rows/cells also:

http://www2.sas.com/proceedings/forum2008/224-2008.pdf

Some other papers:

http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

Solution
‎01-21-2015 02:12 AM
Grand Advisor
Posts: 9,452

Re: conditional cell formating

This would be better for proc report.

and next time , post such questions at ODS and Base Reporting could get a good answer.

data have;
input Treatment     $ Infection1     Infection2     p_value     ;
cards;
A     3.45     4.64     0.683
B     4.32     9.17     0.04
C     2.99     1.04     0.02
;
run;
ods listing close;
ods html file='c:\temp\x.html' style=sasweb;
proc report data=have nowd;
columns Treatment  Infection1 Infection2 p_value;
define Treatment/display;
define Infection1/display;
define Infection2/display;
compute p_value;
 if Infection1 gt Infection2 then call define(_col_,'style','style={background=green}');
  else  call define(_col_,'style','style={background=red}');
endcomp;
run;
ods html close;
ods listing;

x.png

XIa Keshan

Occasional Contributor
Posts: 10

Re: conditional cell formating

Thank you guys!! Everything works as desired.

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 386 views
  • 6 likes
  • 3 in conversation