Hi,
I am trying to generate a report, where in a comparison is done between two different Adverse Events terms.
Status column is displayed as below::
if AETERM and o_AETERM, then "No change"
if AETERM ne o_AETERM then "Updated".
Is there a way, I can highlight all the AETERM and o_AETERM if the status is "Updated" otherwise it remains as it is.
I tried using the following code, but this is highlighting all the AETERM columns.
Any suggestions?
/***Export the data to xls***/
ods escapechar='~';
ods listing close;
ods tagsets.ExcelXP file = "Documents/Output/report_20180711.xls" style=sasweb;
ods tagsets.ExcelXP options(sheet_name='REPORT' orientation='landscape'
autofit_height='yes' FitToPage='yes'
Pages_FitWidth='1' Pages_FitHeight='300' zoom='100' absolute_column_width='20,20,20,20,20'
embedded_titles='yes' embedded_footnotes='yes' );
proc report data=current.final nowd headline headskip missing spacing=5 split='*';
column SUBJECT SITE AETERM o_AETERM STATUS;
define SUBJECT / left flow "ID";
define SITE / left flow "Site";
define AETERM / left flow "Adverse Event";
define o_AETERM / left flow "Previous Adverse Event";
define STATUS / left flow "Status";
compute AETERM;
if AETERM ne o_AETERM then call define(_col_, "style", "Style={background=red}");
endcomp;
run;
ods tagsets.ExcelXP close;
ods listing close;
Hi:
Using SASHELP.CLASS as a test, I find the CALL DEFINE statement works as described:
See the above example. There are 5 CALL DEFINE statements and 5 places in the report are highlighted in Excel when I use TAGSETS.EXCELXP, as follows:
--CALL DEFINE A highlights the HEIGHT column based on the value of HEIGHT and SEX. The color is either lightyellow or default background of white
--CALL DEFINE B highlights the NAME column based on the value of HEIGHT and SEX. NAME is lightgreen if the condition is true, otherwise, the background is the default color of white.
--CALL DEFINE C highlights the SEX column based on the value of HEIGHT and SEX. The color of the SEX column is lightgreen, if the condition is true; otherwise, the background is the default.
--WEIGHT background color is controlled by CALL DEFINE D and CALL DEFINE E. The background color of WEIGHT is lightred if the first condition is true; and the background color of WEIGHT is lightyellow if the second condition is true.
If you run my code (posted below), the color coding should work for you using TAGSETS.EXCELXP as shown in my screen shot. If the color coding works for you with my program and SASHELP.CLASS, but doesn't work with your program and your data, then that means either your condition is wrong or your CALL DEFINE is wrong or something about your logic or code or data needs to be adjusted.
Hope this helps.
Cynthia
Here's my complete code:
ods tagsets.excelxp file='c:\temp\test_hilite.xml' style=htmlblue
options(embedded_titles='yes');
proc report data=sashelp.class;
title 'Using TAGSETS.EXCELXP and Call Define';
column age name sex height weight;
define name / order;
define age / order;
define sex / display;
define height /display;
define weight / display;
compute height;
if sex = 'M' and height ge 68 then do;
call define(_col_,'style','style={background=lightyellow}');
call define('name','style','style={background=lightgreen}');
call define('sex','style','style={background=lightgreen}');
end;
endcomp;
compute weight;
if weight gt 125 and height lt 68 then do;
call define(_col_,'style','style={background=lightred}');
end;
else if weight gt 125 and height ge 68 then do;
call define(_col_,'style','style={background=lightyellow}');
end;
endcomp;
run;
ods tagsets.excelxp close;
Hi:
It is a bit hard to read your posting because of the first line. However, I believe that your issue is with the fact that your code violates the "left to right" rule of PROC REPORT. For example, if I have a column statement like this:
COLUMN VARA VARB VARC VARD;
Then in the COMPUTE block for VARB, I cannot compare VARB to VARC -- that is a rule violation.
Proc REPORT lays down each report row, one row at a time, working from left to right. So first, PROC REPORT puts down the column headers. Then PROC REPORT puts down VARA and at the point in time when VARA is placed on the report row, PROC REPORT has NO visibility of the values for VARB, VARC or VARD. Likewise for VARB -- at the point in time when VARB is placed on the report row, PROC REPORT now has visibility of VARA (which is to the left of VARB) and has visibility of VARB, but has NO visibility of the values for VARC or VARD.
This means if your COMPUTE block is trying to compare AETERM with o_AETERM:
compute AETERM;
if AETERM ne o_AETERM then call define(_col_, "style", "Style={background=red}");
endcomp;
Then this won't work if AETERM is to the left of o_AETERM on the COLUMN statement. Something like this world work:
compute o_AETERM;
if AETERM ne o_AETERM then call define('AETERM', "style", "Style={background=red}");
endcomp;
because when PROC REPORT is in the COMPUTE block for o_AETERM, it now has both values to compare. And if you change your CALL DEFINE statement, you can "touch" the value for AETERM in the COMPUTE block for o_AETERM.
Also, I assume that AETERM and o_AETERM are character variables with a usage of DISPLAY or else your variable reference is incorrect. If they are numeric variables, then you would need to use a different variable reference of the form:
variable_name.stat_name
such as
salary.sum or donate.mean
(depending on the statistic listed on the DEFINE Statement). I know there are previous postings on this topic like this one:
I'm sure that a search will reveal more.
Also, an added note, I see some options that will be ignored by ODS because they are LISTING-only options for PROC REPORT, so options like: HEADLINE HEADSKIP SPACING=5 in the PROC REPORT statement and FLOW in the DEFINE statement will NOT be used by any destination other than LISTING -- you may as well delete these options.
Cynthia
Hi:
Using SASHELP.CLASS as a test, I find the CALL DEFINE statement works as described:
See the above example. There are 5 CALL DEFINE statements and 5 places in the report are highlighted in Excel when I use TAGSETS.EXCELXP, as follows:
--CALL DEFINE A highlights the HEIGHT column based on the value of HEIGHT and SEX. The color is either lightyellow or default background of white
--CALL DEFINE B highlights the NAME column based on the value of HEIGHT and SEX. NAME is lightgreen if the condition is true, otherwise, the background is the default color of white.
--CALL DEFINE C highlights the SEX column based on the value of HEIGHT and SEX. The color of the SEX column is lightgreen, if the condition is true; otherwise, the background is the default.
--WEIGHT background color is controlled by CALL DEFINE D and CALL DEFINE E. The background color of WEIGHT is lightred if the first condition is true; and the background color of WEIGHT is lightyellow if the second condition is true.
If you run my code (posted below), the color coding should work for you using TAGSETS.EXCELXP as shown in my screen shot. If the color coding works for you with my program and SASHELP.CLASS, but doesn't work with your program and your data, then that means either your condition is wrong or your CALL DEFINE is wrong or something about your logic or code or data needs to be adjusted.
Hope this helps.
Cynthia
Here's my complete code:
ods tagsets.excelxp file='c:\temp\test_hilite.xml' style=htmlblue
options(embedded_titles='yes');
proc report data=sashelp.class;
title 'Using TAGSETS.EXCELXP and Call Define';
column age name sex height weight;
define name / order;
define age / order;
define sex / display;
define height /display;
define weight / display;
compute height;
if sex = 'M' and height ge 68 then do;
call define(_col_,'style','style={background=lightyellow}');
call define('name','style','style={background=lightgreen}');
call define('sex','style','style={background=lightgreen}');
end;
endcomp;
compute weight;
if weight gt 125 and height lt 68 then do;
call define(_col_,'style','style={background=lightred}');
end;
else if weight gt 125 and height ge 68 then do;
call define(_col_,'style','style={background=lightyellow}');
end;
endcomp;
run;
ods tagsets.excelxp close;
Hi Cynthia,
Thanks for the demonstration. I see this in the code you sent me-
ods tagsets.excelxp file='c:\temp\test_hilite.xml' style=htmlblue
and I am using a .xls extension file for the output and the websas style. Is this causing the issue of colors not being highlighted in the excel spreadsheet?
Thank you,
Regards,
Nasya
Hi Cynthia,
Thanks for the follow-up. Yes, I tried your code and it worked, yes it is SASWEB not websas, my bad.
I tried re writing the code as yours and it worked now, am not sure what went wrong earlier.
I appreciate your time and patience in answering my questions.
Regards,
Nasya
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.