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:
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.