BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Shayan2012
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

8 REPLIES 8
Haikuo
Onyx | Level 15

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

Shayan2012
Quartz | Level 8

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?

Cynthia_sas
SAS Super FREQ

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

Shayan2012
Quartz | Level 8

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.

Shayan2012
Quartz | Level 8

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.

Ksharp
Super User

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

podarum
Quartz | Level 8

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.

Reeza
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4740 views
  • 10 likes
  • 6 in conversation