The SAS Output Delivery System and reporting techniques

Want to remove the blank row in the excel

Reply
Frequent Contributor
Frequent Contributor
Posts: 77

Want to remove the blank row in the excel

Hi Dear,

     When I ran the below attached coding, i was able to create a excel report, but i see a blank row is  being creted between the column header and the result, produced by Line statment..  How do I make to disappear / remove the blank row.


Thanks very much for your help in advance.

thanks

Anton

data test;
input @1 var1 $1. @2 Var2 $2.;
cards;
ab
cb
cd
ex
;
run;

ODS TAGSETS.EXCELXP  FILE='C:\TEMP\TEST.XLS'  STYLE=DEFAULT RS=NONE;


ODS TAGSETS.EXCELXP OPTIONS(
            ORIENTATION='LANDSCAPE'
        EMBEDDED_TITLES='YES'
             SHEET_NAME="SHEETXX"
                FITTOPAGE='YES

      CENTER_HORIZONTAL='ON'
  ABSOLUTE_COLUMN_WIDTH='08,08'
  );
RUN;
TITLE ' HOW TO REMOVE THE BLANK LINE AFTER THE COLUMN HEADER';
proc report data=test nowd ;

compute before;
line 'test';
endcomp;

run;
ODS _ALL_ CLOSE;
ODS LISTING;
RUN;

SAS Super FREQ
Posts: 8,716

Re: Want to remove the blank row in the excel

Hi:

  I suggest that you use DOC='HELP' as a suboption and look for the description of the "skip_space" suboption. The space that you see is NOT attached to the column headers (I call the column headers the variable names in the columns). What you are calling the headers seems to me to be the area underneath the SAS title statement. The extra space after the SAS TITLE is controllable by TAGSETS.EXCELXP. But I think you need to use SKIP_SPACE='1,0,0,1,1' or something like that. I believe that the 3rd value is the amount of space to skip after the title.

  So if you did this: SKIP_SPACE='1,0,5,1,1' you would get a very tall cell underneath the SAS title.

cynthia

Frequent Contributor
Frequent Contributor
Posts: 77

Re: Want to remove the blank row in the excel

HI Cynthia,

Thanks very much for your response.

I am right, I didn't mean the blank row between the title and the table. I don't need the row ( 4th blank row) that is between the column header and the data.  I have tried many ways, but none of them works. If you can provide the solution for this , I will be much appriciated your help.

Thanks

Anton

SAS Super FREQ
Posts: 8,716

Re: Want to remove the blank row in the excel

Hi:

I'm sorry, I only see the line that says "test" from the COMPUTE BEFORE code.

compute before;

   line 'test';

endcomp;

  The location that you describe is exactly the location where COMPUTE BEFORE LINE statement text would be written -- between the column headers and the first row of data. Did you want the COMPUTE BEFORE string???. Or, do you want to know how to change this location??? You might try COMPUTE BEFORE _PAGE_ and see if you like that location output better.

  Otherwise, take the COMPUTE BEFORE code block out of your PROC REPORT program. The SKIP_SPACE suboption is going to get rid of the blank space underneath the title. Taking out the COMPUTE BEFORE will eliminate the line that you describe.

cynthia

New Contributor
Posts: 3

Re: Want to remove the blank row in the excel

Hi Cynthia,

I am jumping onto this discussion, because you mentioned the compute before _page_ block and I can't find anything on this topic relating to my problem.

I have created a seperate discussion for it.

I have two proc report statements.

The first generates a dummy table for subtotals.

Here I have a compute before _page_ block within ODS output. It adds one line containing a macro variable (username) and one blank line.

The second table is the data output and an autofilter is set.

It seems the compute block affects the autofilter such that it shifts to the first row of the data and is not on the column headings anymore.

Any thoughts ?

Regards

Reinhard

SAS Super FREQ
Posts: 8,716

Re: Want to remove the blank row in the excel

Hi:

I posted some thoughts over in that other thread.

cynthia

Ask a Question
Discussion stats
  • 5 replies
  • 1849 views
  • 0 likes
  • 3 in conversation