Using SAS Studio 9.4. In the Results window the orange cell highlighting shows:
but in the Excel file the orange highlighted cells disappear:
Here is the ods and proc print statement:
ods excel
file="\\sasprod01\prisonersbay\clin_data\&prod.\&prot.\&outpath.\&prot._Discrepancy_Checks_&thedate._&thetime..xlsx"
style=Sapphire options(autofilter="ALL" sheet_name="" embedded_titles="yes" embed_titles_once="yes" );
ods excel options(sheet_name="EXDCC_010");
proc report data=diffall2 list ;
column record &keyvars &comparevars;
define record/ display "Record";
%do i=1 %to &numcomp;
compute %scan(&comparevars,&i,' ');
%let temp=%scan(&comparevars,&i,' ');
if record=2 and %scan(&comparevars,&i,' ') ne lag1( %scan(&comparevars,&i,' ')) then do;
call define ("&temp","style","style={background=orange}");
end;
else if record=3 then call define (_row_,"style","style={background=lightblue}");
else if record=4 then call define (_row_,"style","style={background=lightgreen}");
endcomp;
%end;
run;
Any ideas?
Just a note, there are no errors or warnings in the log. If I change the call define to _row_ instead of variable name in each compute statement, then the row highlighting works and does show in the Excel file...but for some reason, cell highlighting work in the SAS output but gets lost when creating the Excel file. :~(
Easiest to test would be a different color instead of orange, like Red, in the Proc Report call define code. Just in case your Excel doesn't like orange in that context.
There is no way anyone can attempt to duplicate your result without data or the definitions of way too many macro variables. Perhaps running the code with options MPRINT turned on would show something when this resolves that would give a hint.
if record=2 and %scan(&comparevars,&i,' ') ne lag1( %scan(&comparevars,&i,' ')) then do; call define ("&temp","style","style={background=orange}"); end;
It might also help to show the code that successfully uses orange in the "Orange=Value Difference"
Hi:
It's not the color. I suspect it is something else. In a quick test program, using the color name "orange" worked OK:
Note that since I took all the default usage definitions since age has a default usage of sum, the specification for age in the COMPUTE block needs to be "age.sum" and not just the variable name.
See what happens when I do not use the "var.stat" form of the reference in the CALL DEFINE:
The age column is NOT changed to orange AND there are error messages in the log.
Cynthia
Thanks for your responses. @ballardw --the code for the "Orange=Value Difference" is simply a title statement:
title8 justify=L bold height=9pt color="orange" F=Arial "Orange=Value Difference" ;
@Cynthia_sas Here is the proc report code which shows after the macro variables are resolved:
Just a note, there are no errors or warnings in the log. If I change the call define to _row_ instead of variable name in each compute statement, then the row highlighting works and does show in the Excel file...but for some reason, cell highlighting work in the SAS output but gets lost when creating the Excel file. :~(
In a data step before the proc report, I created a variable which indicated if there was a change in the data compared to the previous row then used that new variable in the logic in the compute block of the proc report and now the cell highlighting carries over to the Excel file.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.