topic Re: SAS/Access Question in SAS Data Management

SAS/Access Question

That____Redhead — Mon, 25 Nov 2013 14:32:38 GMT

Hello,

I am working with some data on an MS/SQL database, and I want to do my explicit pass-thru statement to create a table on the SQL db. The only way I know to do this is to set a libname pointing to the MS/SQL server, then set that in my create table statement, but this seems very inefficient. I would think I should be able to open the connection then create a new table within it. Thoughts?

Current situation:

LIBNAME temp DATASRC=Sandbox USER=x PASS=x SCHEMA=dbo;

PROC SQL;

CONNECT TO <blah>

CREATE TABLE temp.MyTable AS SELECT * FROM CONNECTION TO ODBC ( blah);

Shouldn't I be able to set some "create table" statement within the subquery to MS/SQL? It seems this would be way more efficient...

Also, my old client used to use Teradata and there was a Fastload=yes option we used... is there such a thing for MS/SQL? It takes FOREVER to upload a mere 1,000 records.

Thanks!

--Russell

Re: SAS/Access Question

AhmedAl_Attar — Mon, 25 Nov 2013 15:28:57 GMT

Russell,

You can check the following SAS/ACCESS documentation

- SAS/ACCESS® 9.2 for Relational Databases Reference Fourth Edition

DBLOAD Procedure Specifics for Microsoft SQL Server, Page: 598

- SAS/ACCESS® 9.3 for Relational Databases Reference, Second Edition

DBLOAD Procedure Specifics for Microsoft SQL Server, Page: 602

Hope this helps,

Ahmed

Re: SAS/Access Question

JBailey — Tue, 26 Nov 2013 22:47:58 GMT

Hi Russell,

When you use the LIBNAME in your SQL code you are not doing explicit pass-thru. I don't have MySQL specific code but you can see the differences with this Teradata example.

/* Notice this code does not use a LIBNAME statement */

PROC SQL;

CONNECT TO TERADATA (SERVER=myTera

USER=myUser

PASSWORD=myPasswd);

/* Everything in the () is sent directly to the database */

SELECT * FROM CONNECTION TO TERADATA

(select *

from eecdata.order_fact a

, eecdata.order_fact));

DISCONNECT FROM TERADATA;

QUIT;

Here are some slides that explain it.

If you want to force this to happen using Implicit Pass-Thru you can do that using the DBIDIRECTEXEC option. Here is an example, unfortunately it is Teradata. The assumption here is that I created the tera1 library using a LIBNAME statement.

You probably know this, but others may not. You can get SAS to display the SQL being passed to the database using the following option. The SQL will be written to the SAS log.

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

Hopefully this will get you on the right track. If you need MySQL specific examples, let me know.

Best wishes,
Jeff