Proc report shows highlighted cells in Results but not in Excel file

[brentphillippi]

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?

[ballardw]

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"

[Cynthia_sas]

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

[brentphillippi]

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:

PROC REPORT DATA=WORK.DIFFALL2 LS=132 PS=60 SPLIT="/" CENTER ;

COLUMN ( record SUBJECT LABNAM VISIT ACCSNNUM TESTNAME TRMTYP STUDYID SPEC ASSEDAT ORRES ORRESU TSTSTAT REASND CMNT );

DEFINE record / DISPLAY FORMAT= OLDNEW55. WIDTH=55 SPACING=2 RIGHT "Record" ;

DEFINE SUBJECT / DISPLAY FORMAT= $11. WIDTH=11 SPACING=2 LEFT "SUBJECT" ;

DEFINE LABNAM / DISPLAY FORMAT= $7. WIDTH=7 SPACING=2 LEFT "LABNAM" ;

DEFINE VISIT / DISPLAY FORMAT= $7. WIDTH=7 SPACING=2 LEFT "VISIT" ;

DEFINE ACCSNNUM / DISPLAY FORMAT= $7. WIDTH=7 SPACING=2 LEFT "ACCSNNUM" ;

DEFINE TESTNAME / DISPLAY FORMAT= $3. WIDTH=3 SPACING=2 LEFT "TESTNAME" ;

DEFINE TRMTYP / DISPLAY FORMAT= $1. WIDTH=1 SPACING=2 LEFT "TRMTYP" ;

DEFINE STUDYID / DISPLAY FORMAT= $14. WIDTH=14 SPACING=2 LEFT "STUDYID" ;

DEFINE SPEC / DISPLAY FORMAT= $4. WIDTH=4 SPACING=2 LEFT "SPEC" ;

DEFINE ASSEDAT / DISPLAY FORMAT= $9. WIDTH=9 SPACING=2 LEFT "ASSEDAT" ;

DEFINE ORRES / DISPLAY FORMAT= $2. WIDTH=2 SPACING=2 LEFT "ORRES" ;

DEFINE ORRESU / DISPLAY FORMAT= $1. WIDTH=1 SPACING=2 LEFT "ORRESU" ;

DEFINE TSTSTAT / DISPLAY FORMAT= $2. WIDTH=2 SPACING=2 LEFT "TSTSTAT" ;

DEFINE REASND / DISPLAY FORMAT= $1. WIDTH=1 SPACING=2 LEFT "REASND" ;

DEFINE CMNT / DISPLAY FORMAT= $1. WIDTH=1 SPACING=2 LEFT "CMNT" ;

COMPUTE TRMTYP / CHAR LENGTH=1 ;

if record = 2 and TRMTYP ne lag1(TRMTYP) then call define("TRMTYP", "style", "style={background=orange}");

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;

COMPUTE STUDYID / CHAR LENGTH=14 ;

if record = 2 and studyid ne lag1(studyid) then call define("studyid", "style", "style={background=orange}");

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;

COMPUTE SPEC / CHAR LENGTH=4 ;

if record = 2 and spec ne lag1(spec) then call define("spec", "style", "style={background=orange}");

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;

COMPUTE ASSEDAT / CHAR LENGTH=9 ;

if record = 2 and assedat ne lag1(assedat) then call define("assedat", "style", "style={background=orange}");

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;

COMPUTE ORRES / CHAR LENGTH=2 ;

if record = 2 and orres ne lag1(orres) then call define("orres", "style", "style={background=orange}");

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;

COMPUTE ORRESU / CHAR LENGTH=1 ;

if record = 2 and orresu ne lag1(orresu) then call define("orresu", "style", "style={background=orange}");

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;

COMPUTE TSTSTAT / CHAR LENGTH=2 ;

if record = 2 and tststat ne lag1(tststat) then call define("tststat", "style", "style={background=orange}");

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;

COMPUTE REASND / CHAR LENGTH=1 ;

if record = 2 and reasnd ne lag1(reasnd) then call define("reasnd", "style", "style={background=orange}");

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;

COMPUTE CMNT / CHAR LENGTH=1 ;

if record = 2 and cmnt ne lag1(cmnt) then call define("cmnt", "style", "style={background=orange}");

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;

RUN;

[brentphillippi · Accepted solution]

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.  :~(

[brentphillippi]

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.