Help using Base SAS procedures

How to export Tables in different excel sheets

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

How to export Tables in different excel sheets

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.


Accepted Solutions
Solution
‎04-16-2013 10:43 PM
Respected Advisor
Posts: 3,124

Re: How to export Tables in different excel sheets

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


All Replies
Solution
‎04-16-2013 10:43 PM
Respected Advisor
Posts: 3,124

Re: How to export Tables in different excel sheets

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

Frequent Contributor
Posts: 75

Re: How to export Tables in different excel sheets

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?

SAS Super FREQ
Posts: 8,743

Re: How to export Tables in different excel sheets

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

Frequent Contributor
Posts: 75

Re: How to export Tables in different excel sheets

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.

Frequent Contributor
Posts: 75

Re: How to export Tables in different excel sheets

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.

Super User
Posts: 9,682

Re: How to export Tables in different excel sheets

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

Super Contributor
Posts: 395

Re: How to export Tables in different excel sheets

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.

Super User
Posts: 17,842

Re: How to export Tables in different excel sheets

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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