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

Hi, this should be easy - or so I thought.

I want to compare 2 cells, and highlight one of them if it is larger then the second.

For example, I want to highlight the weight if it is higher then the max weight.

ProductWeightMaxWeight
A45
A65
B34
343
C73

Here is what I have as code, that does not work -  I'm not sure how to use the compute block to create a different style:

proc report data=work.data nowd;

column product weight maxweight;

define weight / computed ;

    compute weight;

    if weight < maxweight then do;

        call define('Weight','style','style={background=White}');

     end;

     else if weight> maxweight then do;

        call define('Weight','style','style={background=yellow}');

     end;

     endcomp;

run;

I think I need to take a class on Proc Report. Thanks for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ


Hi:

  SASHELP.CLASS did not have a variable called MAXWEIGHT, so Tim's code creates that variable as a "temp" variable. If you are going to do a comparison between WEIGHT and MAXWEIGHT, then you will need your COMPUTE block to be for MAXWEIGHT. PROC REPORT places variables on the report from LEFT to RIGHT. So, if this is your COLUMN statement:

column product weight maxweight;

then, at the point in time when WEIGHT is being placed on the report row, PROC REPORT has not yet placed MAXWEIGHT on the report row. So if you did your test in the COMPUTE block for WEIGHT, I suspect that every row would be yellow because MAXWEIGHT does not have a value yet. If MAXWEIGHT exists in the data, then the proper place for you to do your test is in a COMPUTE block for MAXWEIGHT.

  The code below shows 3 different methods:

1) Method 1: has DISPLAY usage for WEIGHT and MAXWEIGHT

2) Method 2: has SUM usage for WEIGHT and MAXWEIGHT (notice how this changes the reference method in the COMPUTE block and CALL DEFINE statement

3) Method 3: has MEAN usage for WEIGHT and MAXWEIGHT (because I wanted to show how the summary line for Averages could be created with an RBREAK statement)

  I changed the logical operators to be LE and GT (which I prefer over <, > or <= or >=), so that the equal situation (if WEIGHT=MAXWEIGHT) would be covered.

cynthia

   

data fakedata;
  length product $10;
  infile datalines;
  input product $ weight maxweight;
return;
datalines;
A 4 5
A 6 5
B 3 4
3 4 3
C 7 3
;
run;
    
ods _all_ close;
title; footnote;
  
ods html file='c:\temp\hilight.html';
proc report data=work.fakedata nowd;
title 'Method 1: All items are DISPLAY usage';
column product weight maxweight;
define product / display;
define weight / display ;
define maxweight / display;
    ** use LE to take care of equal situation instead of just LT;
    compute maxweight;
       if weight le maxweight then do;
         call define('Weight','style','style={background=White}');
       end;
       else if weight gt maxweight then do;
         call define('Weight','style','style={background=yellow}');
       end;
     endcomp;

run;

     

proc report data=work.fakedata nowd;
title 'Method 2: All numeric items are SUM usage (default)';
column product weight maxweight;
define product / display;
define weight / sum ;
define maxweight / sum;

    compute maxweight;
       if weight.sum le maxweight.sum then do;
         call define('Weight.sum','style','style={background=White}');
       end;
       else if weight.sum gt maxweight.sum then do;
         call define('Weight.sum','style','style={background=yellow}');
       end;
     endcomp;

run;

     

proc report data=work.fakedata nowd;
title 'Method 3: Numeric items are MEAN usage with summary line at bottom';
  column product weight maxweight;
  define product / display;
  define weight / mean ;
  define maxweight / mean;
  rbreak after / summarize;
    compute after;
       product='Averages';
    endcomp;
    compute maxweight;
       if weight.mean le maxweight.mean then do;
         call define('Weight.mean','style','style={background=White}');
       end;
       else if weight.mean gt maxweight.mean then do;
         call define('Weight.mean','style','style={background=yellow}');
       end;
     endcomp;

run;
ods html close;

View solution in original post

6 REPLIES 6
Tim_SAS
Barite | Level 11

Try this. By default numeric variables such as "weight" are analysis variables and so have statistics associated with them. In a compute block you would refer to the statistic. For example "weight.sum".  In this case, however, you don't want statistics, you just want to display the value, so you should specify "weight" as a display variable.

proc report data=sashelp.class nowd;

column name weight;

define name--weight / display;

compute before;

  maxweight = 0;

endcomp;

compute weight;

  if weight < maxweight then do;

    call define('Weight','style','style={background=green}');

  end;

    else do;

      if weight > maxweight then do;

        call define('Weight','style','style={background=yellow}');

        maxweight = weight;

      end;

  end;

endcomp;

run;

Cynthia_sas
SAS Super FREQ

Hi:

  As Tim recommends, defining WEIGHT would allow you to do the comparisons easier. I am confused about one thing though. Does the variable MAXWEIGHT exist on every observation in the dataset or are you computing MAXWEIGHT?

  Just curious. If MAXWEIGHT already exists in your data, you might have to change your code slightly from what Tim shows.

cynthia

And, we do offer a report class that covers ODS, PROC TABULATE and PROC REPORT.

https://support.sas.com/edu/schedules.html?ctry=us&id=284#outline

Kimberley
Calcite | Level 5

Hi Cynthia,  maxweight does already exist in my dataset, maybe that's why I can't seem to make Tim's code work.

How woudl I need to change the code?

Thank you for your help, I will look into taking the class, thanks for the info.

Kim

Cynthia_sas
SAS Super FREQ


Hi:

  SASHELP.CLASS did not have a variable called MAXWEIGHT, so Tim's code creates that variable as a "temp" variable. If you are going to do a comparison between WEIGHT and MAXWEIGHT, then you will need your COMPUTE block to be for MAXWEIGHT. PROC REPORT places variables on the report from LEFT to RIGHT. So, if this is your COLUMN statement:

column product weight maxweight;

then, at the point in time when WEIGHT is being placed on the report row, PROC REPORT has not yet placed MAXWEIGHT on the report row. So if you did your test in the COMPUTE block for WEIGHT, I suspect that every row would be yellow because MAXWEIGHT does not have a value yet. If MAXWEIGHT exists in the data, then the proper place for you to do your test is in a COMPUTE block for MAXWEIGHT.

  The code below shows 3 different methods:

1) Method 1: has DISPLAY usage for WEIGHT and MAXWEIGHT

2) Method 2: has SUM usage for WEIGHT and MAXWEIGHT (notice how this changes the reference method in the COMPUTE block and CALL DEFINE statement

3) Method 3: has MEAN usage for WEIGHT and MAXWEIGHT (because I wanted to show how the summary line for Averages could be created with an RBREAK statement)

  I changed the logical operators to be LE and GT (which I prefer over <, > or <= or >=), so that the equal situation (if WEIGHT=MAXWEIGHT) would be covered.

cynthia

   

data fakedata;
  length product $10;
  infile datalines;
  input product $ weight maxweight;
return;
datalines;
A 4 5
A 6 5
B 3 4
3 4 3
C 7 3
;
run;
    
ods _all_ close;
title; footnote;
  
ods html file='c:\temp\hilight.html';
proc report data=work.fakedata nowd;
title 'Method 1: All items are DISPLAY usage';
column product weight maxweight;
define product / display;
define weight / display ;
define maxweight / display;
    ** use LE to take care of equal situation instead of just LT;
    compute maxweight;
       if weight le maxweight then do;
         call define('Weight','style','style={background=White}');
       end;
       else if weight gt maxweight then do;
         call define('Weight','style','style={background=yellow}');
       end;
     endcomp;

run;

     

proc report data=work.fakedata nowd;
title 'Method 2: All numeric items are SUM usage (default)';
column product weight maxweight;
define product / display;
define weight / sum ;
define maxweight / sum;

    compute maxweight;
       if weight.sum le maxweight.sum then do;
         call define('Weight.sum','style','style={background=White}');
       end;
       else if weight.sum gt maxweight.sum then do;
         call define('Weight.sum','style','style={background=yellow}');
       end;
     endcomp;

run;

     

proc report data=work.fakedata nowd;
title 'Method 3: Numeric items are MEAN usage with summary line at bottom';
  column product weight maxweight;
  define product / display;
  define weight / mean ;
  define maxweight / mean;
  rbreak after / summarize;
    compute after;
       product='Averages';
    endcomp;
    compute maxweight;
       if weight.mean le maxweight.mean then do;
         call define('Weight.mean','style','style={background=White}');
       end;
       else if weight.mean gt maxweight.mean then do;
         call define('Weight.mean','style','style={background=yellow}');
       end;
     endcomp;

run;
ods html close;

ArtC
Rhodochrosite | Level 12

As an aside notice in Cynthia's code that the compute block is for MAXWEIGHT and not WEIGHT.  In the original posted code you are attempting to use the variable MAXWEIGHT in the WEIGHT compute block.  Since MAXWEIGHT is to the right of WEIGHT in the COLUMN statement, MAXWEIGHT cannot yet be addressed.  Remember that for a compute block associated with a given report item you may only address report items to the left of it in the COLUMN statement.

Kimberley
Calcite | Level 5

Thank you everyone for your help, I understand a lot more about how the compute block works now, and how to traffic light better.

Cynthia, you were absolutely right - the whole column did turn yellow.  I'm looking into taking the class, I wish it was available on e-learning, so much easier to convince my manager to take e-learning classes then having to travel to the US (I'm in Canada). The web class would be great, but the schedule is so disruptive to our daily operations, but is still probably our best option.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1437 views
  • 6 likes
  • 4 in conversation