topic Re: Excel ODS Skip_space option in ODS and Base Reporting

Excel ODS Skip_space option

AWXu — Tue, 21 Feb 2017 05:15:34 GMT

Hi,

We recently found that the skip_space option for ODS Excel is no longer recognised. Deleting the skip_space option causes all our proc tabulate outputs (about 20 per sheet) to write in the same excel worksheet. Are there any other options that can substitute the function of skip_space?

Appreciate any help on this.

An extract of the code below (there's about 20 proc tabulate macros per sheet and 6 sheets in total):

* Sheet 1;
ODS Excel
FILE="&Directory.&Save_File..xlsx"
style=htmlblue
options(
start_at="A2"
sheet_interval='none'
sheet_name='All'
embedded_titles='yes'
zoom='80'
skip_space='1,0,0,0,0');

%proc tabulate macro(1);
%proc tabulate macro(2);
%proc tabulate macro(3);

 
* Sheet 2;
ODS Excel
style=htmlblue
options(
start_at="A2"
sheet_interval='none'
sheet_name='Small'
embedded_titles='yes'
skip_space='1,0,0,0,0');

%proc tabulate macro(1);
%proc tabulate macro(2);
%proc tabulate macro(3);

Re: Excel ODS Skip_space option

RW9 — Tue, 21 Feb 2017 13:39:13 GMT

Have you tried changing the sheet_interval option?

SHEET_INTERVAL

Table

Interval to divide the output between worksheets. Values are Table, Page, Bygroup, Proc, or None

What I see from your program is that you call the same tabulate code multiple times, with a parameter, why not just have the tabulate code, and set a by varaible. Then sheet_interval set to Bygroup will have each by group on a new sheet?

Re: Excel ODS Skip_space option

Cynthia_sas — Tue, 21 Feb 2017 22:56:30 GMT

Hi:
Are you sure that SKIP_SPACE is a valid suboption for ODS EXCEL? Look at the doc for the OPTIONS that you can specify:
http://support.sas.com/documentation/cdl/en/odsug/69832/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04zeur27rv.htm

I don't see SKIP_SPACE here. If you are converting an ODS TAGSETS.EXCELXP program to ODS EXCEL, be aware that not all the suboptions from TAGSETS.EXCELXP will work with ODS EXCEL.

cynthia