PROC REPORT coloring a field based on 2 other fields

[ikoba]

Hi,

I have a dataset with 5 fields.

study, site, subject, total_q, closed_q

I would like to color the closed_q field based on closed_q/total_q.

I tried this, but it is not works.

proc report data = sr.drs nowd;
columns study site subject total_q closed_q ;
define study/group ;
define site/group ;
define total_q / analysis style(header)=[background=olive];
define closed_q / analysis style(header)=[background=olive];

compute closed_q;
if round((closed_q/total_q)*100,0.01)<50 then call define (_col_,"style","STYLE=[BACKGROUND=RED]");
else if round((closed_q/total_q)*100,0.01)>=50 and round((closed_q/total_q)*100,0.01)<75 then call define (_col_,"style","STYLE=[BACKGROUND=ROSE]");
else if round((closed_q/total_q)*100,0.01)>=75 and round((closed_q/total_q)*100,0.01)<85 then call define (_col_,"style","STYLE=[BACKGROUND=ORANGE]");
else if round((closed_q/total_q)*100,0.01)>=85 and round((closed_q/total_q)*100,0.01)<95 then call define (_col_,"style","STYLE=[BACKGROUND=YELLOW]");
else if round((closed_q/total_q)*100,0.01)>=95 then call define (_col_,"style","STYLE=[BACKGROUND=GREEN]");
endcomp;

break after site / skip summarize style=[background=CXF2F2F2 font_weight=bold];
break after study / skip summarize style=[background=CXFFFFCC font_weight=bold];
run;

I tried another solution, I added one more field (pq) to the dataset with the value of closed_q/total_q:

proc sql;
 update sr.drs set pq=round((closed_q/total_q)*100,0.01);
quit;



proc report data = sr.drs nowd;
columns study site subject pq total_q closed_q ;
define study/group ;
define site/group ;

define pq / analysis mean noprint;

define total_q / analysis style(header)=[background=olive];
define closed_q / analysis style(header)=[background=olive];

compute closed_q;
if pq<50 then call define (_col_,"style","STYLE=[BACKGROUND=RED]");
else if pq>=50 and pq<75 then call define (_col_,"style","STYLE=[BACKGROUND=ROSE]");
else if pq>=75 and pq<85 then call define (_col_,"style","STYLE=[BACKGROUND=ORANGE]");
else if pq>=85 and pq<95 then call define (_col_,"style","STYLE=[BACKGROUND=YELLOW]");
else if pq>=95 then call define (_col_,"style","STYLE=[BACKGROUND=GREEN]");
endcomp;

break after site / skip summarize style=[background=CXF2F2F2 font_weight=bold];
break after study / skip summarize style=[background=CXFFFFCC font_weight=bold];
run;

I have the same issue, closed_q column is always red.

Could you please help me what is wrong here?

Thank you in advance!

[Cynthia_sas · Accepted solution]

Hi:
Without data, no one can test or tweak your code. Are there any messages in the log about variables being uninitialized? When you use an analysis variable, such as your PQ variable, you specify the MEAN statistic for the analysis. PROC REPORT has rules about how you must reference analysis variables in a COMPUTE block. You are required to use a compound name such as variable.statistic in the compute block. So for your PQ variable, it appears the correct reference should be PQ.MEAN in the COMPUTE block. You should be seeing a message in the SAS log. For your other variables, CLOSED_Q and TOTAL_Q, you've specified them as analysis variables, which have a default statistic of SUM, so the issue with using those variables is that PROC REPORT would expect the reference in a COMPUTE block to be CLOSED_Q.SUM and TOTAL_Q.SUM -- this is one of the rules of PROC REPORT -- if you search the forums, you should find many other postings on the topic of using compound names in PROC REPORT's COMPUTE block.
Also note that changing the color of cells only works in ODS destinations like RTF, PDF, HTML, etc. the use of the SKIP option in the break statement is ignored in ODS destinations. You won't see any messages, you just won't see a skipped line where you expect it. The PROC REPORT documentation shows which options are LISTING-only options such as SKIP, DOL, DUL, OL, UL, LS, PS, FLOW, etc.
Cynthia

[ikoba]

Hi Cynthia,
Sorry, next time I will add test data and screenshot. The problem was that I missed the .MEAN. I tried with .SUM, but not with .MEAN.
Thank you so much!