Conditional Traffic Lighting Based on the Value of Another Variable

[Mgarret]

Hi all--

I'm having some issues with with traffic lighting a variable based on the value of another variable.  I have two variables KPI and Lift. 

KPI	Lift
Response Rate	-20.38%
Conversion Rate	N/A
Yield Rate	N/A
VideoQual RR	-22.10%
Cost Per Response	29.72%
Cost Per Sale	N/A

Lift is a numeric variable but I have applied a format to it so if the cell is  missing (.) then N/A.

1. For the rows in KPI which contain 'Response Rate' 'Conversion Rate' 'Yield Rate' 'VideoQual RR' the traffic lighting should go like this:

--If lift is greater than 0 then Green colored  font

--If lift is less than 0 than red  colored  font

--if lift is missing (.) then black  colored  font

2. For the rows in KPI which contain 'Cost Per Response' 'Cost Per Sale'  the traffic lighting should go like this:

--If lift is greater than 0 then red colored  font

--If lift is less than 0 than green colored  font

--if lift is missing (.) then black  colored  font

A

So the column lift should have two different conditional tragic lighting logic  applied.

There is something up with my syntax  because I can't seem to get the missing(.)/N/A value black.

Below is my program.  Any assistance is appreciated.

proc report data=Final nowd;

  column KPI lift;

  define KPI / display

  style(column)={ just=right  font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD  cellwidth=1.4in}

  style(header)={ just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define lift  / display

  style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

compute lift;

if KPI in('Response Rate' 'Conversion Rate' 'Yield Rate' ' VideoQual RR') and lift < 0 then

call define(_col_,"style","style=[foreground=red]");

else if KPI in('Response Rate' 'Conversion Rate' 'Yield Rate' 'VideoQual RR') and lift > 1 then

call define(_col_,"style","style=[foreground=green]");

else if KPI in('Response Rate' 'Conversion Rate' 'Yield Rate' ' VideoQual RR') and  lift = . ;

call define(_col_,"style","style=[foreground=black]")

else if KPI in('Cost Per Response' 'Cost Per Sale') and lift > 1 then

call define(_col_,"style","style=[foreground=red]");

else if KPI in('Cost Per Response' 'Cost Per Sale') and lift < 0 then

call define(_col_,"style","style=[foreground=green]");

else if KPI in('Cost Per Response' 'Cost Per Sale') and lift = .;

call define(_col_,"style","style=[foreground=black]");

endcomp;

run;

[Reeza]

Your semicolon is in the wrong place and you're missing a then.

else if KPI in('Response Rate' 'Conversion Rate' 'Yield Rate' ' VideoQual RR') and  lift = .  then

call define(_col_,"style","style=[foreground=black]");

[Mgarret]

Hi Reeza--

Thanks! I am still getting a funny output. It looks like this.

KPI	Lift
Response Rate	-20.38%
Conversion Rate	N/A
Yield Rate	N/A
VideoQual RR	-22.10%
Cost Per Response	29.72%
Cost Per Sale	N/A

--N/A should be black

--Cost Per Response should be green.

proc report data=Final nowd;

  column

KPI Control Test lift Statistically_Significant_95 percent_Significant;

  define KPI / display

  style(column)={ just=right  font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD  cellwidth=1.4in}

  style(header)={ just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define lift  / display

  style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

compute lift;

if KPI in('Response Rate' 'Conversion Rate' 'Yield Rate' 'VideoQual RR') and lift < 0 then

call define(_col_,"style","style=[foreground=red]");

else if KPI in('Response Rate' 'Conversion Rate' 'Yield Rate' ' VideoQual RR') and lift > 1 then

call define(_col_,"style","style=[foreground=green]");

else if KPI in('Response Rate' 'Conversion Rate' 'Yield Rate' ' VideoQual RR') and  lift = .  then

call define(_col_,"style","style=[foreground=black]");

else if KPI in('Cost Per Response' 'Cost Per Sale') and lift > 1 then

call define(_col_,"style","style=[foreground=red]");

else if KPI in('Cost Per Response' 'Cost Per Sale') and lift < 0 then

call define(_col_,"style","style=[foreground=green]");

else if KPI in('Cost Per Response' 'Cost Per Sale') and  lift = .  then

call define(_col_,"style","style=[foreground=black]");

endcomp;

run;

[Reeza]

Are your percentages numbers like 45.2 or are they 0.452 formatted as percentages, and as such less than 1, between 0 and 1.

Also, the condition <0 will include missing observations.

[Cynthia_sas · Accepted solution]

Hi:

  I am not sure of your logic. In one post, you say that for Cost Per Response a LIFT of .2972, that the number should be Green, but you said

"2.  For the rows in KPI which contain 'Cost Per Response' 'Cost Per Sale'  the traffic lighting should go like this:

--If lift is greater than 0 then red colored  font

--If lift is less than 0 than green colored  font

--if lift is missing (.) then black  colored  font "

  Whether the number is .2972 or 29.72, either of those numbers is GT 0. But,  according to your rules, that number for LIFT for Cost Per Response should be RED, because both of those numbers are GT 0. .2972 is GT 0 but is LT 1 so if your number is .2972, that will be one problem, as Reeza pointed out. But you did not code GT 0 in your code, instead you coded GT 1.

  See the attached screen shot, that shows the results of running the code below. The DATA step program has 3 different logic scenarios -- it shows 2 things that could cause issues 1) putting the test for missing in the wrong place and 2) using GT 1 instead of GT 0. I made a subset of your data with just a few KPI values -- enough to test your color coding, but not the full list. Each value for KPI has a positive example, a negative example and a missing example, to see how the logic works.

cynthia

*** code to test;

data final;
  length KPI $20 test1_for_lift test2_for_lift test3_for_lift $20;
  infile datalines dlm=',' dsd;
  input rownum KPI $ Lift Lift2;
  ** 2) first set of tests;
  **    as proc report is coded now;
  test1_for_lift = 'unknown1';
  if lift < 0 then test1_for_lift = 'LT 0';
  else if lift > 1 then test1_for_lift = 'GT 1';
  else if lift = . then test1_for_lift = 'MISSING CODED 3';

  ** 2) second set of tests;
  test2_for_lift = 'unknown2';
  if lift = . then test2_for_lift = 'TEST MISSING FIRST';
  else if lift < 0 then test2_for_lift = 'LT 0';
  else if lift > 1 then test2_for_lift = 'GT 1';
  else if lift = 0 then test2_for_lift = 'ZERO';
  else if lift > 0 and lift < 1 then test2_for_lift = 'POSITIVE FRACTION';
  else if lift = 1 then test2_for_lift = 'EXACTLY 1';
     
  ** 3) third set of tests;
  test3_for_lift = 'unknown3';
  if lift = . then test3_for_lift = 'TEST MISSING FIRST';
  else if lift LE 0 then test3_for_lift = 'LE 0';
  else if lift > 0 then test3_for_lift = 'GT 0';

  label test1_for_lift = 'test1: Like PROC REPORT test missing last and gt 1'
        test2_for_lift = 'test2: ALL Possible Values 5 conditions'
  test3_for_lift = 'test3: Simplified Test 3 conditions';
return;
datalines;
1.1, "Response Rate", -.2038, -20.38
1.2, "Response Rate", .2038, 20.38
1.3, "Response Rate", .,.
2.1, "Conversion Rate", -.7777, -77.77
2.2, "Conversion Rate", .7777, 77.77
2.3, "Conversion Rate", .,.
3.1,"Cost Per Response", -.2972 ,-29.72
3.2,"Cost Per Response", .2972, 29.72
3.3, "Cost Per Response", ., .
4.1,"Test Zero", 0,0
4.2,"Test GT 1", 1.234,123.4
4.3,"Test LT 0", -1.234,-123.4
4.4,"Test Pos Fraction", .1234,.1234
4.5,"Test Neg Fraction", -.1234,-12.34
4.6,"Test Exactly 1", 1, 1
;
run;

ods listing close;
ods html file='c:\temp\testlogic.html';
proc print data=final label;
  title 'testing logic';
  var rownum kpi lift test:;
run;

    
proc report data=Final nowd
  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' 
                 FONT_WEIGHT =BOLD background=cxcccccc};
  title 'KPI Lift Report';
  column rownum KPI lift lift2;
  define rownum / order;
  define KPI / display
  style(column)={ just=right  font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD  cellwidth=1.4in};

  define lift  / display
  style(column)={just=center font_size=10pt font_face= 'Arial Narrow' cellwidth=.7in};

  define lift2  / display
  style(column)={just=center font_size=10pt font_face= 'Arial Narrow' cellwidth=.7in};
   

** code has GT 1;
compute lift;
if KPI in('Response Rate' 'Conversion Rate' 'Yield Rate' 'VideoQual RR') then do;
  if lift = .  then call define(_col_,"style","style=[foreground=black]");
  else if lift < 0 then call define(_col_,"style","style=[foreground=red]");
  else if lift > 1 then call define(_col_,"style","style=[foreground=green]");
end; 
  
if KPI in('Cost Per Response' 'Cost Per Sale') then do;
  if lift = . then call define(_col_,"style","style=[foreground=black]");
  else if lift > 1 then call define(_col_,"style","style=[foreground=red]");
  else if lift < 0 then call define(_col_,"style","style=[foreground=green]");
end;
endcomp;

** Code has GT 0;  
compute lift2;
if KPI in('Response Rate' 'Conversion Rate' 'Yield Rate' 'VideoQual RR') then do;
  if lift2 = .  then call define(_col_,"style","style=[foreground=black]");
  else if lift2 < 0 then call define(_col_,"style","style=[foreground=red]");
  else if lift2 > 0 then call define(_col_,"style","style=[foreground=green]");
end; 
      
if KPI in('Cost Per Response' 'Cost Per Sale') then do;
  if lift2 = . then call define(_col_,"style","style=[foreground=black]");
  else if lift2 > 0 then call define(_col_,"style","style=[foreground=red]");
  else if lift2 < 0 then call define(_col_,"style","style=[foreground=green]");
end;
endcomp;
run;
ods html close;

[Mgarret]

Sorry Cynthia that it took me so long to thank you for this. This is totally correct. Thanks!