DATA Step, Macro, Functions and more

PROC REPORT_COMPUTE

Reply
Contributor
Posts: 20

PROC REPORT_COMPUTE

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;
SAS Super FREQ
Posts: 9,365

Re: PROC REPORT_COMPUTE

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:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Proc-report-compute-not-returning-desired-resu...

 

  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

 

Contributor
Posts: 20

Re: PROC REPORT_COMPUTE

Thank you Cynthia for the clear and elaborate explanation.
It worked the way you said.
When exporting to this PROC REPORT to xls, the color highlight is not seen. Is there a way this can be accomplished?
SAS Super FREQ
Posts: 9,365

Re: PROC REPORT_COMPUTE

Hi:

  Using SASHELP.CLASS as a test, I find the CALL DEFINE statement works as described:

call_define_color_excelxp.png

 

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;
Ask a Question
Discussion stats
  • 3 replies
  • 79 views
  • 0 likes
  • 2 in conversation