topic Re: Do Conditional Highlighting - ODS HTML in ODS and Base Reporting

Do Conditional Highlighting - ODS HTML

LAtwood — Wed, 05 Jan 2011 15:03:02 GMT

I'm trying to do some conditional formatting with ODS HTML. I found this proc template code to do conditional formatting, but I'm not sure if this is what I can use to accomplish what I need to do. I have a list of data that lists out names of projects by users. I just want to highlight the first project of each user. Here is the conditional formatting code. (It creates a checkerboard effect)

Proc template;
define tagset tagsets.format;
Parent=tagsets.htmlcss;
define event doc;
start:
put '' NL;
finish:
put "" NL;
end;
define event doc_head;
start:
put "" NL;
put VALUE NL;
finish:
put "" NL;
put " " NL;
put " $A1:$F20" NL;
put " " NL;
put " =mod(row(),3)=MOD(COLUMN(),3)" NL;
put " " NL;
put " " NL;
put " " NL;
put " =MOD(ROW(),4)=0" NL;
put " " NL;
put " " NL;
put "" NL;
put "" NL;
put "" NL;
end;
end;
run;

I've also found where you can run a PROC REPORT and do a BREAK to create a line and summarizes the data, but I do not want this.

Any input and help with this would be appreciated.

Re: Do Conditional Highlighting - ODS HTML

Cynthia_sas — Wed, 05 Jan 2011 16:46:01 GMT

Hi:
Personally, I think PROC REPORT is probably easier than going the template route. I have the following example that uses some fake flight data -- it shows 3 different types of highlighting using PROC REPORT (alternate "greenbar", highlighting based on a condition in one column and highlighting the first flight number for a destination (also conditional highlighting).

When I use ODS MSOFFICE2K and open the resulting HTML file with Excel, the conditional highlighting is respected.

cynthia
[pre]
ods listing close;

** Step 1: Proc Report Greenbar;
** Create alternating colors by using a temporary;
** variable and a CALL DEFINE.;
ods listing close;
** First, make some data;
data weekdata;
infile datalines dlm = ',' pad;
input FlightNumber Date : date9.
Origin $ Destination $
Mail Freight Boarded;
format Date mmddyy10.;
return;
datalines;
387,03MAR2002,LGA,CPH,465,421,138
387,04MAR2002,LGA,CPH,395,217, 81
387,05MAR2002,LGA,CPH,393,304,142
387,07MAR2002,LGA,CPH,546,204,131
387,08MAR2002,LGA,CPH,415,367,150
387,09MAR2002,LGA,CPH,363,297,128
389,03MAR2002,LGA,CPH,462,323,138
389,04MAR2002,LGA,CPH,293,244,122
389,05MAR2002,LGA,CPH,324,345,242
389,07MAR2002,LGA,CPH,247,406,121
389,08MAR2002,LGA,CPH,321,348,130
389,09MAR2002,LGA,CPH,333,499,124
182,03MAR2002,LGA,YYZ,311,278,137
182,04MAR2002,LGA,YYZ,327,160,160
182,05MAR2002,LGA,YYZ,461,317,125
182,06MAR2002,LGA,YYZ,443,360,122
182,07MAR2002,LGA,YYZ,388,569,155
182,08MAR2002,LGA,YYZ,343,387,164
182,09MAR2002,LGA,YYZ,477,192,140
;
run;

ods msoffice2k file='c:\temp\rep_altbar1.xls' style=sasweb;
proc report data=work.weekdata nowd
style(summary)={font_weight=bold};
title '1 Highlight Every Other Row';
column destination date flightnumber freight;
define destination / group;
define date / order;
define flightnumber / order;
define freight / sum;
break after destination / summarize;
compute destination;
tempvar + 1;
if mod(tempvar,2) gt 0 then
call define(_ROW_,'STYLE','style={background=#99ffcc}');
endcomp;
run;
ods msoffice2k close;
title;

** Step 2: Alternate Proc Report Method;
** Set background of whole row based on value of freight.;
ods msoffice2k file='c:\temp\rep_hilite2.xls' style=sasweb;
proc report data=work.weekdata nowd
style(lines)=Header
style(summary)={font_weight=bold};
title '2 Highlight the Whole Row if';
title2 'Freight is GE 300 and LE 500';
column destination date flightnumber freight;
define destination / group;
define date / order;
define flightnumber / order;
define freight / sum;
break after destination / summarize;
compute freight;
if freight.sum ge 300 and freight.sum le 500 then
call define(_ROW_,'STYLE','style={background=pink}');
endcomp;
compute after destination;
line ' ';
endcomp;
run;
ods msoffice2k close;
title;

** Step 3: Highlight the first flight for each destination;
ods msoffice2k file='c:\temp\rep_hilite_onerow3.xls' style=sasweb;
proc report data=work.weekdata nowd
style(lines)=Header
style(summary)={font_weight=bold};
title '3 Highlight the Whole Row if it is the first flightnumber';
column destination flightnumber date freight;
define destination / group;
define flightnumber / order;
define date / order;
define freight / sum;
break after destination / summarize;
** if flightnumber is an ORDER variable, it will be blank on every row;
** except the first row;
compute flightnumber;
if flightnumber gt ' ' then
call define(_ROW_,'STYLE','style={background=pink}');
endcomp;
compute after destination;
line ' ';
endcomp;
run;
ods msoffice2k close;
ods listing;
title;
[/pre]

Re: Do Conditional Highlighting - ODS HTML

LAtwood — Thu, 06 Jan 2011 18:26:21 GMT

Thanks Cynthia

Let me ask you this. Is there a way to hide the summary row? I just want the report to display the data, no summaries, but still want it to do the breaks.

Re: Do Conditional Highlighting - ODS HTML

Cynthia_sas — Thu, 06 Jan 2011 18:39:40 GMT

Hi:
Yes, remove the option "summarize" from the BREAK statement:
[pre]
break after destination / ;
[/pre]

And, decide whether or not you want the blank line from the LINE statement in the COMPUTE/ENDCOMP block.

cynthia

Re: Do Conditional Highlighting - ODS HTML

Ksharp — Fri, 07 Jan 2011 01:15:04 GMT

Set them missing value
Or using $varying. format.

Ksharp

Re: Do Conditional Highlighting - ODS HTML

sss — Sat, 22 Jan 2011 10:30:05 GMT

hiiii

i need help in this senario

============================================================
ANUM JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOTAL_FAIL
============================================================
4299 40 20 27 37 25 32 26 24 22 28 17 33 27.58
4264 30 30 36 15 17 27 27 30 25 27 14 24 25.17
4246 19 24 40 18 32 18 20 19 24 16 14 25 22.42
4281 16 18 31 23 17 24 27 20 19 26 13 27 21.75
41786 21 43 33 21 20 21 26 21 11 11 13 14 21.25
4080 16 19 21 16 13 23 13 18 21 17 27 20 18.67
4262 17 14 23 16 30 22 20 18 26 11 9 10 18.00
1789 19 19 14 10 21 19 24 17 18 24 9 14 17.33
4315 20 11 28 17 18 19 11 22 17 25 5 11 17.00
4297 16 11 17 15 14 14 15 16 28 12 6 26 15.83
4279 17 15 16 11 14 14 8 15 14 15 8 8 12.92
4042 15 6 8 6 3 9 13 19 8 3 5 10 8.75
41787 4 4 9 9 3 8 8 19 16 8 10 7 8.75
2377 10 6 11 4 13 11 4 8 9 4 4 7 7.58
4241 6 5 9 5 7 7 6 12 6 7 3 4 6.42
============================================================

Hello Friends

I want to conditionally highlight the cell.
In col(ANUM) is number for asset and values in MONTH(Jan,Feb....DEC) column are No. of Failures
with respective to month and TOTAL_FAIL in mean on all month.

I want to highlight the values whose values are greater then TOTAL_FAIL With respect to ANUM col as Red or some other color.

ex:-in 1st row i want to highlight the values 40,37,32,28,33
in 2nd col i want to highlight the values 30,30,30,27,27,27

------------------------------------------------------------------------------------
/* Code goes here but , i m not getting actual output */
-------------------------------------------------------------------------------
I 'll be very thankful for you suggestion
ODS HTML FILE='TEMP.HTML';
goption reset=all ctext=black ftext="verdana/bold" htext=1 ;
title1 color=cx003366 justify=left j=c font=verdana bold height=3 "C CHART for # Failures for Asset by Month";;
title2 color=darkblue font=verdana justify=c height=1 justify=RIGHT font=verdana bold italic "Run Date: %sysfunc(date(),mmddyy.)";
title3 j=left '____________________________________________________________________________';
title4 color=black font=verdana bold j=left height=2
'Description :' FONT=VERDANA ' Control charts, also known as Shewhart charts, in statistical process control are tools used to determine whether a
manufacturing or business process is in a state of statistical control or not. In this report C-chart is used to monitor number of failures for an' ;
title5 color=black font=verdana j=left height=1 ' Assumption : 1) The inspection procedure is same for each sample and is carried out consistently from sample to sample.';
title6 color=black font=verdana j=left height=1' 2) Number of failures follows Poisson distribution';
title7 j=left '____________________________________________________________________________';
symbol v = dot color = GREEN ;
goptions cback=white vsize=5.0 hsize=8 ftext=verdana xpixels=600 ypixels=300;

option font="verdana" ;

axis1 value=(height=0.6 color=DARKBLUE font="verdana/bold") label=(height=1 color=ORANGE font="verdana/bold" 'Month');;
axis2 value=(height=0.6 color=DARKBLUE font="verdana/bold") label=(height=1 angle=90 color=ORANGE font="verdana/bold");

proc report data=_demo4 nowd split='/'
style(report)={font_face= ' verdana ' font_size=2 bordercolor=white}
style(Header)=[ background=cx003366 foreground =white bordercolor=darkblue FONT_FACE='verdana' FONT_SIZE=2.5 FONT_WEIGHT=Bold CELLSPACING=1 ]
style (column)={just=center font_face='verdana' FONT_WEIGHT=Bold bordercolor=white background=white font_size= 2 };
column ORGID SITEID LOCATION ASSETNUM DESCRIPTION JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ;
define JAN/display "JAN";
define FEB/display "FEB";
define MAR/display "MAR";
define APR/display "APR";
define MAY/display "MAY";

compute JAN ;
if JAN OR FEB OR MAR OR APR GE TOTAL_FAILURES then do;
call define(_COL_,'STYLE','STYLE=[foreground=red]');
end;
else
call define(_COL_,'STYLE','STYLE=[foreground=black]');

endcomp;
run;
ODS HTML CLOSE;

Re: Do Conditional Highlighting - ODS HTML

Cynthia_sas — Sat, 22 Jan 2011 11:45:37 GMT

You have not answered the questions I asked in this post, nor addressed the issues that I pointed out here:
http://support.sas.com/forums/thread.jspa?threadID=12619&tstart=0

Also, there is no need to make the same posting multiple times. It only makes your posting(s) hard to follow:
http://support.sas.com/forums/thread.jspa?threadID=12444&tstart=0
http://support.sas.com/forums/thread.jspa?threadID=12624&tstart=0
http://support.sas.com/forums/thread.jspa?threadID=12620&tstart=0