topic Re: Can the blank line at the end of the PROC REPORT be deleted? in SAS Programming

Can the blank line at the end of the PROC REPORT be deleted?

SASAna — Tue, 31 May 2016 19:10:01 GMT

I am working on appending the data through loop and PROC REPORT and ODS EXCEL methods.

I am successful in appending the data but i am getting a blank rows after each iterations of proc report which is after 1000 lines of data. how to delete or suppress that blank lines?

Let me know if you need any additional information.

Re: Can the blank line at the end of the PROC REPORT be deleted?

Cynthia_sas — Fri, 03 Jun 2016 14:13:44 GMT

Hi:

Without code or seeing what you describe, it is hard to comment. From what you describe, however, I imagine a HUGE worksheet with 1000 rows, a blank line (after the first PROC REPORT) and then another 1000 rows from a second PROC REPORT) ?

Even when I have very dedicated users, their tolerance for scrolling through thousands of rows was limited. So I have to wonder whether delivering a worksheet without ANY reader breaks is a good idea.

But, without data to test or the code that produces the output, or an example of the current results vs the final desired result, it is hard to know whether the blank line is generated by PROC REPORT or whether the blank line is the result of the normal processing when you use sheet_interval='none', as shown if you run the code below.

ods excel file='c:\temp\tworeports.xlsx'
options(sheet_interval='none');
proc report data=sashelp.class;
title '1) first';
column name age height sex weight;
run;

proc report data=sashelp.shoes(obs=25);
title '2) second';
column region product sales inventory returns;
run;

The output:

If you run this code, you will observe 1 blank line between the first output from PROC REPORT for SASHELP.CLASS and the second output from PROC REPORT for SASHELP.SHOES. That blank line is NOT coming from PROC REPORT. That blank line is the normal division between 2 procedures.

Whether you can get rid of this blank line (if this is what you are referencing, is a question for Tech Support.

cynthia

Re: Can the blank line at the end of the PROC REPORT be deleted?

SASAna — Mon, 06 Jun 2016 14:34:24 GMT

Thanks for the reply Cynthia.

Due to the MAX # of attributes (around 400) ODS EXCEL is limiting the # of rows to 1200, so i added a logic of loop of 1000 records to test. It works really fine, but with the bug of blank line.

You are correct . Excel output is like this format -

Header

1000 Rows

A blank line

1000 Rows

A blank line

1000 Rows

A blank line

Was searching a way with lot of options but nothing worked till now.

Thanks

Re: Can the blank line at the end of the PROC REPORT be deleted?

Cynthia_sas — Mon, 06 Jun 2016 22:06:23 GMT

HI:
As I indicated, PROC REPORT is not at fault here and what you observe is not a "bug" -- it is the normal division between every procedure. It would not matter if you had a PROC REPORT, followed by a PROC MEANS, followed by a PROC TABULATE, if you put all the output into one worksheet, there is usually some kind of "break" between each procedure.

cynthia