Date:         Tue, 18 Sep 2001 15:29:09 +0100
Reply-To:     "Vyverman, Koen" <koen.vyverman@FID-INTL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Vyverman, Koen" <koen.vyverman@FID-INTL.COM>
Subject:      Re: 'XL' library ODBC
Content-Type: text/plain; charset="iso-8859-1"

Nigel asked:

> I've created an ODBC library to an XL spreadsheet.
> ========
>     libname xl odbc noprompt="DRIVER=Microsoft Excel Driver
> (*.xls);DBQ=c:\myfiles\myspreadsheet.xls";
> ========
> When I view the 'XL' library in the SAS v8.2 (PC) explorer
> window, I can see
> a table under the "Contents of 'XL'" named
> 'sheet1$externaldata2'  However,
> when I try to set it to a permanent library, I get a syntax
> error.  For
> example;
> =======
>     data perm.mytable;
>         setxl.sheet1$externaldata2;
>     run;
> =======
> genereats the following error:
> =======
> ERROR: File XL.sheet1.DATA does not exist.
> *snip*

Setting the validvarname option to 'any' works for me. I have an
Excel workbook "c:\temp\allvie_npa.xls" containing a data sheet
"Allvie NPA Data". The following successfully loads it into a
SAS data set work.huh :

 libname xl odbc noprompt="DRIVER=Microsoft Excel Driver (*.xls);
                           DBQ=c:\temp\allvie_npa.xls";

 options validvarname=any;

 data huh;
   set xl.'allvie npa data$'n;
  run;

Context: SAS8.2 with Office97 on WinNT (SP6).

Hope this helps,
Koen.

---------------------------------
Koen Vyverman
Database Marketing Manager
Fidelity Investments - Luxembourg
---------------------------------