Hi all,
I am trying to export some tables into Excel 2003 or 2007. The ideal pattern for me is to export each table in a new sheet in a unit file.
I mean, table 1 --> sheet 1, table 2--> sheet 2, ... when all sheet 1,2, ... are in the same file. I searched in the internet but did not find anything related.
Do you think it is possible? if yes, how?
Thanks a lot guys.
Very much possible.
ODS TAGSETS.EXCELXP
FILE='C:\temp\test_xp.xls'
STYLE=minimal
OPTIONS ( Sheet_Name = 'Shoes' );
PROC PRINT DATA=sashelp.shoes; RUN;
ODS TAGSETS.EXCELXP
OPTIONS ( Sheet_Name = 'Class' );
PROC PRINT DATA=sashelp.class; RUN;
ODS TAGSETS.EXCELXP CLOSE;
When having the prompt, click 'yes'. Or you don't like the prompt, then change test_xp.xls to test_xp.xml
There are ways to do multisheet using Proc Report as well.
Haikuo
Very much possible.
ODS TAGSETS.EXCELXP
FILE='C:\temp\test_xp.xls'
STYLE=minimal
OPTIONS ( Sheet_Name = 'Shoes' );
PROC PRINT DATA=sashelp.shoes; RUN;
ODS TAGSETS.EXCELXP
OPTIONS ( Sheet_Name = 'Class' );
PROC PRINT DATA=sashelp.class; RUN;
ODS TAGSETS.EXCELXP CLOSE;
When having the prompt, click 'yes'. Or you don't like the prompt, then change test_xp.xls to test_xp.xml
There are ways to do multisheet using Proc Report as well.
Haikuo
Thank you very much,Haikuo. Thats awesome and the code works well. However, when I try to open excel file, I got an error with this log file for the error:
XML ERROR in Style
REASON: Bad Value
FILE: D:\test4.xls
GROUP: Style
TAG: Font
ATTRIB: FontName
VALUE: MS PGothic, MS PGothic, Helvetica
(This is repated multiple times, apparently for each observation)
I am guessing that excel can not recognize the sas font, and I am not sure how can I change it in the procedure. should I use proc template? or it is possible through the above code?
Could you please help me on that?
Hi:
I have never seen that error when using TAGSETS.EXCELXP. If you used the MINIMAL style, as recommended, that style has no font definition. When I use the MINIMAL style with TAGSETS.EXCELXP and look down in the XML file (with Notepad), I do NOT see any hard-coded font references at all. What version of SAS do you have and if you look in the log, what version of TAGSETS.EXCELXP did SAS use? What version of Office did you use to open the file (you need Excel 2002/2003 to open the file -- Excel 97 won't work).
I would recommend that you open a track with Tech Support. To open a track with Tech Support, fill out the form at this link:
http://support.sas.com/ctx/supportform/createForm They can look at ALL of your code and the style that you used and the version of SAS that you have and the version of TAGSETS.EXCELXP that you use and help you come to a resolution.
cynthia
Thanks a lot Cynthia,
Actually, I chekced again with minimal style and with xml. filetype and it worked this time. I must have done something wrong. But, If I want to use format xls the problem remains, and I am not sure why. I will try to find the problem anyway through the support.
Just for future reference, I add that it seems the problem I was talking about above was resulting from the names that I was trying to attribute to excel sheets. My names contained underscores, and apparently faced problems in exporting. After removing the _, the code worked perfectly.
Or use a simple Macro to make a real excel file .
data have; input dataset_name : $20.; cards; sashelp.class sashelp.air ; run; data _null_; set have; call execute('proc export data='||dataset_name||' outfile="c:\temp\want.xls" dbms=excel;sheet="'||strip(scan(dataset_name,-1,'.'))||'";run;'); run;
KSharp
Message was edited by: xia keshan
KSharp, is there a way to update the data to excel? If I create a table in Excel, I want it to remain, and just update the SAS data.Thanks.
Please post as your own question, and link/reference this if required.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.