Hi Susie . Make sure that you have ODBC. If not, don't read no further. If you have ODBC, the code is right, Just add this:

libname xl odbc noprompt='dsn=Excel Files; Then the will be:

----- libname xl odbc noprompt='dsn=Excel Files; dbq=E:\public\test.xls';

proc sql noprint; select distinct "xl.'"||trim(memname)||"'n" into :xlsheets separated by ' ' from dictionary.members where libname='XL' and index(memname,'$')>0;

data whatever; set &xlsheets; run;

-----Original Message----- From: Susie Li [mailto:Susie.Li@US.SANOFI.COM] Sent: January 14, 2003 11:28 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Excel TO SAS from many sheets?

A nifty program indeed, except I have trouble running it too. Can you help?

Here is my code,

----- libname xl odbc noprompt='XXXXXXXXXXXXXXXXX; dbq=E:\public\test.xls';

proc sql noprint; select distinct "xl.'"||trim(memname)||"'n" into :xlsheets separated by ' ' from dictionary.members where libname='XL' and index(memname,'$')>0;

data whatever; set &xlsheets; run; -----

Here is my error message:

___

libname xl odbc noprompt=XXXXXXXXXXXXXXXXXXX 193 dbq=E:\public\test.xls';

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ERROR: Error in the LIBNAME statement. 194

Susie Li Sanofi-Synthelabo, Inc. 90 Park Ave New York, NY 10016 (212)551-4385 susie.li@us.sanofi.com

__________________

> > > > -----Original Message----- > > From: Karl K. [mailto:karlstudboy@HOTMAIL.COM] > > Sent: January 8, 2003 2:15 PM > > To: SAS-L@LISTSERV.UGA.EDU > > Subject: Re: Excel TO SAS from many sheets? > > > > > > You've received a couple good solutions that use DDE and one that uses > > Proc Import. Here's an additional one that uses ODBC, that is kind of > > short and sweet and works without modification regardless of how many > > worksheets are in the file. Obviously, if you don't have ODBC, read no > > further. It assumes that all your sheets are in the same physicial file > > and that, as you stated, all the worksheets are structured the same and > > you just want to append them together. > > > > libname xl odbc > > noprompt='dsn=Excel Files; > > dbq=<path to your excel file>.xls'; > > > > proc sql noprint; > > select distinct "xl.'"||trim(memname)||"'n" > > into :xlsheets separated by ' ' > > from dictionary.members > > where libname='XL' and index(memname,'$')>0; > > > > data whatever; > > set &xlsheets; > > run; > > > > Simple, no? A couple explanatory comments: > > 1) "xl" is an arbitrary name, but you need to use the same name > > consistently. > > 2) the "index(memname,'$')>0" in the where clause differentiates the > > worksheets from any named ranges they may contain. If you don't have > > any named ranges in your workbook, you can omit it. > > 3) because worksheet names look like they contain a "$" sign to ODBC, > > and that's an illegal character for SAS, you have to refer to the > > worksheets using what's called "name constants". So what the select > > statement does is construct name constants by enclosing the worksheet > > names in single quotes followed by "n". > > > > As I said, just another path to the same outcome. > > > > Karl

Important: The Information in this e-mail belongs to Sanofi-Synthelabo Inc., is intended for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of, or reliance on, the contents of this e-mail is prohibited. If you have received this e-mail in error, please notify us immediately by replying back to the sending e-mail address, and delete this e-mail message from your computer.