The SAS Output Delivery System and reporting techniques

Ods excelXP tagsets: sheet_interval doesn't work

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Ods excelXP tagsets: sheet_interval doesn't work

Dear experts,

the option sheet_interval='proc' doensn't work (to me) as it should. I tried it using ods file= xml, xls, but when writing more than one proc tabulate it creates different dinstinct files and not different sheets within the same file.

It only works when I use sheet_interval='table' and I write more than one 'table' within the same proc tabulate.

Unfortunately I cannot use this solution because the conditions (where) are different for each table.

Does it depend from the version/options of excel?

How could avoid the problem?

Many thanks to all.

Daniela


Accepted Solutions
Solution
‎05-07-2015 11:37 AM
SAS Super FREQ
Posts: 8,743

Re: Ods excelXP tagsets: sheet_interval doesn't work

Hi, Daniela:

  I think that is the best thing you can do. The only other thing I can suggest is possibly add this to your code:

ods tagsets.excelxp file='c:\temp\mult_sheet.xml' newfile=none style=seaside

    options(sheet_interval='proc' sheet_name='One');

and try again. You would have to check with Tech Support why you even need NEWFILE= since the default is supposed to be NONE. And if you get the SAME results using NEWFILE=, that is also something to investigate with Tech Support.

cynthia

View solution in original post


All Replies
Valued Guide
Posts: 858

Re: Ods excelXP tagsets: sheet_interval doesn't work

Here's a template that should work for you:

ods _all_ close;

ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml'

style=printer;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME ONE' AUTOFILTER = 'ALL');

proc print data=FILE1 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME TWO' AUTOFILTER = 'ALL');

proc print data=FILE2 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP close;

Contributor
Posts: 23

Re: Ods excelXP tagsets: sheet_interval doesn't work

Thank you... but it doesn't work.

Unfortunately it still creates more than one file...

Daniela

Valued Guide
Posts: 858

Re: Ods excelXP tagsets: sheet_interval doesn't work

Are you only using one output line (ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml')?

If you have multiple files what are they named when they are generated, are the sheet names working correctly?

Contributor
Posts: 23

Re: Ods excelXP tagsets: sheet_interval doesn't work

Dear Mark,

yes, I tried it (ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml'), but the problem persists: it still creates more than one file.

Sheet names, instead, work correctly.

Daniela

Super User
Posts: 17,806

Re: Ods excelXP tagsets: sheet_interval doesn't work

What version of tagsets are you using - you should see a note in your log of something to the effect of the following. If you have less than v1.130 upgrade your tagsets from the tagset page.

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013). Add options(doc='help') to the ods

statement for more information.

Super User
Posts: 17,806

Re: Ods excelXP tagsets: sheet_interval doesn't work

Can you replicate your process that does not work with some of the data from sashelp library?

Contributor
Posts: 23

Re: Ods excelXP tagsets: sheet_interval doesn't work

Here's an example of what I have to do.

I work with files with 30000 records, numeric variables and weights... but I think it doesn't matter with this problem; so I chose a very simple example to show you what doesn't work.

proc sort data=sashelp.class; by sex; run;

ods _all_ close;

ods tagsets.excelxp path="\\pc69899\STRANIERI\seminari ministero\Milano Napoli" file="class.xml" style=printer;

ods tagsets.excelXP options

(sheet_interval='proc'

thousands_separator="."

decimal_separator=","

convert_percentages="yes"

embedded_titles="yes"

embedded_footnotes="yes"

fitToPage="yes"

absolute_column_width='9.0');

ods tagsets.excelxp options (sheet_name="11");

proc tabulate data=sashelp.class  FORMCHAR='|             ' NOSEPS;

by sex;

CLASS  name age;

where age=11;

TABLE  (name all),age*(n*F=9.)/rts=40 condense;

run;

ods tagsets.excelxp options (sheet_name="12");

proc tabulate data=sashelp.class  FORMCHAR='|             ' NOSEPS;

by sex;

CLASS  name age;

where age=12;

TABLE  (name all),age*(n*F=9.)/rts=40 condense;

run;

ods tagsets.excelxp options (sheet_name="13");

proc tabulate data=sashelp.class  FORMCHAR='|             ' NOSEPS;

by sex;

CLASS  name age;

where age=13;

TABLE  (name all),age*(n*F=9.)/rts=40 condense;

run;

ods tagsets.excelxp close;

ods listing;

This program creates 3 files: class, class1, class2. Inside each one, there's only one sheet with 2 tables by sex.

I'd like, instead, one file (class) with 3 sheets (one per proc).

Thank you very much for your help!

Daniela

SAS Super FREQ
Posts: 8,743

Re: Ods excelXP tagsets: sheet_interval doesn't work

Hi:

  When I run this code, the sheet_interval='proc' sub-option works for me. All of the TABULATE output is on one sheet, the proc print is on sheet 2 and the proc means is on sheet 3. If you run my code and get more than 3 sheets, then you should open a track with Tech Support.

        

Cynthia

ods tagsets.excelxp file='c:\temp\mult_sheet.xml' style=seaside
    options(sheet_interval='proc' sheet_name='One');

** should get 3 tables on 1 sheet from TABULATE;

proc tabulate data=sashelp.shoes;
where region in ('Asia', 'Canada', 'Pacific');
var sales returns;
class region product;
table product,
      region all,
   sales*mean returns*mean / box=_page_;
run;

    

ods tagsets.excelxp options(sheet_name='Two');
proc print data=sashelp.shoes(obs=20);
run;

   

ods tagsets.excelxp options(sheet_name='Three');
proc means data=sashelp.shoes mean min max stderr;
var sales;
class region;
run;

  

ods tagsets.excelxp close;

Contributor
Posts: 23

Re: Ods excelXP tagsets: sheet_interval doesn't work

Dear Cynthia,

Unfortunately, running your code, I got 3 xml files:

1- mult_sheet (3 tables on 1st sheet ('One') from TABULATE)

2-mult_sheet1 (1 table on 1st sheet ('Two') from print)

3-mult_sheet2 (1 table on 1st sheet ('Three') from means).

I think I must open a track with Tech Support.

Thank you.

Daniela

Solution
‎05-07-2015 11:37 AM
SAS Super FREQ
Posts: 8,743

Re: Ods excelXP tagsets: sheet_interval doesn't work

Hi, Daniela:

  I think that is the best thing you can do. The only other thing I can suggest is possibly add this to your code:

ods tagsets.excelxp file='c:\temp\mult_sheet.xml' newfile=none style=seaside

    options(sheet_interval='proc' sheet_name='One');

and try again. You would have to check with Tech Support why you even need NEWFILE= since the default is supposed to be NONE. And if you get the SAME results using NEWFILE=, that is also something to investigate with Tech Support.

cynthia

Contributor
Posts: 23

Re: Ods excelXP tagsets: sheet_interval doesn't work

Cynthia!

I'm so happy!!!

It finally worked with your option newfile=none !!!

Thank you so much!

Daniela

P.S. (for Reeza)

My sas version is:

"SAS 9.1.3 and above, v1.94, 09/09/12".

Super User
Posts: 17,806

Re: Ods excelXP tagsets: sheet_interval doesn't work

v1.94 was released in September 2008 - appr7 years ago Smiley Happy

Download and install the new version and see if you have the same issues:

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

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 1117 views
  • 3 likes
  • 4 in conversation