Date:         Wed, 22 Oct 2008 11:26:56 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: How to do conditional formatting in SAS?
Comments: To: Dianne Rhodes <diannerhodes@COMCAST.NET>
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

Dianne,

Thanks very much; that did work beautifully.   Here is what my report wound
up looking like:

ods listing close;
ods tagsets.excelxp
file='C:\Work_Activities\injections_study_patients\results_report.xml'
style=analysis3
options(absolute_column_width='10,10,10,8,8,10,10,15,8,15,8,15,10,8,8,8,8,10,10,15,8,15,8,15,10,8,8,8,8,20'
sheet_label=' ');
ods tagsets.excelxp options(sheet_name="Report");

proc report data=newvisits2 nowindows
style(report)=[rules=all cellspacing=0 bordercolor=gray]
style(header)=[background=lightskyblue foreground=black]
style(column)=[background=lightcyan foreground=black];
column
study_nbr
visit_location
date_of_visit
age
va_od
va_od_logmar
isp_first_od_logmar
logmar_od_diff_formatted
va_os
va_os_logmar
isp_first_os_logmar
logmar_os_diff_formatted;

define study_nbr/display;
define visit_location/display;
define date_of_visit/display;
define age/display;
define va_od/display;
define va_od_logmar/display;
define isp_first_od_logmar/display;
define logmar_od_diff_formatted/computed 'Logmar OD Diff Formatted';
define va_os/display;
define va_os_logmar/display;
define isp_first_os_logmar/display;
define logmar_os_diff_formatted/computed 'Logmar OS Diff Formatted';

compute logmar_od_diff_formatted;
    if logmar_od_diff >= -.25 and logmar_od_diff <= 25 then
        logmar_od_diff_formatted= logmar_od_diff;
    else if logmar_od_diff=. then
        logmar_od_diff_formatted=logmar_od_diff;
    else
        do;
            logmar_od_diff_formatted=logmar_od_diff;
            call define(_col_,'style',
            'style=[foreground=black
            background=pink
            font_weight=bold]');
        end;
endcomp;

compute logmar_os_diff_formatted;
    if logmar_os_diff >= -.25 and logmar_os_diff <= 25 then
        logmar_os_diff_formatted= logmar_os_diff;
    else if logmar_os_diff=. then
        logmar_os_diff_formatted= logmar_os_diff;
    else
        do;
            logmar_os_diff_formatted=logmar_os_diff;
            call define(_col_,'style',
            'style=[foreground=black
            background=pink
            font_weight=bold]');
        end;
endcomp;

run;

ods tagsets.excelxp close;
ods listing;



-Mary

p.s. note the "nowindows" option on the report; the report window really
drove me crazy!

----- Original Message -----
From: Dianne Rhodes
To: SAS-L@LISTSERV.UGA.EDU
Sent: Tuesday, October 21, 2008 10:48 AM
Subject: Re: How to do conditional formatting in SAS?


On Tue, 21 Oct 2008 10:32:55 -0500, Mary <mlhoward@AVALON.NET> wrote:

>I would like to add conditional formatting to the column of the change in
>logmar scores; such as to change the background color to yellow or
something
>when the value is out of a certain range (such as when the value is
> .25).
>
>Is there a way that I can add conditional formatting on this column via
Proc
>Report or something else and then output that to XML using Excel tagsets?
I
>anticipate rerunning this a number of times as we update values, and thus
>would like to program in the conditional formatting into my SAS program
>rather than adding it once I pull the output into Excel.
>
>
>-Mary

Hi Mary
You can do this in proc report using formats, or using Call Define.
There is a good description in Ray and Sandy's paper here:

http://www.nesug.org/proceedings/nesug02/at/at004.pdf

which I think demonstrates what you are looking for.
Dianne R