Hi,
I have a few questions regarding options on tableeditor. I hope someone can help clarify.
1. How I can make the excel_save_file option work? It seems that there is no Excel file being saved everytime I export it to Excel.
2. Is there a way to apply the options directly to Excel so that I don't have to export it after the HTML has been generated?
3. If I need to export several tables to Excel, is there a way to assign different sheet name for each table exported without prompts involved?
Below is a sample code I am testing:
ods tagsets.tableeditor file="D:\Output\exporttoexcel.html"
style=styles.mystyle
options(excel_zoom="90"
excel_scale="90"
excel_autofilter="yes"
excel_frozen_headers="yes"
excel_orientation="landscape"
sheet_name="test"
auto_format="color1"
excel_save_file="D:\Output\exporttoexcel.xls"
open_excel="yes"
);
proc print data=sashelp.class noobs;
run;
proc print data=sashelp.shoes noobs;
run;
ods tagsets.tableeditor close;
Appreciate your immediate response.
Thanks!
Pinklemon
i tried under excel 2010 and sas 9.3 with the corresponding tagsets for 9.3
I encounter many difficulties
1 the tagsets for 9.3 must be downloaded from sas page
2 i was working with firefox and not with IE as default browser and with the result viewer inside sas 9.3
and IE addon of firefox was not accepting the script of the activex control... of the by sas builded page
so i correct to
IE as default for sas to view ods results and no more result viewer!
and this code was working with the advice of allowing the activex script under IE
and excel was finally created
i don't think direct export to excel is possible (as tableeditor was designed for html)
except an export from the excelxp tagsets by the mean of xml followed by an opening action of excel on this xml file.
Andre
NOTE in the code : excel_save_file="D:\\temp\\exporttoexcel.xls"
double slash everywhere for IE
ods html close;
ods path sasuser.templat(update) sashelp.tmplmst(read);
ods tagsets.tableeditor file="D:\temp\exporttoexcel.html"
style=styles.mystyle
options(excel_zoom="90"
excel_scale="90"
excel_autofilter="yes"
excel_frozen_headers="yes"
excel_orientation="landscape"
sheet_name="test"
auto_format="color1"
excel_save_file="D:\\temp\\exporttoexcel.xls"
open_excel="yes"
);
proc print data=sashelp.class noobs; run;
proc print data=sashelp.shoes noobs; run;
ods tagsets.tableeditor close;
Hi Andre,
Thank you for your inputs. I am currently using SAS 9.1.3 and Microsoft Excel 2003. I was able to save the Excel workbook as per your advice to put two slashes in my path. However, I am still thinking how I can export several tables in 1 worksheet and create multisheet at same time that contains several tables as well.
Hi:
Andre mentioned the possibility of using TAGSETS.EXCELXP. The advantages of this method are:
1) ODS creates output that conforms to the Office 2003 Spreadsheet Markup Language XML specification for multi-sheet workbooks
2) That means ODS can automatically create multi-sheet workbooks without making an HTML file first
3) TAGSETS.EXCELXP has an extensive set of suboptions that allow you to control printing, orientation, gridlines, etc (look in the log to see Doc='Help' documentation
4) You are creating an ASCII text file that is not an HTML file
The disadvantages of this method are:
1) By Microsoft design specs, you cannot use images, logos or graphs in the XML file
2) If your users want to see the file named as .XLS instead of .XML, you may have to open and resave the file from inside Excel
3) If you name the file with the .XLS extension in your SAS code, you are not creating a "true binary" Excel file, you are merely "fooling" the Windows registry into launching Excel when the file icon is clicked on.
Sample code is below. If you run it, you should see 3 worksheets in the workbook. Here's a user group paper that discusses some of the more popular suboptions.
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf
cynthia
title; footnote;
ods _all_ close;
** Open by doing a File-->Open from inside Excel;
ods tagsets.excelxp file='c:\temp\multsheet_wb.xml'
style=sasweb
options(doc='Help');
proc print data=sashelp.class(obs=5);
run;
proc print data=sashelp.shoes(obs=5);
run;
proc print data=sashelp.cars(obs=5);
run;
ods _all_ close;
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.