topic How to color ENTIRE ROW(s) (created via ODS Excel procedure) based on the value(s) of ROW(S) in ODS and Base Reporting

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

inquistive — Fri, 28 Jan 2022 19:55:12 GMT

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.

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

Cynthia_sas — Sun, 30 Jan 2022 22:26:54 GMT

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

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

inquistive — Mon, 31 Jan 2022 17:47:35 GMT

@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?

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

PaigeMiller — Mon, 31 Jan 2022 18:40:10 GMT

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.