topic SAS OLE DB in QlikView

SAS OLE DB

Thu, 30 Apr 2009 23:11:12 GMT

Hello

This is probably quite obscure, I don't know if anyone can help. I'm trying to import a SAS (v9.1) file directly into Qlikview. I'm sure I've done this before and it worked, but now it doesn't.

I select OLE DB from the script editor and click "connect".

I select the driver for SAS datasets "SAS Local Data Provider 9.1"

I click "next" and in the "data source" box I put the name of the folder where my sas files reside.

I click OK and then the "select" button. The list of sas datasets appears with their column headings (so it obviously found them okay). I pick the dataset I want and then click OK, which pastes the code into the script as follows:

CONNECT TO [Provider=sas.LocalProvider.9.1;Data Source=T:\HOUSEHOLD\MONTHLY SAS FILES\DATA;Mode=Read|Share Deny None];

SQL SELECT *

FROM "HCMASTER_EVENT";

I then click reload and get the following error dialogue:

SQL error: A provider specific error occurred (CODACommand::Execute() : Execute not allowed).

Script line: SELECT * FROM "'HCMASTER_EVENT"

SQL SELECT *

FROM "HCMASTER_EVENT"

Has anyone else tried importing SAS files? Does anyone know what the problem is here?

Thanks.

James

SAS OLE DB

Thu, 30 Apr 2009 23:11:54 GMT

Incidentally, I can use OLE DB to load SAS datasets into Excel with no problems.

SAS OLE DB

Oleg_Troyansky — Thu, 30 Apr 2009 23:15:11 GMT

James,

sounds like a security issue on your SAS data source. I'm not familiar wtih SAS at all, but based on the message (if it's accurate), your database might now allow something like executing dynamic SQL statements? Check all various permissions - either ad the SAS level, or at the Data Provider level. Try ODBC as an alternative.

regards,

Oleg

SAS OLE DB

yblake — Fri, 01 May 2009 03:18:36 GMT

SAS support says that "The SAS Local Data Provider for OLE DB does not support SQL, so any query that uses SQL syntax (for example, SELECT * FROM table-name) will fail"

see : http://support.sas.com/kb/30/795.html

May be an Access DB can act as frontend, try to "attach" SAS dataset to an Access Database using ADO in cmdTableDirect mode, and run SQL query from Access attached table.

SAS OLE DB

Wed, 05 Jan 2011 01:48:45 GMT

Did you ever figure this out?

Re: SAS OLE DB

Wed, 09 Oct 2013 14:09:17 GMT

Has anyone figured out how to get around this yet? We are probably going to export the dataset to a .CSV document, but it is not an ideal solution.

Thoughts?