proc report : if/else inside compute statement

[AlexeyS]

Hello,

I want to create excel and add some color to the rows based on some conditions.

I use ods excel and proc report.

proc report data=test;
column stn avg1 avg2 avg3;
define stn / order;
define avg1 /display;
define avg2 / display;
define avg3 / display;
compute avg2;
   if avg2>avg3 then do;
     call define('avg2', "style",  "style=[background=lightyellow]" );
end;
endcomp;
run;

This code doesn't add color, but if i replace avg3 by number, the code works fine. Please explain what i did wrong.

Thank you

[PaigeMiller · Accepted solution]

PROC REPORT uses variables left-to-right in the COLUMNS statement, so any compute statement for avg2 cannot use avg3, it can only use variables to the left of avg2 or avg2 itself. You can create an alias to use AVG3 to the left of AVG2, and tell PROC REPORT to not include the alias in the output table.

proc report data=test;
column stn avg1 avg3=avg3_alias avg2 avg3;
define stn / order;
define avg3_alias/noprint;
define avg1 /display;
define avg2 / display;
define avg3 / display;
compute avg2;
   if avg2>avg3_alias then do;
     call define('avg2', "style",  "style=[background=lightyellow]" );
end;
endcomp;
run;

[FreelanceReinh]

Hello @AlexeyS,

I can't test it with ODS Excel (no Excel here), but with ODS HTML your code does add color -- however not in the intended way: As Paige has already explained, a COMPUTE block for variable AVG2 can only refer to other items to the left of AVG2 in the COLUMN statement. AVG3 is missing at the time when the COMPUTE block is executed, so the condition is met whenever avg2>.. Try changing the code to

compute avg3;

This minor change works for me with ODS HTML.

[BrunoMueller]

As already mentioned a compute block on a specific column can only access the report-items that are to the left of it within the COLUMN statement.

As a best practice I always create a _dummy computed column at the very end, this allows me to access any other report-item.

Adding the NOPRINT option will avoid printing, but this computed column can also be used to display values for debugging purposes.

proc report data=test;
  column stn avg1 avg2 avg3 _dummy;
  define stn / order;
  define avg1 /display;
  define avg2 / display;
  define avg3 / display;
  define _dummy / computed noprint;

  compute _dummy;

    if avg2 > avg3 then do;
      call define('avg2', "style",  "style=[background=lightyellow]" );
    end;
  endcomp;
run;

[PaigeMiller]

@BrunoMueller I like it!

[Cynthia_sas]

Hi:

  As explained, the "left-to-right" rule is at play here with PROC REPORT. There have been many previous posts about how you have to work around that to do comparisons. In brief your choices are:

1) use a NOPRINT version of all the variables in the comparison so they appear before the variable in the compute block (as already described), but this is not always do-able with your data.

2) change the variable for the test by either:

  -- in your example doing the test in the COMPUTE block for AVG3 or

  -- sticking a fake or dummy variable at the end of the report row where you can do ALL the tests you need based on the values in the row.

  Here's some code that illustrates the last 2 approaches, using some fake data:

data test;
  infile datalines dlm=',';
  input Stn $ avg1 avg2 avg3;
datalines;
NE,11111,11111,11110
NW,22222,11119,22223
SE,33333,33333,33331
SW,44444,44444,44444
;
run;
   
proc report data=test;
title '1) Move test to compute block for avg3';
column stn avg1 avg2 avg3;
define stn / order;
define avg1 /display;
define avg2 / display;
define avg3 / display;
compute avg3;
   if avg2>avg3 then do;
     call define('avg2', "style",  "style=[background=lightyellow]" );
   end;
endcomp;
run;
   
proc report data=test;
title '2) Create "fake" variable at end of row for testing';
column stn avg1 avg2 avg3 fakevar;
define stn / order;
define avg1 /display;
define avg2 / display;
define avg3 / display;
define fakevar / computed noprint;
compute fakevar;
   fakevar=1;
   if avg2>avg3 then do;
     call define('avg2', "style",  "style=[background=lightyellow]" );
   end;
   else if avg1>avg2 then do;
     call define('Stn', "style",  "style=[background=lightgreen]" );
     call define('avg2', "style",  "style=[background=lightgreen]" );
   end;
   else if avg1=avg3 then do;
     call define('avg1', "style",  "style=[background=lightblue]" );
     call define('avg2', "style",  "style=[background=lightblue]" );
   end;
endcomp;
run;

And the results are:

  The handy thing about making the "fake" helper variable is that by the time PROC REPORT has built the entire report row, you can test ALL the variables in the row in a COMPUTE block. I will frequently make a fake helper variable like shown in #2 report if I have a LOT of tests and a LOT of traffic lighting to do.

Cynthia

ps...my #2 is like Bruno's example, just a different helper variable name.