topic Proc Report Stoplighting based on column value comparison in PDF in SAS Procedures

Proc Report Stoplighting based on column value comparison in PDF

tanya_henderson — Fri, 29 Jun 2018 13:25:18 GMT

I am working on a reconciliation report and I need to highlight the columns where values are unequal. I am using compute fields to define the formatting. I have tried with temp variables and without. I have attached both versions of my code and a screen shot of what the report looks like.

The issue is that my highlighting isn't working as expected. If the two columns do not match then both columns should be read. If the two columns do match then both columns should be green. If you look at the pdf screen shot, you can see that the columns are not highlighting as expected.

Re: Proc Report Stoplighting based on column value comparison in PDF

RW9 — Fri, 29 Jun 2018 13:16:37 GMT

And what is the problem? Start by presenting some test data in the form of a datastep, and the code which isn't doing what you want, based on that test data. Attached files, and bits of code without data really don't show us anything.

Re: Proc Report Stoplighting based on column value comparison in PDF

tanya_henderson — Tue, 03 Jul 2018 14:28:40 GMT

To achieve the highlighting that you want temporary variables are not necessary.
PROC REPORT reads in the data in a LEFT to RIGHT direction based on the placement of the variables on the COLUMN statement. In a compute block, if you reference a variable that is to the right of the compute block variable on the column statement, then that referenced variable is missing or blank and the IF condition will never be true.

In your compute blocks you are referencing values that PROC REPORT can't see yet. I recommend that you use one compute block. In the COMPUTE statement reference the last variable from the COLUMN statement, rcontcsdt. Within this compute block you use all of the IF and CALL DEFINE statements that you already have. However, you do have to change how to reference the column inside of the CALL DEFINE statement.

You need something like this:

   compute rcontcsdt;
       if c_guid1 ne rguid1 or missing (c_guid1) then 
           call define('c_guid1','style','style=[background=#fbbcff]');
       else if c_guid1 = rguid1 then 
           call define('c_guid1','style','style=[background=#bcffcd]');
 
     if c_guid2 ne rguid2 or missing (c_guid2)then 
           call define('c_guid2','style','style=[background=#fbbcff]');
       else if c_guid2 = rguid2 then 
           call define('c_guid2','style','style=[background=#bcffcd]');
 
    if c_guid3 ne rguid3 or missing (c_guid3) then 
           call define('c_guid3','style','style=[background=#fbbcff]');
       else if c_guid3 = rguid3 then 
           call define('c_guid3','style','style=[background=#bcffcd]');
...
   endcomp;

Please let me know if you have any further questions.
Thanks,
Jane Eslinger
SAS Technical Support