BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Daniela_P
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

12 REPLIES 12
Steelers_In_DC
Barite | Level 11

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;

Daniela_P
Obsidian | Level 7

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

Unfortunately it still creates more than one file...

Daniela

Steelers_In_DC
Barite | Level 11

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?

Daniela_P
Obsidian | Level 7

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

Reeza
Super User

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.

Reeza
Super User

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

Daniela_P
Obsidian | Level 7

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

Cynthia_sas
SAS Super FREQ

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;

Daniela_P
Obsidian | Level 7

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

Cynthia_sas
SAS Super FREQ

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

Daniela_P
Obsidian | Level 7

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".

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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