Hi:
I don't quite understand your logic, but it seems to me that when the Program_number_2017 doesn't equal the Program_number_2016, your logic isn't catching that situation. You're also not catching the situation where the 2017 Score = the 2016 Score. As you can see, in my revised logic, the pink cell is being highlighted because of that condition. Right now, PROC REPORT is doing exactly what your logic is telling it to do. Since you never test for the case where Program_number_2017 does not = Program_number_2016 on that report row, the default color of WHITE is being used. I didn't bother with Excel output, since looking at the highlighting in the browser is faster and once the logic is worked out, it's easy to send the output back to Excel when the program does what you want.
I redid your logic a bit so there were more colors and now you can tell which cell was impacted by which IF and CALL DEFINE:
You might still need to refine the logic, but this is where I started. I got rid of the absolute column numbers, because I needed to see the variable names to work through it in my head. The only thing I needed to do, since your scores are numeric variables was that I had to add ".sum" to the end of the variable name. You could get rid of the ".sum" if you change the usage to DISPLAY in a DEFINE statement, but since I am not sure of what else you're doing in the program, I left the default usage for all the score variables.
Here's the revised program I used for the above screen shot.
proc report data=sample_data;
columns Manager Program_Number_2015 Score1_2015 Score2_2015 Score3_2015
Program_Number_2016 Score1_2016 Score2_2016 Score3_2016
Program_Number_2017 Score1_2017 Score2_2017 Score3_2017;
compute Score1_2017;
if Program_number_2017 = Program_number_2016 then do;
if Score1_2017.sum > Score1_2016.sum then do;
call define('Score1_2017.sum', 'style', 'style={background=lightgreen}');
end;
else if Score1_2017.sum < Score1_2016.sum then do;
call define('Score1_2017.sum', 'style', 'style={background=lightred}');
end;
else if Score1_2017.sum = Score1_2016.sum then do;
call define('Score1_2017.sum', 'style', 'style={background=pink}');
end;
end;
else if Program_number_2017 ne Program_number_2016 then do;
if Score1_2017.sum > Score1_2016.sum then do;
call define('Score1_2017.sum', 'style', 'style={background=lightyellow}');
end;
else if Score1_2017.sum < Score1_2016.sum then do;
call define('Score1_2017.sum', 'style', 'style={background=lightblue}');
end;
else if Score1_2017.sum = Score1_2016.sum then do;
call define('Score1_2017.sum', 'style', 'style={background=cxdddddd}');
end;
end;
endcomp;
compute Score2_2017;
if Program_number_2017 = Program_number_2016 then do;
if Score2_2017.sum > Score2_2016.sum then do;
call define('Score2_2017.sum', 'style', 'style={background=lightgreen}');
end;
else if Score2_2017.sum < Score2_2016.sum then do;
call define('Score2_2017.sum', 'style', 'style={background=lightred}');
end;
else if Score2_2017.sum = Score2_2016.sum then do;
call define('Score2_2017.sum', 'style', 'style={background=pink}');
end;
end;
else if Program_number_2017 ne Program_number_2016 then do;
if Score2_2017.sum > Score2_2016.sum then do;
call define('Score2_2017.sum', 'style', 'style={background=lightyellow}');
end;
else if Score2_2017.sum < Score2_2016.sum then do;
call define('Score2_2017.sum', 'style', 'style={background=lightblue}');
end;
else if Score2_2017.sum = Score2_2016.sum then do;
call define('Score2_2017.sum', 'style', 'style={background=cxdddddd}');
end;
end;
endcomp;
compute Score3_2017;
if Program_number_2017 = Program_number_2016 then do;
if Score3_2017.sum > Score3_2016.sum then do;
call define('Score3_2017.sum', 'style', 'style={background=lightgreen}');
end;
else if Score3_2017.sum < Score3_2016.sum then do;
call define('Score3_2017.sum', 'style', 'style={background=lightred}');
end;
else if Score3_2017.sum = Score3_2016.sum then do;
call define('Score3_2017.sum', 'style', 'style={background=pink}');
end;
end;
else if Program_number_2017 ne Program_number_2016 then do;
if Score3_2017.sum > Score3_2016.sum then do;
call define('Score3_2017.sum', 'style', 'style={background=lightyellow}');
end;
else if Score3_2017.sum < Score3_2016.sum then do;
call define('Score3_2017.sum', 'style', 'style={background=lightblue}');
end;
else if Score3_2017.sum = Score3_2016.sum then do;
call define('Score3_2017.sum', 'style', 'style={background=cxdddddd}');
end;
end;
endcomp;
run;
Hope this helps,
Cynthia
... View more
