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
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
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;
Thank you... but it doesn't work.
Unfortunately it still creates more than one file...
Daniela
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?
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
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.
Can you replicate your process that does not work with some of the data from sashelp library?
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
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;
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
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
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".
v1.94 was released in September 2008 - appr7 years ago
Download and install the new version and see if you have the same issues:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.