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?
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:
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:
Thank you for that solution!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.