BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AlexeyS
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;

 

 

--
Paige Miller

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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;

 

 

--
Paige Miller
FreelanceReinh
Jade | Level 19

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
SAS Super FREQ

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
Diamond | Level 26

@BrunoMueller I like it!

--
Paige Miller
Cynthia_sas
SAS Super FREQ

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:

Cynthia_sas_0-1649862526305.png

  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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4655 views
  • 6 likes
  • 5 in conversation