How to remove blank row after tables in ODS Excel?

[jasu]

Hello,

Is it possible to remove the blank row that appears after output tables when using ODS EXCEL with the SHEET_INTERVAL='NONE' option?

In the ExcelXP tagset, there had been a SKIP_SPACE option to control this feature, but as documented in Chevell Parker's excellent "Ringside Seat" article (https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf), the SKIP_SPACE option is not available in ODS EXCEL.

Example code:

data test;
	i = 1;
run;

ods _all_ close;
ods excel file="&OUTFILE"
	options(
		sheet_interval='none'
		/*skip_space='0,0,0,0,0'*/  /* not supported in ODS Excel */
	);

proc report data=test; run;
proc report data=test; run;

ods _all_ close;

Resulting Excel file:

Thanks,

Jake

[Cynthia_sas]

Hi:

  Possibly restructure the data? Do you want the header for i to be repeated? Here's one way of doing it:

and here's another way using the same work.final data with a compute block to write the "fake" headers before each group:

Or, stick with ODS TAGSETS.EXCELXP. Or change the report requirements. I frequently find that if the time to perform the extra "cosmetics" will take as much time as getting the report in the first place... sometimes report users become much more flexible on the cosmetic requirements.

cynthia

[JRoman]

Thanks for the tips, Cynthia!

Unfortunately, the actual data sets I'm working with in the report do not lend themselves well to combining into a single data set (different numbers of columns, different data types, different formats, etc. -- sorry, the demo example I gave was probably too trivial).

I also cannot switch back to ODS TAGSETS.EXCELXP because the output needs to be an XLSX file instead of XML.

I'll see if I can change the report requirements.

Do you know if there are any plans to implement the SKIP_SPACE option in a future release of ODS EXCEL?  I frequently found it to be a pretty useful feature in the tagset.

Thanks,

Jake