topic PROC REPORT: EXCEL Output Cell Color based on another Column Value in ODS and Base Reporting

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

craig159753 — Fri, 11 Mar 2016 14:25:05 GMT

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.

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

RW9 — Fri, 11 Mar 2016 15:04:41 GMT

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;

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

Tim_SAS — Fri, 11 Mar 2016 15:46:08 GMT

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.

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

craig159753 — Fri, 11 Mar 2016 15:51:30 GMT

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

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

srinidelite — Mon, 03 Jul 2017 06:31:06 GMT

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

Cynthia_sas — Sat, 01 Jul 2017 15:54:03 GMT

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#M47530

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

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

srinidelite — Mon, 03 Jul 2017 06:29:43 GMT

Apologies 😞

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