Date:         Thu, 22 Feb 2001 12:58:58 +0200
Reply-To:     Arjen.Raateland@vyh.fi
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arjen Raateland <Arjen.Raateland@VYH.FI>
Organization: Suomen ymparistokeskus
Subject:      Re: Fw: access to sql server / odbc
Content-Type: text/plain; charset=us-ascii

> > Not the answer to the substance, but the answer to the 'why?'.  Because
> > Micro$oft said so.  MS is recommending to third parties that they not use
> > the Sybase connectivity tools of the past.  In fact, there are features in
> > SQL Server 2000 that you can't get to with the Sybase drivers (from Unix
> SAS
> > 6.12).

It is not imperative that one moves from Access to SQL Server/SYBASE to
Access to ODBC as SI suggests. We at FEI opted for Access to OLE DB
instead and got it.
AFAIK, it's easier to set up than ODBC which needs to be set up
separately on each work station, i.e. with OLE DB no particular setup is
needed.

BTW, we used to have a problem with Access to SQL Server (SQL Server 7,
NT 4, SAS 6.12) concerning data base columns with NULL values. It is
possible that this happened only with columns whose values are
determined in a view, but at least Access to OLE DB solved it and gave
use easy access to Excel files to boot.

For this I use a libname like the following:

libname _xl oledb
  provider="Microsoft.Jet.OLEDB.4.0"
  properties=('data source'='d:\temp\sas_to_xl.xls')
  provider_string="Excel 8.0"
;

which basically gives me direct read/write access to an Excel 97
workbook.
This is much easier than what we used to have to do, viz. write
input/put statements for DDE or intermediate CSV files. With decimal
commas in Excel and date values with full stops for separators (Finnish
settings) this was always really tricky. Copying a SAS data file to an
Excel table or v.v. is now as easy as copying one SAS data file to
another.

--
Arjen Raateland
Finnish Environment Institute
SAS Support
phone +358 9 4030 0350