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
Super User
Super User
Posts: 7,720

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!!

Contributor
Posts: 20

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

[ Edited ]
 
SAS Super FREQ
Posts: 8,820

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

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
Contributor
Posts: 20

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

Apologies   Smiley Sad 

 

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1646 views
  • 0 likes
  • 5 in conversation