Today, I tried to link a ODBC compliant database to a SAS programme. SAS is a programme, much like Perl, that allows data to be read, transformed and written back to a database. SAS claims that it is able to use OLE DB to hook up to ODBC compliant databases. The idea is that the SAS programme is seen as a data consumer, while OLE DB is a dataprovider thru its software that is automatically installed on a windows box.
When SAS is linked to a database, two questions are asked: what is datasource? and: what is the provider? The helpfile with SAS does not offer you great help: it only indicates that a name of the provider should be given. But what is the name of the provider? And what is the name of the datasource?
It took me a little while to find the answer:
1: The name of the datasource is exactly the name of the datasource as used in the ODBC. This can be understood as OLE DB is used here to exchange data via ODBC with the database.
2: The name of the provider is “MSDASQL.1”. I found this name by looking at the properties in Excel when data were read via OLE DB from an ODBC datasource. There, the properties were given as: “Provider=MSDASQL.1;Persist Security Info=True;User ID=tom;Data Source=MYSQL_THUIS;Extended Properties=”DSN=MYSQL_THUIS;UID=tom;”;Initial Catalog=tom”. That gave me the clue: Provider=MSDASQL.1. Exactly this name was the name, I looked for.
The idea that connection properties for OLE DB can be easily found in Excel, gave me the idea how to hook up databases via OLE DB to SAS.
Example: an Access database has these properties when it is linked to Excel: “Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\tmaanen\Documents\Boekhouding_2002041.mdb;…”. I use these values in SAS to hook up an Access database via OLE DB. See picture for an example on how these properties were used:
