SAS OLE DB

[#1]

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

[#2]

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

[Oleg_Troyansky]

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

[yblake · Accepted solution]

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.

[#5]

Did you ever figure this out?

[#6]

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?