The SAS Output Delivery System and reporting techniques

How do I do traffic highlighting when comparison values are in different rows

Reply
Senior User
Posts: 1

How do I do traffic highlighting when comparison values are in different rows

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.

sample output.PNG

 

Regular Contributor
Posts: 235

Re: How do I do traffic highlighting when comparison values are in different rows

split your data into 2 datasets and join them together.  Then your traffic light will be based upon individual rows.

SAS Super FREQ
Posts: 9,431

Re: How do I do traffic highlighting when comparison values are in different rows

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:

report_logic.png 

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

Ask a Question
Discussion stats
  • 2 replies
  • 97 views
  • 0 likes
  • 3 in conversation