PROC REPORT_COMPUTE

[Nasya]

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;
  

[Cynthia_sas]

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

[Nasya]

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?

[Cynthia_sas · Accepted solution]

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;

[Nasya]

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

[Cynthia_sas]

Hi:
I am not familiar with the "websas" style. Is this a custom style or a style that you use at your site? Perhaps you mean the SASWEB style? If you are using STYLE=SASWEB, that should not make a difference. The highlighting should show for you.

You can run my test code. It uses SASHELP.CLASS. If you see colors when you use my code (EXACTLY as written, except you may have to change the path), then you should be able to see colors if you code your syntax correctly.

I never use XLS as the file extension for TAGSETS.EXCELXP for these reasons:
1) you are creating an XML file that conforms to the Spreadsheet Markup Language 2003 specification;
2) XLS is a file extension for true binary Excel files and you are not creating a true binary Excel file;
3) Microsoft is getting stricter on file extensions and will issue error messages when a file extension doesn't match what's in the file -- so you're likely to get a popup window from Excel that complains when you use XLS instead of XML for TAGSETS.EXCELXP output;
4) if you want to create a true XLSX file (note XLSX, not XLS), then use ODS EXCEL, not ODS TAGSETS.EXCELXP.

Did you run my code, as I wrote it? Did you see colors?

Cynthia

[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