The SAS Output Delivery System and reporting techniques

Call Define to turn an entire row a color

Reply
Contributor
Posts: 25

Call Define to turn an entire row a color

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

Grand Advisor
Posts: 9,679

Re: Call Define to turn an entire row a color

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

Contributor
Posts: 25

Re: Call Define to turn an entire row a color

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;

SAS Super FREQ
Posts: 8,640

Re: Call Define to turn an entire row a color

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;

Contributor
Posts: 25

Re: Call Define to turn an entire row a color

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.

Super Contributor
Posts: 394

Re: Call Define to turn an entire row a color

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.

Contributor
Posts: 25

Re: Call Define to turn an entire row a color

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

Grand Advisor
Posts: 9,679

Re: Call Define to turn an entire row a color

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).

SAS Super FREQ
Posts: 8,640

Re: Call Define to turn an entire row a color

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

Contributor
Posts: 25

Re: Call Define to turn an entire row a color

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.

Post a Question
Discussion Stats
  • 9 replies
  • 1122 views
  • 6 likes
  • 4 in conversation