topic Create SQL table using SAS ODBC? in SAS Procedures

Create SQL table using SAS ODBC?

Arun_23 — Wed, 12 Mar 2014 04:29:43 GMT

I refer to ODBC libraries using the following statement

LIBNAME STATIC ODBC DATASRC = static SCHEMA = DBO USER = <username> PASSWORD = <password>;

I am trying to create a new table within the the Static table in SQL Server using the following code:

proc sql;

create table static.NEW_SQL_TABLE as

select * from static.OLD_SQL_TABLE (obs = 10);

quit;

I get the following error -->

ERROR: CLI Execute Error: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name 'DBO.NEW_SQL_TABLE'.

:[Microsoft][ODBC SQL Server Driver][SQL Server] Statement(s) could not be prepared.

However a simple temporary dataset within SAS works perfectly fine using the below code:

proc sql;

create table TEMP_SQL_TABLE as

select * from static.OLD_SQL_TABLE (obs = 10);

quit;

Can anybody please help me with this? How else will I be able to directly write a SAS dataset into the SQL database (without having to export to csv and then importing back again, which is what I currently do -pardon my ignorance here)?

Thanks a ton.

Re: Create SQL table using SAS ODBC?

RW9 — Wed, 12 Mar 2014 09:43:57 GMT

Hi,

Not particularly familiar with SQL Server so hope this is not too wide of the mark. In Oracle the schema is a two level dimension, XXXYYY$VVVEEE, so with a $ division. I am wondering if something along those lines is your issue in the DBO is one level. There does seem to be something about multi level schemas after 2005 version:

User-Schema Separation

The only other thing I remember from access Oracle is that we had to do something like:

proc sql;

connect to oracle (path=dddd user=aaa orapw=cccc);

execute (drop table A_TABLE) by oracle;

execute (commit) by oracle;

disconnect from oracle;

quit;

Now of course the above is for OC however, the execute (sql commands) by db connection may be of help.

Hope this helps.

Re: Create SQL table using SAS ODBC?

esjackso — Wed, 12 Mar 2014 11:35:09 GMT

Here are somethings I would look at:

--Have you verified that you have write permissions in the database?

--Have you tried passthru sql to the SQL server? (which is what did in his example to an oracle database... just look up the syntax for SQL Server)

I dont think you mentioned which version of sas or SQL server you are using. This could help solve the issue.

You could always open a ticket with SAS support to see if they could help.