SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS/Access Question

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

SAS/Access Question

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;

<create shell in MS/SQL>

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    


Accepted Solutions
Solution
‎11-26-2013 05:47 PM
SAS Employee
Posts: 203

Re: SAS/Access Question

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.

passthru-2.jpg

passthru-1.jpg

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.

DBIDIRECTEXEC.jpg

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

View solution in original post


All Replies
Regular Contributor
Posts: 213

Re: SAS/Access Question

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

Solution
‎11-26-2013 05:47 PM
SAS Employee
Posts: 203

Re: SAS/Access Question

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.

passthru-2.jpg

passthru-1.jpg

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.

DBIDIRECTEXEC.jpg

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 418 views
  • 0 likes
  • 3 in conversation