The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

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

[ Edited ]

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. 


Accepted Solutions
Solution
‎03-11-2016 10:50 AM
Super Contributor
Posts: 394

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

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.

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 6,726

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

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;
Solution
‎03-11-2016 10:50 AM
Super Contributor
Posts: 394

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

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.

Contributor
Posts: 39

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

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

Post a Question
Discussion Stats
  • 3 replies
  • 747 views
  • 0 likes
  • 3 in conversation