How to color code a cell based on another variable value in proc report

[NagendraBS]

Hi All,

I have a dataset with actual values and % values for each month as below 

SEG	AMOUNT	M1	M2	M3	M1%	M2%	M3%
ABC	100	2,200	2,420	2,662	97%	98%	100%
ABC	200	1,458	1,604	1,764	93%	95%	99%
ABC	300	1,345	1,480	1,627	96%	100%	99%
ABC	400	870	957	1,053	87%	97%	96%
ABC	500	554	609	670	68%	92%	99%
BCD	100	4,450	4,895	5,385	99%	98%	97%
BCD	200	1,278	1,406	1,546	93%	96%	100%
BCD	300	3,345	3,680	4,047	100%	88%	94%
BCD	400	1,879	2,067	2,274	99%	97%	97%
BCD	500	665	732	805	78%	91%	99%

I need to color code both the columns M1 & M1% cells based on M1% column values. Let say if M1%>95% then green (both M1 & M1% cells); else if m1% >90% then orange; else red. The same format should apply for all the subsequent columns  as below. 

I did try using the PROC REPORT to achieve the result but unable to get color format for both actuals and percentage values at the same time in proc report. Below is snippet used to achieved the partial results. 

Query:

 proc report data=work.freq_accts;

     column cas level M1 M2 M1% M1% M2% M1%%; 

define cas / display 'CAS';

        define level / display 'Plan Amt';

        define M1 / display '1Month' ;

        define M2 / display '2Months' ;

        define M3% / display '3Months' ;

define M1% / display 'M1%' ;

        define M2% / display 'M2%' ;

        define M3% / display 'M3%' ;

compute M1%;

          if M1% > .95 then 

         call define ('M1', "style","style={background=light green}");

          else if M1% > .90 then

         call define ('M1', "style","style={background=light orange}");

      else if M1% ne . then

         call define ('M1', "style","style={background=red}");

        endcomp;

        compute M2%;

          if M2% > .95 then

         call define ('M2', "style","style={background=light green}");

          else if M2% > .90 then

         call define ('M2', "style","style={background=light orange}");

      else if M2% ne . then

         call define ('M2', "style","style={background=red}");

        endcomp;

        compute M3%;

          if M3% > .95 then

         call define ('M1', "style","style={background=light green}");

          else if M3% > .90 then

         call define ('M1', "style","style={background=light orange}");

      else if M3% ne . then

         call define ('M1', "style","style={background=red}");

        endcomp;

    run;

Based on the above query able to get the results as below 

Now I wanted to color code the other columns as well based on the same condition. If not possible need to eliminate the % columns and keep the color format for actuals. 

Thanks in advance. 

Thanks,

Neel

[Cynthia_sas · Accepted solution]

Hi:

  I'm not sure that the code you posted produces the output you showed. As an example, your PROC REPORT step shows 

You show a CAS variable and a LEVEL variable and an M1%, M2% and M3% in the DEFINE statements, but you show M1% 3 times in the COLUMN statement and so I would expect your code to generate errors and no color coding or error prone color coding at all. Your output shows SEG and AMOUNT variables, but those aren't the labels in the PROC  REPORT, so that is problematic.

  Are your percent variables (which you have labelled M1%, M2% M3%) character or numeric? Based on your IF statements, my suspicion is that they are numeric, but I also suspect your code has other issues. Are your variables really named M1%, M2% and M3%? I would expect those variable names to generate errors in the SAS Log. Also, your M1, M2 and M3 variables are definitely numeric, but you don't show any SAS format in the DEFINE statement that would format the numbers with a COMMA format. In addition, your IF statement treats M1% as numeric with a value of .95 and .90, etc, but again, there's no format being used in the PROC REPORT step to format the decimal number as a percent.

  Is it possible to see your SAS log that's generating the  results you posted?  I would be curious to see how your reference to M1%, M2%, etc is being handled. 

  There are a few different ways to handle having the percent columns with the same background color as the M1, M2 and M3 variables. One way is to use another CALL DEFINE in the COMPUTE block and an alternate way is to use traffic-lighting with a user-defined format for the percent variables and then a simplified COMPUTE block for the M1, M2 and M3 values.

Cynthia

  With some fake data and different variable names, I think either of these examples will produce the report you want. Note that the second example has a simplified COMPUTE block, but the first example also works, although it is more verbose. ( I only typed a few rows of data since I had to make adjustments for the < and the % in the example data you posted.)

[NagendraBS]

Thanks a lot. Yes i did messed up in creating the fake data along with columns, but yeah got it right. 

[jeanleid]

Hi,

Try adding this additional syntax to your compute blocks:

if M1% > .95 then do; 

         call define ('M1', "style","style={background=light green}");

          call define (_row_,'style','style={background=light green}';

         end ;

Then repeat this code in the other percentage variables.

Good luck!