Help using Base SAS procedures

Create SQL table using SAS ODBC?

Not applicable
Posts: 1

Create SQL table using SAS ODBC?

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);


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);


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.

Super User
Super User
Posts: 9,599

Re: Create SQL table using SAS ODBC?


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;


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

Hope this helps.

Super Contributor
Posts: 334

Re: Create SQL table using SAS ODBC?

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.


Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation