Hi, I'm using SAS EG 7.15 that works off of SAS 9.4.3 I'm trying to create a report that merges two datasets. I have the merge fine but am having difficulty with traffic lighting. Basically, I have a column of managers with evaluation scores for the past 3 years for their program. I would like to highlight cells green if their score increased and red if they decreased. The problem is that the managers can change program from year to year and I want the highlighting to reflect scores compared to how the program did the previous year regardless of who the manager was. I have a sample dataset and the proc report code I'm using. It's not data I can provide publicly, so I apologize if the data I created isn't the most clear. It's a merged dataset of the two. the original two datasets are 1. a list of managers with the programs they oversee, and 2. a list of programs with scores going back some years. It works well if the manager hasn't changed programs, but does not work when he/she oversaw a different program the year before. data sample_data;
infile datalines dsd;
input 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 ;
datalines;
Person1,1,5,8,14,1,6,6,10,1,4,8,9
Person2,5,100,0,50,,,,,2,2,3,5
Person3,6,22,24,8,3,14,29,18,3,15,20,100
Person4,7,9,12,14,4,21,24,5,4,29,35,0
Person5,8,17,31,35,8,30,38,30,9,13,19,13
Person6,10,13,15,4,10,11,13,23,21,5,9,10
Person7,11,12,12,3,11,11,13,5,11,12,14,0
Person8,12,7,12,18,12,41,48,38,12,38,50,41
Person9,13,15,17,3,13,13,15,2,13,13,17,0
Person10,,,,,,,,,14,8,9,9
Person11,,,,,,,,,15,30,39,34
Person12,16,5,9,11,17,16,16,22,18,11,13,16
Person13,19,16,22,24,19,13,15,22,19,16,21,24
Person14,,,,,20,4,4,9,20,8,10,8
Person15,21,6,10,13,21,6,9,12,10,15,20,100
;
ods excel file='test.xlsx';
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 _c11_ > _c7_ and _c10_ = _c6_ then do;
call define('_c11_', 'style', 'style={background=#A9D08E}');
end;
if _c11_ < _c7_ and _c10_ = _c6_ then do;
call define('_c11_', 'style', 'style={background=#FF5050}');
end;
endcomp;
compute Score2_2017;
if _c12_ > _c8_ and _c10_ = _c6_ then do;
call define('_c12_', 'style', 'style={background=#A9D08E}');
end;
if _c12_ < _c8_ and _c10_ = _c6_ then do;
call define('_c12_', 'style', 'style={background=#FF5050}');
end;
endcomp;
compute Score3_2017;
if _c13_ > _c9_ and _c10_ = _c6_ then do;
call define('_c13_', 'style', 'style={background=#A9D08E}');
end;
if _c13_ < _c9_ and _c10_ = _c6_ then do;
call define('_c13_', 'style', 'style={background=#FF5050}');
end;
endcomp;
run;
ods excel close; I've realized that one of the flaws in my code is that if you look at Person2, they're currently overseeing Program2, but none of the current managers oversaw Program2 last year, so the comparison I try will never find Program2 scores from last year. I'm sorry if this is a little convoluted. But they want me to highlight based on program increase/decease but sort the programs by manager. Also, I'm also relatively new to SAS, so this might not be the best way to go about this. Finally, here's a clip of what the output should look like.
... View more
