PROC REPORT: EXCEL Output Cell Color based on another Column Value

[craig159753]

Hi all,

I have produced an EXCEL output through SAS EG, I want to colour some of the cells YELLOW when another column has a certain value. Below is stripped down version of the data set

OBS        VALUE                     MATCH

1              Smith                           Y

2              Jones                          N

3              Edwards                      N

4              Adams                         Y

So the EXCEL output looks almost identical to the SAS data set, other than I DO NOT want to the print the MATCH column. I just need the VALUE column to be highlighted YELLOW when the MATCH column contains the letter N.

My code so far

ods listing close;
ods tagsets.excelxp file="\\NA1SASFILE1\CVSSandbox\&sysuserid.\AMG\COMPARE\compare.xls" 
                    style = sasweb 
                    options (sheet_interval = "none" sheet_name = "SUBJECT");

	proc report data = compare_t5 nowd
				      style(report)={background=white}
				      style(header)={background=lightblue}
				      style(column)={background=white};

		column observation value match

		/* Standard Columns Columns */
		define observation / display order=internal;
		define value       / display order=internal;
		define match       / noprint;
	
		/* Conditional Formatting */				
		compute value;
			if match = "N" then call define(_col_,'style','style={background=yellow}');
		endcomp;	
	run;

ods tagsets.excelxp close;
ods listing;

I can not use the format method because my data set contains thousands of records. And not all "Smiths" for example will need to be highlighted yellow.

any help would be great. 

[RW9]

Simplest way I can see, have a datastep which appears before the proc report.  This datastep would flag the data you need to change the color of and assign a color value:

data your_data;
  set your_data;
  if ... then flag=1;
  if ... then flag=2;
...
run;

Then in your proc report use the flag variable to calculate wether a row should be colored and what color:

proc report data=your_data;
 ...
  compute value;
    if flag=1 then call define(_col_,'style','style={background=yellow}');
    if flag=2 then call define(_col_,'style','style={background=red}');
  endcomp;
run;

[Tim_SAS · Accepted solution]

Remember, PROC REPORT builds each row from left to right, so in your example the value of 'match' is not known when 'value' is computed. To change the style of 'value' based on the value of 'match', the 'match' column must be to the left of the 'value' column. 

See these examples.

[craig159753]

Spot on! I realised this earlier, but thanks for solidifying it!!

[Cynthia_sas]

Hi:
It's not a great idea to append your new question to an already solved posting. And you've already asked this question in a different posting, haven't you? Isn't this the same question:
https://communities.sas.com/t5/General-SAS-Programming/PROC-REPORT-compare-col-vs-col/m-p/372100#M47...

And the same "left-to-right" rule of PROC REPORT applies.

Please go back and read the other posting. The correct answer has been posted several times.

cynthia

[srinidelite]

Apologies   😞 

Yes I will Move this question back to the mentioned post ! Thanks again for regulating me 🙂