topic Re: Call Define to turn an entire row a color in ODS and Base Reporting

Call Define to turn an entire row a color

kcskaiser — Mon, 26 Jan 2015 18:02:37 GMT

The last column in my proc report is an error code value. If the error code is gt than 0 then I want then entire row set to a background color of red.

This is my define:

define last_error_code

/ display

'Error*Code';

This is my compute block:

compute last_error_code;

if last_error_code gt 0 then do;

call define(_row_, "style/merge", "style=[background=errcol. font_weight=bold]");

end;

endcomp;

The errcol. format is just white or red.

The problem is that only the the error code column and a couple of other columns are made red. Some stay the same color they were, others lose color completely, some turn a different color altogether. The style/merge is my latest attempt, I didn't expect it to work and of course it doesn't.

Several other rows have compute blocks using call defito set style values, several columns also have compute blocks that also use call define to set style values.

This one is simple.. how can I turn the entire row red regardless of what else is being done?

Thanks in advance everyone

Re: Call Define to turn an entire row a color

ballardw — Mon, 26 Jan 2015 19:41:31 GMT

Since you apparently have multiple style definitions the interaction is going to be hard to identify and resolve without the full code.

Re: Call Define to turn an entire row a color

kcskaiser — Mon, 26 Jan 2015 20:18:53 GMT

This is the full code of my 'Proc Report'.

proc report data = logs3

(where = (datepart(actual_start) ge &runmonth))

style(summary)=[background=yellow]

nowd split = '*' headskip;

column workflow_name id2 id

actual_start actual_start=startmin

actual_start2

session_timestamp session_timestamp=endmax

duration time

last_duration avg_duration

successful_rows last_rows

diff_from_last_rows avg_rows

row_diff_from_avg pct_diff_from_avg FAILED_SOURCE_ROWS

next_place last_error_code

;

define workflow_name / display noprint;

define id2 / group noprint;

define startmin / min f=datetime16. '*Start Time';

define endmax / max f=datetime16. '*End Time';;

define id / display style = [background=lightgreen

flyover=$idname.] '';

define actual_start / order noprint;

define actual_start2 / computed noprint f= datetime16. '*Start Time';

define session_timestamp / display noprint f= datetime16. '*End Time';

define duration / analysis noprint f= time12.2 '*Run Time';;

define time / computed f= time12.2 '*Run Time';

define last_duration / display f= time12.2 'Previous*Run Time';

define avg_duration / display f= time12.2 'Average*Run Time';

define successful_rows / analysis f= comma12. 'Rows*Processed';

define last_rows / analysis f= comma12. 'Rows*Last Month';

define diff_from_last_rows / analysis f= comma12. 'Diff From*Last Month';

define avg_rows / display f= comma12. 'Average*Rows';

define row_diff_from_avg / display noprint f= comma12. 'Diff From*Avg Month';

define pct_diff_from_avg / display f= percent6.2 '% Diff of*Avg Month';

define next_place / display noprint;

define failed_source_rows / analysis f= comma12. 'Failed*Rows';

define last_error_code / display 'Error*Code';

break after id2 / page summarize;

* Because I want to show all values, even those equal to the ;

* previous value, I used this: ;

* http://support.sas.com/kb/24/322.html ;

compute actual_start2;

if actual_start ne . then hold = actual_start;

actual_start2 = hold;

endcomp;

compute before ;

alltime = 0;

endcomp;

compute before _page_ /

style = {just=l FONTWEIGHT=bold color=blue

backgroundcolor=palegreen};

txt = upcase(compbl(cat(workflow_name,' --> ',scan(id2,2,' '))));

if workflow_name = scan(id2,2,' ') then txt = upcase(workflow_name);

line txt $75.;

endcomp;

compute id;

if id in ('A1','C10','D1','J7','K27','L6','Q_R','U15'

'W6A','W6B') then

call define(_row_, "style",

"style=[backgroundcolor=lightgrey]");

endcomp;

compute time;

time = endmax - startmin;

call define(_col_, "style", "style=[background=lightblue

font_weight=bold]");

endcomp;

compute pct_diff_from_avg;

call define(_col_, "style", "style=[color=tlight.

font_weight=bold]");

endcomp;

compute diff_from_last_rows;

call define(_col_, "style", "style=[color=tlight.

font_weight=bold]");

endcomp;

compute successful_rows;

call define(_col_, "style", "style=[background=wheat

font_weight=bold]");

endcomp;

compute last_rows;

call define(_col_, "style", "style=[background=KHAKI

font_weight=bold]");

endcomp;

compute after _page_ /

style = {just=l FONTWEIGHT=bold color=blue

backgroundcolor=white};

txt = cat('Next Up: ',next_place);

line txt $175.;

endcomp;

compute after id2;

*call define(_row_,"style", " style = [background=yellow]");

tottime = endmax-startmin;

days = floor((tottime)/(24*60*60));

hours = hour(tottime);

minutes = minute(tottime);

seconds = second(tottime);

theline = compbl(cat(put(days,z2.),' days ',

put(hours,z2.),' hours ',put(minutes,z2.),' minutes ',

put(seconds,z2.),' seconds'));

alltime = alltime+tottime;

adays = floor((alltime)/(24*60*60));

ahours = hour(alltime);

aminutes = minute(alltime);

aseconds = second(alltime);

thealine = compbl(cat(put(adays,z2.),' days ',

put(ahours,z2.),' hours ',put(aminutes,z2.),' minutes ',

put(aseconds,z2.),' seconds'));

line @1 'Group Time: ' +3 theline $char75.;

line @1 'Total Time: ' +4 thealine $char75.;

endcomp;

compute last_error_code;

if last_error_code gt 0 then do;

call define(_row_, "style/merge", "style=[background=errcol.

font_weight=bold]");

end;

endcomp;

run;

Re: Call Define to turn an entire row a color

Cynthia_sas — Mon, 26 Jan 2015 20:21:42 GMT

Hi:

Depending on the rest of your code, it is possible that even with STYLE/MERGE, what you specify in the CALL DEFINE will NOT override something previously set in another COMPUTE block for a different column. So it sounds to me like you must have other code that is preventing your highlighting from working. As you can see if you run this code that uses SASHELP.CLASS, the _ROW_ does work.

Cynthia

ods html file='c:\temp\hilite.html';

proc report data=sashelp.class nowd;
column name age sex height weight;
define name / order;
define age /display;
define sex / display;
define height / sum;
define weight / sum;
compute age;
    if substr(name,1,1) = 'J' and age = 12 then do;
      call define(_row_,'style','style={background=lightred}');
    end;
    else if age = 15 then do;
     call define(_row_,'style','style={background=lightyellow}');
    end;
endcomp;
run;
ods html close;

Re: Call Define to turn an entire row a color

kcskaiser — Mon, 26 Jan 2015 20:40:53 GMT

This is exactly what I want to happen. And I'm guessing your right about something else blocking it. I've included all the code from my proc report.

Thanks.

Re: Call Define to turn an entire row a color

Tim_SAS — Mon, 26 Jan 2015 20:57:40 GMT

PROC REPORT considers column styles to be more important than row styles, so if any of the calls to DEFINE that change the column style are executed for the same row, the column style will override the row style.

Re: Call Define to turn an entire row a color

kcskaiser — Mon, 26 Jan 2015 21:01:53 GMT

This does indeed seem to be what is happening. But surly SAS has a way around this.

Re: Call Define to turn an entire row a color

ballardw — Mon, 26 Jan 2015 23:16:14 GMT

Proc tabulate addresses this with the table option of Style_precedence but I don't see anything similar in Proc Report (as of 9.3 anyway).

Re: Call Define to turn an entire row a color

Cynthia_sas — Mon, 26 Jan 2015 23:57:26 GMT

The way I would work around it would be not to use _ROW_ and to change each of the variables on the row individually. Or, since it looks like there is quite a bit changing of rows and columns going on, I would probably try to reduce the scenarios that might "collide" against each other and code those in a more complex IF statement.

cynthia

Re: Call Define to turn an entire row a color

kcskaiser — Tue, 27 Jan 2015 14:52:34 GMT

This worked.. sort of. I thought I'd do it in a loop counting columns 1 to 13. This sounded promising until I tried it. The number of columns internal to SAS is apparently set by your columns statement not what is actually on the report. So it became 1 to 21. Using higher numbers causes all kinds of strange errors.

I can't get some columns to turn red no matter what I do. In my code they are time, successful_rows, and last_rows I have no idea why. Unless it has to do with them already being set to a color in a previous compute block. But that doesn't explain why it does override the color in the first column.

My compute block now looks like below:

compute last_error_code;

if last_error_code gt 0 then do i = 1 to 21;

call define(i, "style/merge", "style=[background=red

font_weight=bold]");

end;

endcomp;

All but three columns are now red, better but not right.

Thanks for this response it did help a lot.