tagsets.ExcelXP Removing blank line between procedures

chimanbj — Thu, 11 Feb 2016 04:38:37 GMT

I'm trying to replicate a report format, and I've got the data laid out in the order that I need, but I have an extra line that I'm trying to get rid of. I have two proc report statments that I'm running on one tab of the spreadsheet. This is made necessary by the requirement of a header row between sections of data. I have used the sashelp.class table to illustrate what I am trying to do:

ods tagsets.ExcelXP file='c:\problem.xml'
                    options(embedded_titles='yes'
                            sheet_interval='none'
                           )
;

title1 "How Do I Remove The Extra Line?";
title2 "The line between the two procs";
run;
proc report data=sashelp.class(where=(sex='M'));
column name age height weight;
define name / display "Name";
define age / display "Age";
define height / display "Height";
define weight / display "Weight";
compute before;
line "Males in the Class";
endcomp;
compute after;
line "Females in the Class";
endcomp;
run;
title1;
title2;
run;
proc report data=sashelp.class(where=(sex='F')) noheader;
column name age height weight;
define name / display "Name";
define age / display "Age";
define height / display "Height";
define weight / display "Weight";
run;

ods tagsets.ExcelXP close;

After each of the procedure statements, as well as before the first procedure, after the title2 statement, there is a row that I would ideally like to remove automatically (rows 3, 17 and 27). I know I could go into the spreadsheet and remove the row manually, but I am trying to remove any manual manipulation of this workbook.

Is there a way to do this programatically?

Re: tagsets.ExcelXP Removing blank line between procedures

Reeza — Thu, 11 Feb 2016 09:26:51 GMT

Look into the SKIP_SPACE option in ODS TAGSETS.EXCELXP

http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

The definition of the options is here:

http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#skip

The default values are:

Table : 1
Byline : 0
Title : 1
Footer : 1
PageBreak : 1

Re: tagsets.ExcelXP Removing blank line between procedures

chimanbj — Tue, 08 Mar 2016 19:57:31 GMT

Thank you for that solution!

topic Re: tagsets.ExcelXP Removing blank line between procedures in ODS and Base Reporting

tagsets.ExcelXP Removing blank line between procedures

Re: tagsets.ExcelXP Removing blank line between procedures

Re: tagsets.ExcelXP Removing blank line between procedures