How to color ENTIRE ROW(s) (created via ODS Excel procedure) based on the value(s) of ROW(S)

[inquistive]

Hi Experts,
Scenario:
Dataset(decision) contains following info:

Name      Score     Grade     DecisionMemo
Michael   80           B-            Internship
Michelle  77          C+            Mentoring
Andy       99          A+             Fulbright
Nancy    73           C               Mentoring
Candy   72           C-              Coaching

1.The entire row(s) must be highlighted with a RED background color if the Grade is C(C+, C-), YELLOW if the grade is B(B+, B-), and GREEN if Grade is A(A+, A-).
2.The entire ROW must be highlighted with a PURPLE background color if the Name begins with C(andy), Grade is C- and DecisionMemo is 'Coaching'.

This is what I have now:
ods _all_ close;
ods excel file = 'c:\myreport\decision.xlsx';
options (
sheet_name='Memo'
protect_worksheet='on'
autofilter='all'
frozen_headers='on'
orientation='landscape'
screen_resolution_scale_factor='display-scale-factor'
);
proc print data = decision label noobs; run;
style(header)= {just=c foreground=black};
var name  / style(data)={just=l} style(header)={background=light green};

var score / style(data)={just=l} style(header)={background=light green};
var grade / style(data)={just=l} style(header)={background= light brown};
var decisionmemo / style(data)={just=c} style(header)={background= yellow};
ods excel close;

Any relevant links or code (so kind of you, thank you very much!) is greatly appreciated.

[Cynthia_sas]

Hi:

  You will need to switch to PROC REPORT if you want to highlight an entire row. For example, consider this output created with ODS EXCEL and PROC REPORT, as shown below:

There are a few different ways to code the COMPUTE block. I prefer to test a variable on the report row at the end of the row, after all the variables have been placed on the row. PROC REPORT moves from left to right as it is placing items on the report row, so it is easier to do a test on the last item on a row if you want to change the style of the entire row or you want to touch multiple columns on the same row. I make a helper variable called SVAR that holds the style override for each row based on the VALUE of AGE. Note that I defined AGE as a display usage item. If I had taken the default usage of ANALYSIS for AGE, then the IF statement would have needed to change the reference in the IF to AGE.SUM -- but in this case, I was not doing any calculations with AGE or summary lines, so DISPLAY usage allowed me to simplify the IF statement.

  PROC PRINT will let you change the color of one variable at a time, but you can't change the color of a whole row in PROC PRINT -- that is one of the examples where PROC REPORT shines.

Cynthia

[inquistive]

@cynthia.

Thanks for the quick tip. It seems to work but ignores the following options: 
options (
sheet_name='Memo'
protect_worksheet='on'
autofilter='all'
frozen_headers='on'
orientation='landscape'
screen_resolution_scale_factor='display-scale-factor'
);

Is there a way to keep these options in place?

[PaigeMiller]

You can use these options with PROC REPORT just as you tried earlier with PROC PRINT. These are ODS EXCEL options, not PROC PRINT options.