Proc report / coloring cells based on the 95% CI statistical significance

[Cruise]

Hi SAS Community and @Cynthia_sas ,

My goal is to produce a colored table on the statistical significance of 95%CI of RR(rate ratio). Proc report code below produces wrong decision in coloring. Where am I making mistake? Any hints or suggestions highly appreciated.

Problem statement:

Rules for coloring are:

A.

If LCL (_c5_) and UCL (_c6_) are both greater than 1 then color the cells peachpuff;

If LCL (_c5_) and UCL (_c6_) are both less than 1 then color the lighgreen;

else color the cell white.

B.

Paint RR with a color selected based on the rules using LCL and UCL.

This logic was written in SAS codes below. However, rules appear not correctly applied in the final output as shown in the image.

IF (_c5_>1 AND _c6_>1) THEN do; svarp='style={background=peachpuff}'; END;
ELSE IF _c5_<1 AND _c6_<1 THEN do; svarp='style={background=lightgreen}'; END;
ELSE DO; svarp='style={background=white}'; END;

options nolabel; 
ODS SELECT ALL; 
proc report data=PAR_SEX_MAR11 out=e.check_columns;  
 column CANCER_TYPE row altsite col,SEX,(RR LCL UCL) dummycalc;
   define CANCER_TYPE/ " " group noprint;
   define row /" " group;
   define col /" " across NOZERO FORMAT=$Parameter.;
   define SEX / " " across FORMAT=$SEX.;
   define altsite/ ' ' group;
   define rr/analysis; 
   DEFINE dummycalc /NOPRINT; 
   define lcl/analysis /*NOPRINT*/; 
   define ucl/analysis /*NOPRINT*/; 
compute dummycalc;

IF (_c5_>1 AND _c6_>1) THEN do; svarp='style={background=peachpuff}'; END;
ELSE IF _c5_<1 AND _c6_<1  THEN do; svarp='style={background=lightgreen}'; END;
ELSE DO; svarp='style={background=white}'; END;

IF (_c8_>1 AND _c9_>1) THEN do; svarn='style={background=peachpuff}'; END;
ELSE IF _c8_<1 AND _c9_<1  THEN do; svarn='style={background=lightgreen}'; END;
ELSE DO; svarn='style={background=white}'; END;

IF (_c11_>1 AND _c12_>1) THEN DO; svara='style={background=peachpuff}'; END;
ELSE IF _c11_<1 AND _c12_<1 THEN DO; svara='style={background=lightgreen}'; END;
ELSE DO; svara='style={background=white}'; END;

IF (_c14_>1 AND _c15_>1) THEN DO; svarb='style={background=peachpuff}'; END;
ELSE IF (_c14_<1 AND _c15_<1) THEN DO; svarb='style={background=lightgreen}'; END;
ELSE DO; svarb='style={background=white}'; END;

IF (_c17_>1 AND _c18_>1) THEN DO; svarc='style={background=peachpuff}'; END;
ELSE IF (_c17_<1 AND _c18_<1) THEN DO; svarc='style={background=lightgreen}'; END;
ELSE DO; svarc='style={background=white}'; END;

IF (_c20_>1 AND _c21_>1) THEN DO; svard='style={background=peachpuff}'; END;
ELSE IF (_c20_<1 AND _c21_<1) THEN DO; svard='style={background=lightgreen}'; END;
ELSE DO; svard='style={background=white}'; END;

IF (_c23_>1 AND _c24_>1) THEN DO; svare='style={background=peachpuff}'; END;
ELSE IF (_c23_<1 AND _c24_<1) THEN DO; svare='style={background=lightgreen}'; END;
ELSE DO; svare='style={background=white}'; END;

IF (_c26_>1 AND _c27_>1) THEN DO; svarf='style={background=peachpuff}'; END;
ELSE IF (_c26_<1 AND _c27_<1) THEN DO; svarf='style={background=lightgreen}'; END;
ELSE DO; svarf='style={background=white}'; END;

IF (_c29_>1 AND _c30_>1) THEN DO; svarg='style={background=peachpuff}'; END;
ELSE IF (_c29_<1 AND _c30_<1) THEN DO; svarg='style={background=lightgreen}'; END;
ELSE DO; svarg='style={background=white}'; END;

IF (_c32_>1 AND _c33_>1) THEN DO; svarh='style={background=peachpuff}'; END;
ELSE IF (_c32_<1 AND _c33_<1) THEN DO; svarh='style={background=lightgreen}'; END;
ELSE DO; svarh='style={background=white}'; END;

IF (_c35_>1 AND _c36_>1) THEN DO; svari='style={background=peachpuff}'; END;
ELSE IF (_c35_<1 AND _c36_<1) THEN DO; svari='style={background=lightgreen}'; END;
ELSE DO; svari='style={background=white}'; END;

IF (_c38_>1 AND _c39_>1) THEN DO; svarj='style={background=peachpuff}'; END;
ELSE IF (_c38_<1 AND _c39_<1) THEN DO; svarj='style={background=lightgreen}'; END;
ELSE DO; svarj='style={background=white}'; END;

IF (_C41_>1 AND _c42_>1) THEN DO; svark='style={background=peachpuff}'; END;
ELSE IF (_c41_<1 AND _c42_<1) THEN DO; svark='style={background=lightgreen}'; END;
ELSE DO; svark='style={background=white}'; END;

IF (_C44_>1 AND _c45_>1) THEN DO; svarl='style={background=peachpuff}'; END;
ELSE IF (_c44_<1 AND _c45_<1) THEN DO; svarl='style={background=lightgreen}'; END;
ELSE DO; svarl='style={background=white}'; END;

IF (_C47_>1 AND _c48_>1) THEN DO; svarm='style={background=peachpuff}'; END;
ELSE IF (_c47_<1 AND _c48_<1) THEN DO; svarm='style={background=lightgreen}'; END;
ELSE DO; svarm='style={background=white}'; END;

IF (_C50_>1 AND _c51_>1) THEN DO; svarn='style={background=peachpuff}'; END;
ELSE IF (_c50_<1 AND _c51_<1) THEN DO; svarn='style={background=lightgreen}'; END;
ELSE DO; svarn='style={background=white}'; END;

IF (_C53_>1 AND _c54_>1) THEN DO; svaro='style={background=peachpuff}'; END;
ELSE IF (_c53_<1 AND _c54_<1) THEN DO; svaro='style={background=lightgreen}'; END;
ELSE DO; svaro='style={background=white}'; END;

IF (_C56_>1 AND _c57_>1) THEN DO; svarp='style={background=peachpuff}'; END;
ELSE IF (_c56_<1 AND _c57_<1) THEN DO; svarp='style={background=lightgreen}'; END;
ELSE DO; svarp='style={background=white}'; END;

IF (_C59_>1 AND _c60_>1) THEN DO; svarq='style={background=peachpuff}'; END;
ELSE IF (_c59_<1 AND _c60_<1) THEN DO; svarq='style={background=lightgreen}'; END;
ELSE DO; svarq='style={background=white}'; END;

IF (_C62_>1 AND _c63_>1) THEN DO; svarr='style={background=peachpuff}'; END;
ELSE IF (_c62_<1 AND _c63_<1) THEN DO; svarr='style={background=lightgreen}'; END;
ELSE DO; svarr='style={background=white}'; END;

IF (_C65_>1 AND _c66_>1) THEN DO; svars='style={background=peachpuff}'; END;
ELSE IF (_c65_<1 AND _c66_<1) THEN DO; svars='style={background=lightgreen}'; END;
ELSE DO; svars='style={background=white}'; END;

IF (_C68_>1 AND _c69_>1) THEN DO; svart='style={background=peachpuff}'; END;
ELSE IF (_c68_<1 AND _c69_<1) THEN DO; svart='style={background=lightgreen}'; END;
ELSE DO; svart='style={background=white}'; END;

IF (_C71_>1 AND _c72_>1) THEN DO; svaru='style={background=peachpuff}'; END;
ELSE IF (_c71_<1 AND _c72_<1) THEN DO; svaru='style={background=lightgreen}'; END;
ELSE DO; svaru='style={background=white}'; END;

IF (_C74_>1 AND _c75_>1) THEN DO; svarv='style={background=peachpuff}'; END;
ELSE IF (_c74_<1 AND _c75_<1) THEN DO; svarv='style={background=lightgreen}'; END;
ELSE DO; svarv='style={background=white}'; END;

call define('_c4_','style',svarp);   call define('_c5_','style',svarp); call define('_c6_','style',svarp);
call define('_c7_','style',svarn);   call define('_c8_','style',svarn); call define('_c9_','style',svarn);
call define('_c10_','style',svara); call define('_c11_','style',svara); call define('_c12_','style',svara);
call define('_c13_','style',svarb); call define('_c14_','style',svarb); call define('_c15_','style',svarb);
call define('_c16_','style',svarc); call define('_c17_','style',svarc); call define('_c18_','style',svarc);
call define('_c19_','style',svard); call define('_c20_','style',svard); call define('_c21_','style',svard);
call define('_c22_','style',svare); call define('_c23_','style',svare); call define('_c24_','style',svare);
call define('_c25_','style',svarf); call define('_c26_','style',svarf); call define('_c27_','style',svarf);
call define('_c28_','style',svarg); call define('_c29_','style',svarg); call define('_c30_','style',svarg);
call define('_c31_','style',svarh); call define('_c32_','style',svarh); call define('_c33_','style',svarh);
call define('_c34_','style',svari); call define('_c35_','style',svari); call define('_c36_','style',svari);
call define('_c37_','style',svarj); call define('_c38_','style',svarj); call define('_c39_','style',svarj);
call define('_C40_','style',svark); call define('_C41_','style',svark); call define('_C42_','style',svark);
call define('_C43_','style',svarl); call define('_C44_','style',svarl); call define('_C45_','style',svarl);
call define('_C46_','style',svarm); call define('_C47_','style',svarm); call define('_C48_','style',svarm);
call define('_C49_','style',svarn); call define('_C50_','style',svarn); call define('_C51_','style',svarn);
call define('_C52_','style',svaro); call define('_C53_','style',svaro); call define('_C54_','style',svaro);
call define('_C55_','style',svarp); call define('_C56_','style',svarp); call define('_C57_','style',svarp);
call define('_C58_','style',svarq); call define('_C59_','style',svarq); call define('_C60_','style',svarq);
call define('_C61_','style',svarr); call define('_C62_','style',svarr); call define('_C63_','style',svarr);
call define('_C64_','style',svars); call define('_C65_','style',svars); call define('_C66_','style',svars);
call define('_C67_','style',svart); call define('_C68_','style',svart); call define('_C69_','style',svart);
call define('_C70_','style',svaru); call define('_C71_','style',svaru); call define('_C72_','style',svaru);
call define('_C73_','style',svarv); call define('_C74_','style',svarv); call define('_C75_','style',svarv);
endcomp;
run;

mock data;

DATA HAVE1; 
   LENGTH PARAMETER $10.;
   INPUT PARAMETER $ SITES $ SEX Level1 RR LCL UCL;
CARDS;
PM25	LIVER	1	.	1.02	1.01	1.02
SMOKE_CAT	LIVER	1	2	0.86	0.79	0.93
SMOKE_CAT	LIVER	1	3	1.21	1.08	1.34
SMOKE_CAT	LIVER	1	4	1.32	1.1	1.54
SMOKE_CAT	LIVER	1	5	1.25	1.07	1.43
NO2	LIVER	1	.	1.41	1.14	1.68
SMOKE_CAT	LIVER	1	2	0.81	0.63	0.99
SMOKE_CAT	LIVER	1	3	1.6	1.23	1.97
SMOKE_CAT	LIVER	1	4	0.59	0.63	0.54
SMOKE_CAT	LIVER	1	5	0.98	0.96	0.99
PM25	LIVER	2	.	1.02	1.01	1.02
SMOKE_CAT	LIVER	2	2	0.86	0.79	0.93
SMOKE_CAT	LIVER	2	3	1.21	1.08	1.34
SMOKE_CAT	LIVER	2	4	1.32	1.1	1.54
SMOKE_CAT	LIVER	2	5	1.25	1.07	1.43
NO2	LIVER	2	.	1.41	1.14	1.68
SMOKE_CAT	LIVER	2	2	0.81	0.63	0.99
SMOKE_CAT	LIVER	2	3	1.6	1.23	1.97
SMOKE_CAT	LIVER	2	4	0.59	0.63	0.54
SMOKE_CAT	LIVER	2	5	0.98	0.96	0.99
PM25	STOMACH	1	.	1.88	1.35	2.4
SMOKE_CAT	STOMACH	1	2	0.34	0.23	0.45
SMOKE_CAT	STOMACH	1	3	2.06	1.44	2.68
SMOKE_CAT	STOMACH	1	4	0.98	0.96	0.99
SMOKE_CAT	STOMACH	1	5	2.25	1.53	2.97
NO2	STOMACH	1	.	2.34	1.57	3.11
SMOKE_CAT	STOMACH	1	2	2.43	1.61	3.25
SMOKE_CAT	STOMACH	1	3	0.58	0.56	0.59
SMOKE_CAT	STOMACH	1	4	2.62	1.7	3.54
SMOKE_CAT	STOMACH	1	5	2.71	1.74	3.68
PM25	STOMACH	2	.	1.88	1.35	2.4
SMOKE_CAT	STOMACH	2	2	0.34	0.23	0.45
SMOKE_CAT	STOMACH	2	3	2.06	1.44	2.68
SMOKE_CAT	STOMACH	2	4	0.98	0.96	0.99
SMOKE_CAT	STOMACH	2	5	2.25	1.53	2.97
NO2	STOMACH	2	.	2.34	1.57	3.11
SMOKE_CAT	STOMACH	2	2	2.43	1.61	3.25
SMOKE_CAT	STOMACH	2	3	0.58	0.56	0.59
SMOKE_CAT	STOMACH	2	4	2.62	1.7	3.54
SMOKE_CAT	STOMACH	2	5	2.71	1.74	3.68
;
RUN;

[PaigeMiller]

---

@Cruise wrote:

Hi SAS Community and @Cynthia_sas ,

My goal is to produce a colored table on the statistical significance of 95%CI of RR(rate ratio). Proc report code below produces wrong decision in coloring. Where am I making mistake? Any hints or suggestions highly appreciated.

Color wrong

---

Explain. What is wrong? What should the correct coloring be?

[Cruise]

Paige, hope my question is now made clearer.

[PaigeMiller · Accepted solution]

I see two problems (and there may be more)

When you have a value of 1, and the test is to see if the value is < 1, it may be that the formatted values is 1 (not less than 1) but the actual value is < 1.

Next problem is that variables svarp and svarn are computed in two different places, which of course will cause the wrong colors in one of the two places.

[Cynthia_sas]

Hi:
It would be easier for anyone to help you if you:
1) posted data for your test and
2) posted a program that can be used on your test data and
3) post ALL the code -- for example no one can run your program without getting FORMAT errors, since you did NOT provide the format

As it stands now, anyone who wants to help you has to use your fake data and then has to immediately edit the PROC REPORT code you've posted. Either post the data that goes with the program or post a program that works with the data.

Then, post the output that was produced with the fake data and identify exactly what is wrong. Typically, with the color coding and CALL DEFINE, the issue is that you are getting the absolute column numbers wrong.

This is my guess with your use of NOZERO. Remember that I explained, in the previous posting, that with NOPRINT you still had to account for "empty" columns, which would get an absolute column number. The same is true of using NOZERO. Just because you cannot see the empty columns with NOZERO does NOT prevent PROC REPORT from assigning an absolute column number. So, if you go back to my SASHELP.PRDSALE example, I think I can illustrate what is going wrong by using OUT=, as shown below:

  Understand how the absolute column numbers work WITHOUT using NOPRINT or NOZERO first. Because when you use NOPRINT or NOZERO, the column numbers do NOT change. As you can see from a simple example using a SASHELP dataset, the use of NOZERO improves the display in the results viewer, but does NOT change the absolute column numbers used.

  Hope this helps,

Cynthia

[Cruise]

@Cynthia_sas , I totally agree with you Cynthia. I apologize for inconsistency between mock data and the code provided. My puzzle was that everything worked well on my mock data but the actual one had problems. I'll make sure mock data and the code are consistent in the future.

@PaigeMiller , you were right on both claims. I corrected svarpn and svarp duplicates. And I also needed to convert SEX to numeric first in data step and use format in proc report. It's strange that I had an error "ERROR: SEX must use a character format" even after I converted SEX to numeric. Therefore, I still used (define SEX / " " across FORMAT=$SEX.;) while SEX in data was numeric. SAS gave no error and resulting output appears to be correct now. 

[PaigeMiller]

%macro colorcells;
     %do s=5 %to 74 %by 3;
        if (_c&s._>1 and _c%eval(&s+1)_>1 then
            svar&s ='style={background=peachpuff}';
        else if (_c&s._<1 and _c%eval(&s+1)_<1 then
            svar&s ='style={background=lightgreen}';
        else svar&s ='style={background=white}';
        call define ("_c%eval(&s-1)_",'style',svar&s);
        call define ("_c&s._",'style',svar&s);
        call define ("_c%eval(&s+1)_",'style',svar&s);
    %end;
%mend;

compute dummycalc;
    %colorcells
endcompute;

[Cruise]

VERY EFFICIENT! where was %eval defined?

[PaigeMiller]

It's a built in macro. You don't have to define it.

[Cynthia_sas]

Hi:  

  I would not recommend using any macro approach however, until you really, really understand how PROC REPORT is assigning values to the absolute column numbers and you really, really understand on all your data (not just on your test data) that you have the "pattern" of absolute numbers correct.

When something works on your test data, but not on your full data, that is generally a clue that you have made some miscalculation in the absolute column numbers.

So as tedious as it might be to use OUT= on the whole dataset with PROC REPORT, you would have then seen the whole universe of absolute column numbers and would have been able to desk check whether your logic was correct for all the columns.

  In my previous posting, I said there was a paper by Allison McMahill Booth that showed a routine that could help you and she showed the %EVAL macro function in her example. The challenge with her macro example is that you would have to take the NOPRINT and NOZERO columns into account in order to use her macro logic. Here's the paper, https://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf on pages 12, 13, 14 is the code. I did work up a macro example for your previous posting, but then when I realized that you were restructuring the data and had a different report in mind, I never posted it.

Cynthia

[Cruise]

after working with you few days, my understanding of absolute columns and how to locate them in the out=data significantly improved. i still would not use macro though. notepad+++ helps me so far. thanks Cynthia!

[PaigeMiller]

---

@Cynthia_sas wrote:

Hi:  

  I would not recommend using any macro approach however, until you really, really understand how PROC REPORT is assigning values to the absolute column numbers and you really, really understand on all your data (not just on your test data) that you have the "pattern" of absolute numbers correct.

So as tedious as it might be to use OUT= on the whole dataset with PROC REPORT, you would have then seen the whole universe of absolute column numbers and would have been able to desk check whether your logic was correct for all the columns.

---

But the original poster @Cruise did print out the OUT= data set, and so he knows what the column numbers are. I know the column numbers too, as I saw this OUT= data set.

And anyway, we didn't go to a macro approach until the non-macro code was working.

[Cynthia_sas]

Thanks for pointing that out. I was being overly cautious in my warning. Unfortunately, too many people jump straight to macro coding without understanding the underlying processes first.

Your macro solution was a good example of how to resolve the absolute column numbers and also create a unique name for the style variable for the CALL DEFINE -- once the underlying pattern was clear.

Cynthia