topic Re: How to export Tables in different excel sheets in SAS Procedures

How to export Tables in different excel sheets

Shayan2012 — Wed, 17 Apr 2013 02:19:43 GMT

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.

Re: How to export Tables in different excel sheets

Haikuo — Wed, 17 Apr 2013 02:43:56 GMT

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

Re: How to export Tables in different excel sheets

Shayan2012 — Fri, 19 Apr 2013 05:24:46 GMT

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?

Re: How to export Tables in different excel sheets

Cynthia_sas — Fri, 19 Apr 2013 12:28:04 GMT

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

Re: How to export Tables in different excel sheets

Ksharp — Sat, 20 Apr 2013 07:32:43 GMT

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

Re: How to export Tables in different excel sheets

Shayan2012 — Sat, 20 Apr 2013 17:35:44 GMT

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.

Re: How to export Tables in different excel sheets

Shayan2012 — Thu, 25 Apr 2013 13:19:35 GMT

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.

Re: How to export Tables in different excel sheets

podarum — Thu, 19 Dec 2013 20:52:35 GMT

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.

Re: How to export Tables in different excel sheets

Reeza — Thu, 19 Dec 2013 20:55:08 GMT

Please post as your own question, and link/reference this if required.