BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
That____Redhead
Fluorite | Level 6

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    

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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

2 REPLIES 2
AhmedAl_Attar
Rhodochrosite | Level 12

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

JBailey
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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