Help using Base SAS procedures

Create SQL table using SAS ODBC?

Reply
N/A
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);

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.

Super User
Super User
Posts: 7,403

Re: Create SQL table using SAS ODBC?

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.

Super Contributor
Posts: 333

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.

EJ

Ask a Question
Discussion stats
  • 2 replies
  • 466 views
  • 0 likes
  • 3 in conversation