DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 84

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

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.

SAS Super FREQ
Posts: 8,743

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

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:

two_report_blank_line_excel.png

 

 

  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

Frequent Contributor
Posts: 84

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

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

 

 

 

SAS Super FREQ
Posts: 8,743

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

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
Ask a Question
Discussion stats
  • 3 replies
  • 223 views
  • 0 likes
  • 2 in conversation