topic Re: SAS ODS excel Remove Blank Rows After Proc Report Tables in ODS and Base Reporting

SAS ODS excel Remove Blank Rows After Proc Report Tables

juan1marioo — Wed, 25 Aug 2021 16:36:22 GMT

Is there a way to delete the empty row that generates between proc reports in an excel when using ods excel. I know you can use skip_space with ods tagset.excelxp, but I need an xlsx file. Thanks!!

Re: SAS ODS excel Remove Blank Rows After Proc Report Tables

Cynthia_sas — Wed, 25 Aug 2021 17:00:50 GMT

Hi:
Are you generating with sheet_interval='none' as a sub-option? do you have titles turned on? Can you show an example of the code, including ODS statements that you're submitting? Are you using Statistical Procedures along with PROC REPORT? The usual behavior is for every separate PROC REPORT to start a new sheet in the workbook.

If this is a fairly standard number of rows in each PROC REPORT, then you can figure out which one of the rows you want to "hide" using a suboption, as shown below:

Cynthia

Re: SAS ODS excel Remove Blank Rows After Proc Report Tables

juan1marioo — Wed, 25 Aug 2021 17:59:53 GMT

They're all on the same sheet. I need them deleted unfortunately, not just hidden.

Re: SAS ODS excel Remove Blank Rows After Proc Report Tables

Cynthia_sas — Wed, 25 Aug 2021 18:41:50 GMT

Hi:
Unfortunately, without seeing your data or your code, it's hard to make suggestions. Can you concatenate the data together so that one PROC REPORT will generate what you need? Otherwise, there's not an option that I recall that will do what you want.
Cynthia

Re: SAS ODS excel Remove Blank Rows After Proc Report Tables

Ksharp — Fri, 27 Aug 2021 12:40:34 GMT

Check option 'start_at='.

https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

CODE UNTESTED


ods excel file='c:\temp\temp.xlsx' options(sheet_interval='none');
proc print data=sashelp.class(obs=4);
run;

ods excel options(start_at="1,6");
proc print data=sashelp.heart(obs=10);
run;
ods excel close;

Re: SAS ODS excel Remove Blank Rows After Proc Report Tables

juan1marioo — Fri, 27 Aug 2021 12:46:29 GMT

Re: SAS ODS excel Remove Blank Rows After Proc Report Tables

t_ar_taat — Wed, 13 Apr 2022 02:03:35 GMT

We can hide line 6 though not deleting.

ods excel file = "d:\temp.xlsx"
options(hidden_rows="6");

https://communities.sas.com/t5/ODS-and-Base-Reporting/Remove-empty-row-between-title-footnote-and-table-ODS-EXCEL-and/td-p/595486