The SAS Output Delivery System and reporting techniques

tagsets.ExcelXP Removing blank line between procedures

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

tagsets.ExcelXP Removing blank line between procedures

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?

 

Capture.JPG

 


Accepted Solutions
Solution
‎03-08-2016 02:57 PM
Super User
Posts: 17,785

Re: tagsets.ExcelXP Removing blank line between procedures

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

View solution in original post


All Replies
Solution
‎03-08-2016 02:57 PM
Super User
Posts: 17,785

Re: tagsets.ExcelXP Removing blank line between procedures

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
Contributor
Posts: 25

Re: tagsets.ExcelXP Removing blank line between procedures

Thank you for that solution!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 554 views
  • 0 likes
  • 2 in conversation