topic Re: Pass Through SQL Select Into ? in SAS Procedures

Pass Through SQL to Append/Update a SQL Server Table?

KevinC_ — Thu, 02 Jan 2014 21:06:14 GMT

Hello Everyone,

I am trying to use pass through SQL to append new data to an existing SQL Server table. I get this error pointing to the libname reference devsrv: "Expecting a : .". I use the same libname reference when I create tables on the same server and and it has always worked. I suspect the error is caused by something else in my code. Does anyone have any suggestions? Any input would be greatly appreciated. Thank you!

Here is my code:

Libname devsrv ODBC DSN="SERV1" schema=dbo;

PROC SQL;

connect to odbc(dsn="SERV1");

select *

into devsrv.cust_table1

from cust_data;

disconnect from odbc;

QUIT;

RUN;

Re: Pass Through SQL Select Into ?

DBailey — Thu, 02 Jan 2014 21:22:06 GMT

Libname devsrv ODBC DSN="SERV1" schema=dbo;

PROC SQL;

create table devsrv.cust_table1 as

select *

from cust_data;

QUIT;

Re: Pass Through SQL Select Into ?

GlenGold — Thu, 02 Jan 2014 21:22:47 GMT

I'm not sure exactly what you are trying to accomplish, but I've used the into: to create a macro variable in PROC SQL.

something like this:

PROC SQL;

select max(order_date) into: most_recent_shipment

from cust_data

where ship_ind = 1;

quit;

PROC SQL;

create table late_ship as

select order_id

from order_table

where ship_ind = 0

and order_date < &most_recent_shipment;

quit;

Re: Pass Through SQL Select Into ?

ballardw — Thu, 02 Jan 2014 21:24:11 GMT

SAS SQL instruction into is attempting to place the data into a SAS macro variable which should be indicated by the : .

Re: Pass Through SQL Select Into ?

DBailey — Thu, 02 Jan 2014 21:25:29 GMT

reread your question...try this:

PROC SQL;

connect to odbc(dsn="SERV1");

execute (

select *

into cust_table1

from cust_data

) by odbc;

disconnect from odbc;

QUIT;

RUN;

Re: Pass Through SQL Select Into ?

KevinC_ — Thu, 02 Jan 2014 21:42:50 GMT

Thank you, DBailey!

I tried again using your code (exactly as you typed) and I got this error:

CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name 'cust_data'.

The cust_data dataset exits. Not sure why it doesn't like it. Any suggestions? Thanks again!

Re: Pass Through SQL Select Into ?

LinusH — Thu, 02 Jan 2014 22:07:37 GMT

First, this is becoming a bit SQL Server specific question.

But to read your question again, by doing select into in SQL Server. it means that you are creating a table based on the structure in the select statement.

So in your case, your query fails because the table already exists (or does it exist within SQL Server?).

But you said you wish to insert data into your table, then you need to use the insert into construct instead/as well.

If your table cust_data resides in SAS, you can't use explicit pass-thru.

Re: Pass Through SQL Select Into ?

KevinC_ — Thu, 02 Jan 2014 22:33:24 GMT

Thank you, LinusH.

I changed my SAS code to this:

Libname devsrv ODBC DSN="SERV1" schema=dbo;

PROC SQL;

connect to odbc(dsn="SERV1");

execute (

Insert into devsrv.cust_table1 (cust_id, loan_count, category)

select cust_id, loan_count, category

from cust_data

) by odbc;

disconnect from odbc;

QUIT;

RUN;

This is the error I got:

CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name 'devsrv.cust_table1'.

Any suggestions from anyone? Thanks.:)

Re: Pass Through SQL Select Into ?

Tom — Thu, 02 Jan 2014 22:55:36 GMT

Now that you are sending the SQL code directly to ODBC yourself you need to use syntax that ODBC can interpret. It knows nothing of the SAS libref DEVSRV that you created so you need to use the native syntax for referencing the table you want. In your LIBNAME statement you linked the SAS libref of DEVSRV to the DBO schema.

You probably need to change devsrv.cust_table1 to dbo.cust_table1.

Re: Pass Through SQL Select Into ?

DBailey — Fri, 03 Jan 2014 16:14:41 GMT

The odbc connections include a default library which if you didn't specify becomes MASTER. On the SQLServer, there is a database that you would include and the syntax is

databasename.schema.tablename. So...

PROC SQL;

connect to odbc(dsn="SERV1");

execute (

select *

into databasename.schema.cust_table1

from databasename.schema.cust_data

) by odbc;

disconnect from odbc;

QUIT;

If this doesn't work, then you probably will need to get with the sqlserver dba to help understand the correct databasename and schema.

Re: Pass Through SQL Select Into ?

Patrick — Sat, 04 Jan 2014 01:28:02 GMT

You haven't told us yet if your table "cust_data" is a database table or a SAS WORK table. Assuming it's a SAS work table code as below should do (not tested):

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

libname devsrv ODBC DSN="SERV1" schema=dbo DBCOMMIT=0;

proc append base=devsrv.cust_table1 data=cust_data force /* nowarn */ ;

run;

To take full advantage of "DBCOMMIT=0" you might also need to set values other than the defaults for:

"INSERT_SQL" and "INSERTBUFF"

Re: Pass Through SQL to Append/Update a SQL Server Table?

CTorres — Tue, 14 Jan 2014 18:54:19 GMT

My contribution:

To use the SQL Pass-Through facility you don't need the libname but instead you need to do a select * from connection.

Try this:

PROC SQL;

connect to odbc(dsn="SERV1");

select * from connection to odbc (

**** Here the sql code you want the SQL server to execute ****

);

disconnect from odbc;

QUIT;

RUN;

Regards,

Re: Pass Through SQL to Append/Update a SQL Server Table?

Lenvdb — Wed, 06 Aug 2014 15:23:23 GMT

Did you ever get a solution with this issue?

I am now having a similar issue and found by inserting this line I can do it:

USE <databasename>;

PROC SQL;

connect to odbc as sasx(dsn="SERV1");

Execute(USE &db; /*==specify your database===*/

Insert into cust_table1 (cust_id, loan_count, category)

select cust_id, loan_count, category

from cust_data

)by sasx;

disconnect from sasx;

QUIT;