topic Weird interaction between Proc Report, ODS EXCEL, and cell borders in ODS and Base Reporting

Weird interaction between Proc Report, ODS EXCEL, and cell borders

scify — Thu, 19 Sep 2019 23:14:52 GMT

So I'm seeing something odd when trying to play with cell borders in a proc report going to an ODS EXCEL output. The client is requesting that some of the cells be merged horizontally--as far as I can tell, I can't do that via SAS, but I figured I could try and fake it with 0-width internal cell borders. This seems to work fine in the internal results viewer, but when transitioned to Excel, any cell with any border attribute defined instead gets completely surrounded.

SAS EG results viewer:

Excel output:

Desired Excel Output:

And my code:

ods excel file="&PDFPath./&&filename_value._&&qvar..xlsx" style=journal
options(embedded_titles='yes' embedded_footnotes='yes' sheet_interval='NONE' sheet_name="&qtrvar. Summary"
absolute_column_width='26,12,26,12' );

proc report data=source spanrows nowd
	style(header)={bordertopwidth=.5 borderbottomwidth=.5
		borderleftwidth=.5 borderrightwidth=.5 borderstyle=solid
		bordercolor=black};
	column ("Level 2" (row1 value1 row2 value2));
	define row2 / " " style(column)={width=1000%};
	define row1 / " " style(column)={width=1000% bordertopwidth=.5 borderbottomwidth=.5
						borderleftwidth=.5 borderrightwidth=.5 borderstyle=solid
						bordercolor=black};
	define value1 / " " style(column)={width=1000% bordertopwidth=.5 borderbottomwidth=.5
						borderleftwidth=.5 borderrightwidth=.5 borderstyle=solid
						bordercolor=black};
	define value2 / " " style(column)={width=1000% bordertopwidth=0 borderbottomwidth=0
						borderleftwidth=0 borderrightwidth=.5 borderstyle=solid
						bordercolor=black};

	compute value1;
	if row1 = 'Target Achieved: Y' then do;
		call define('row1', 'style', 'style=[background=lightgreen font_weight=bold just=c]');
		call define('value1', 'style', 'style=[background=lightgreen font_weight=bold just=c]');
		call define('row2', 'style', 'style=[borderbottomwidth=.5]');
		call define('value2', 'style', 'style=[borderbottomwidth=.5]');
	end;
	else if row1 = 'Target Achieved: N' then do;
		call define('row1', 'style', 'style=[background=lightred font_weight=bold just=c]');
		call define('value1', 'style', 'style=[background=lightred font_weight=bold just=c]');
		call define('row2', 'style', 'style=[borderbottomwidth=.5]');
		call define('value2', 'style', 'style=[borderbottomwidth=.5]');
	end;
	endcomp;
quit;

ods excel close;

Re: Weird interaction between Proc Report, ODS EXCEL, and cell borders

Reeza — Thu, 19 Sep 2019 20:01:43 GMT

Why is your width set to 1000% and not 100%?What does the log show?

Re: Weird interaction between Proc Report, ODS EXCEL, and cell borders

scify — Thu, 19 Sep 2019 20:06:19 GMT

The width is set to 1000 because I based the code off of another proc report in some code I inherited. The log doesn't show anything useful, unfortunately--no errors, no warnings, only the note telling me that the excel file was written to the output file.

Re: Weird interaction between Proc Report, ODS EXCEL, and cell borders

ballardw — Thu, 19 Sep 2019 20:34:02 GMT

With Excel output you should also show your ODS Excel definition, or Tagsets.Excel , or describe how you get the output to Excel.

There are options that may help with cell width as the body of a report procedure's output may well be controlled by the output from a previous procedure.

Which way are you attempting to mimic merging? It appears that you might be doing a top/bottom merge?

On a row where you have a borderleftwidth=0 the column to the left would also want a borderrightwidth=0 to suppress that border

It would also help to either provide data or provide an example of what you are attempting with one of the SAS supplied data sets like SASHELP.CLASS.

Re: Weird interaction between Proc Report, ODS EXCEL, and cell borders

Reeza — Thu, 19 Sep 2019 21:26:23 GMT

Doesn't that strike you as incorrect?

Re: Weird interaction between Proc Report, ODS EXCEL, and cell borders

scify — Thu, 19 Sep 2019 22:42:47 GMT

Cell width is not the issue here. It's just something Reeza caught on.

The hypothetical merge would be horizontal, across the first two columns of the bottom row. Every answer I've seen about doing that says it can't be done, which is why I'm trying to fake it with hidden cell borders.

If you look at the example images I posted above, you can see that the borders are coming out correctly in the SAS results viewer, but not when it is exported to excel. You are correct that I forgot to put the ods statement in, which I meant to; I'll go ahead and edit that back in to the original post now, as well as an example of what I'm actually trying to get the Excel to look like.

There isn't really much in the way of underlying data here; because I'm trying to reproduce what the client mocked up in Excel, it's literally just text pushed through a proc stream to an infile that will eventually have values (in the column value1) filled in with macro variables.

The main thing I'm trying to figure out here is why the borders are behaving differently in the Excel export: for whatever reason the rightmost column (the variable value2), which only has a right border in the results viewer, suddenly has a full border in Excel.

Re: Weird interaction between Proc Report, ODS EXCEL, and cell borders

Reeza — Thu, 19 Sep 2019 22:56:32 GMT

What version of SAS are you using? Can you generate similar code using SASHELP so we can test it? As posted we can't run your code to test anything.

Prior to SAS 9.4TS1M3 ODS Excel was pre-production and had issues. If you're using an older version that may be the reason. Again, if we can't run your code, it's hard to comment beyond that without generating our own examples, which is time intensive.

Re: Weird interaction between Proc Report, ODS EXCEL, and cell borders

scify — Thu, 19 Sep 2019 23:16:09 GMT

PROC PRODUCT_STATUS tells me it's SAS 9.4 M5. I'm accessing it through EG 7.11 HF3, if that matters.

The data set is created as follows:

%let ent_rb = foo;

proc stream outfile=text; begin
Rate: Baseline| &ent_rb| | |
Rate: Current | | | |
Improvement| | | |
Superior Performance| | | |
Target Achieved:| | | |
;;;;



data source;
infile text delimiter="|";
length row1 row2 $150;
input row1 $ value1 $ row2 $ value2 $ @@ ;
run;

Re: Weird interaction between Proc Report, ODS EXCEL, and cell borders

ballardw — Fri, 20 Sep 2019 22:52:54 GMT

This gets a little closer:

%let ent_rb = foo;

data work.source;
infile datalines dlm='|';
length row1 row2 $150;
input row1 $ value1 $ row2 $ value2 $  ;
datalines;
Rate: Baseline| &ent_rb| | |
Rate: Current | | | |
Improvement| | | |
Superior Performance| | | |
Target Achieved:| | | |
;
run;

ods excel file="x:\data\junk.xlsx" 
   style=journal options(embedded_titles='yes' 
   embedded_footnotes='yes' sheet_interval='NONE' 
   sheet_name="text. Summary"absolute_column_width='26,12,26,12' );

proc report data=work.source spanrows nowd
	style(header)={bordertopwidth=.5 borderbottomwidth=.5
		borderleftwidth=.5 borderrightwidth=.5 borderstyle=solid
		bordercolor=black}
   ;
	column ("Level 2" (row1 value1 row2 value2));
	define row2 / " " style(column)={};
	define row1 / " " style(column)={ bordertopwidth=.5 borderbottomwidth=.5
						borderleftwidth=.5 borderrightwidth=.5 borderstyle=solid
						bordercolor=black};
	define value1 / " " style={  borderwidth=0
						borderstyle=solid
						bordercolor=black};
	define value2 / " " style={  borderwidth=0
						};

	compute value1;
	if row1 = 'Target Achieved: Y' then do;
		call define('row1', 'style', 'style=[background=lightgreen font_weight=bold just=c]');
		call define('value1', 'style', 'style=[background=lightgreen font_weight=bold just=c]');
		call define('row2', 'style', 'style=[borderbottomwidth=.5]');
	end;
	else if row1 = 'Target Achieved: N' then do;
		call define('row1', 'style', 'style=[background=lightred font_weight=bold just=c]');
		call define('value1', 'style', 'style=[background=lightred font_weight=bold just=c]');
		call define('row2', 'style', 'style=[borderbottomwidth=.5]');
	end;
	endcomp;
quit;
ods excel close;

To get your "Target Achieved" to span columns as you show in desired is beyond what I can' do at the moment.

A big cause of your extra lines was in the compute block for Value1.