topic Re: Proc report shows highlighted cells in Results but not in Excel file in ODS and Base Reporting

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

brentphillippi — Wed, 31 Jul 2024 20:55:42 GMT

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?

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

ballardw — Wed, 31 Jul 2024 22:07:01 GMT

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"

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

Cynthia_sas — Thu, 01 Aug 2024 12:23:28 GMT

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

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

brentphillippi — Thu, 01 Aug 2024 15:38:41 GMT

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}");