Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SAS OLE DB

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

1 Solution

Accepted Solutions
yblake
Partner
Partner

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.

View solution in original post

5 Replies
Not applicable
Author

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
Partner
Partner

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.

View solution in original post

Not applicable
Author

Did you ever figure this out?

Not applicable
Author

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?