Hi Karl. I don't if you remember, you helped me to read ( from Excel to SAS) many worksheets of one workbook, using your program based on odbc. My question can I do the inverse? I would say, having many SAS data sets, I want to put them in the same workbook, but each of them in a worksheet. Can you help me?

-----Original Message----- From: Elmaache, Hamani Sent: January 9, 2003 1:21 PM To: 'Karl Kilgore' Subject: RE: Excel TO SAS from many sheets?

Hi Karl. The OCBC works fine, but I want to open my Excell FILE, i cannot . I got the message: Cannot access 'data.xls'. What's happed? Can you help? Hamani

-----Original Message----- From: Karl Kilgore [mailto:karlstudboy@hotmail.com] Sent: January 9, 2003 12:03 PM To: 'Elmaache, Hamani' Subject: RE: Excel TO SAS from many sheets?

Super, I'm glad it worked. In my work, I interface to lots of different types of datafiles, and I'm really fond of ODBC over the other various options SAS offers.

-----Original Message----- From: Elmaache, Hamani [mailto:Hamani.Elmaache@ccra-adrc.gc.ca] Sent: Thursday, January 09, 2003 11:49 AM To: 'Karl Kilgore' Subject: RE: Excel TO SAS from many sheets?

Thanks a lot. I did mistake here:SR2002data.xls.xls. There is two xls. Your code woks very goog for my. Thank you again.

-----Original Message----- From: Karl Kilgore [mailto:karlstudboy@hotmail.com] Sent: January 9, 2003 11:50 AM To: 'Elmaache, Hamani' Subject: RE: Excel TO SAS from many sheets?

Hmmm, although the log message indicates that SAS assigned the libname successfully, I'm guessing it did not. Take a look at line 76 in your listing below. Note the duplication of ".xls" in the file name. Double-check that you've typed the filename in correctly in the dbq= subparameter. In the line you sent me, it looks OK, but if you don't mind, just double-check for me in your actual program that you don't have ". . .\SR2002data.xls.xls".

If that's not it, let me know and we'll try something else.

-----Original Message----- From: Elmaache, Hamani [mailto:Hamani.Elmaache@ccra-adrc.gc.ca] Sent: Thursday, January 09, 2003 11:25 AM To: 'Karl K.' Subject: RE: Excel TO SAS from many sheets?

Hi Karl. I'm not a good programmer. But I want to test you your program. It seems simple and nice. But i have some difficulty to try it: what have I to put in: noprompt='dsn=Excel Files; dbq=<path to your excel file>.xls';?

I did this:noprompt='dsn=Excel Files; dbq=H:\bms\bms2002\SR2002data.xls'; It doesn't work. I got the folling message: /***************************************/ 74 libname xl odbc 75 noprompt=XXXXXXXXXXXXXXXXX 76 dbq=H:\bms\bms2002\SR2002data.xls.xls'; NOTE: Libref XL was successfully assigned as follows: Engine: ODBC Physical Name: 77 78 proc sql noprint; 79 select distinct "xl.'"||trim(memname)||"'n" 80 into :xlsheets separated by ' ' 81 from dictionary.members 82 where libname='XL' and index(memname,'$')>0; NOTE: No rows were selected. 83 NOTE: PROCEDURE SQL used: real time 0.01 seconds cpu time 0.01 seconds

84 data whatever; NOTE: SCL source line. 85 set &xlsheets; - 22 -------- 202 WARNING: Apparent symbolic reference XLSHEETS not resolved. ERROR: File WORK.XLSHEETS.DATA does not exist. ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, END, KEY, KEYS,NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

86 run;

NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.WHATEVER may be incomplete. When this step was stopped there were 0 observations and0 variables. WARNING: Data set WORK.WHATEVER was not replaced because this step was stopped. NOTE: DATA statement used: real time 0.01 seconds cpu time 0.00 seconds

/***************************************/ How to fix it? Thank you for help. Hamani.

-----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