Can we reference SAS libname in SQL pass thru

[Dob4Die]

Can we reference SAS libname and SAS Datasets in SQL Pass thru method to update specific columns in SQL Database Table.

Example Code:

libname test "\libnamepath";

PROC SQL ;

  CONNECT TO SQLSVR AS sqldsn(datasrc=DSN user=USERNAME password='password');

  EXECUTE(" MERGE sqldsn.dbo.DATABASE_TABLE AS A

           USING (select * from test.SASDATASET) AS B

   ON  A.COL1 = B.COL1

           AND A.COL2 = B.COL2

   AND A.COL3 = B.COL3

   AND A.COL4 = B.COl4

              WHEN MATCHED THEN UPDATE SET

             A.COL5 = B.COL5

            ,A.COL6 = B.COL6

            ,A.COL7 = B.COL7

            ,A.COL8 = B.COL8 ;") by sqldsn;

DISCONNECT FROM SQLDSN;

  QUIT;

[jakarman]

When you are coding explicit pass-thu (using the execute statement) you are sending the sql-code directly to the SQL-server without any intervention by SAS.

The SQLSVR (only one of the many RDBMS options)  does not know anything of the SAS environment.

How would SQL-server be able to do something with a SAS-libname or a SAS-dateset? This is impossible.

Would you code SAS SQL using libnames to SQLSVR and libname to SAS it will become a different game.

That type of SQL will run but please think on what will happen. As the data has to be combined is some way the effect will be that the needed SQLSRV data (selected parts) will be downloaded to SAS and the join will run in the SAS environment. As the destination data is a SQLSRV table than all data is passed bck again to the RDBMS. This is very resource intensive on the network between those two.

With smaller datasets where the ease of coding is more important than computerresources this is a good approach.

When you have bigger datasets where performance and computer-resources are important. Do it not like that easy coding approach.

.

[Dob4Die]

Thanks for the response.

I did try using LIBNAME references for both SQL Database and SAS dataset and SQL UPDATE Statement . Find example below.But its taking so long to upload . Just to 50k records its taking 2 hours or so . Trying to optimize the code.

PROC SQL ;

UPDATE SQLDSN.SQL_TABLE A

SET  COL5 = (SELECT B.COL5

                               FROM LIBNAME.SASDATASET B

                 WHERE A.COL1 = B.COL1

                           AND A.COL2 = B.COL2

                   AND A.COL3 = B.COL3

                   AND A.COL4 = B.COL4)

,COL6 = (SELECT B.COL6

                               FROM LIBNAME.SASDATASET B

                 WHERE A.COL1 = B.COL1

                           AND A.COL2 = B.COL2

                   AND A.COL3 = B.COL3

                   AND A.COL4 = B.COL4)

,COL7 = (SELECT B.COL7

                              FROM LIBNAME.SASDATASET B

                 WHERE A.COL1 = B.COL1

                           AND A.COL2 = B.COL2

                   AND A.COL3 = B.COL3

                   AND A.COL4 = B.COL4)

,COL8 = (SELECT B.COL8

                 FROM LIBNAME.SASDATASET B

                 WHERE A.COL1 = B.COL1

                           AND A.COL2 = B.COL2

                   AND A.COL3 = B.COL3

                   AND A.COL4 = B.COL4)

WHERE EXISTS( SELECT 1

              FROM LIBNAME.SASDATASET B

    WHERE A.COL1 = B.COL1

                   AND A.COL2 = B.COL2

           AND A.COL3 = B.COL3

           AND A.COL4 = B.COL4)

;

QUIT;

[Patrick]

As Bill suggests the "best practice" is to upload the small SAS table into the data base and then perform the update of the large DBMS table there.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

[Bill]

An option that I use here is to upload the SAS dataset as a temporary Oracle table. I then have full use of it in the database environment.

[RW9]

I haven't tested the procedure myself, however the purpose of FedSQL is to integrate multiple sources.  Maybe have a look at that:

SAS(R) 9.4 FedSQL Language Reference, Third Edition

[jakarman]

It helps understanding why something is happening that way. An why some choices have  been made.

In the SQL world you are dealing historically with several DBA roles.  the SYSDBA and the restricted DBA. In their roles they are doing:

- MDL (Meta Data language) defining User Groups and all kind of other technical stuff

- DDL (Data Defnition Language) as defining the tables and their columns with all kind of restrictions (external keys indexes constraints)

- DML (Data manipulation Language) is what they offer as being the "application"   

As you are limited to only using DML the creation of tables is forbidden. This approach is not acceptable in an analytics world where the users are creating tables by them self. It is one of the reasons of the success of Excel doing it yourself without getting blocked by some DBA or other ICT guy. This background is the reason why SAS will need to copy all data to the SAS environment when something cannot be solved to run at the RDBMS environment.  

SAS Options to use.

- Analyse what is happening with SAStrace option. SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

- Put the DBIDIRECTEXEC option to be ON SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition as the goal is having as much as possible in the external DBMS.

  This one defaults top no. Seeing that one you must also define a connection that will be shared (connection=global) in the Libnames. 

- Table Sample Limiting Result Sets by Using TABLESAMPLE (TechNet MS) could be well translated to maxobs in SAS. Sometimes this translation is done correctly other times it is failing.

   Seen this with Teradata done correctly (SAMPLE <> MAXOBS) for proc SQL directly and as view but failing with a datastep view (views as SAS views).    

   When it works you can test verify functionality with a very small amount of data

First = location of the data.

As you are knowing what is allowed to store at the RDBMS and SAS doesn' t. Tha tis your first step. Add:

     Create  SQLDSN.SQL_TABLE_upd as select * FROM LIBNAME.SASDATASET  ;   /* this will cause un upload  from SAS to SQLserver */

Then updating that master SQL update table should run fast in SQ-server. The requirement is that you are having DDL access grants to that database. 

Temporary tables.

Not having DDL access grants to that database but allowing to use temporary tables at sufficient sizing (there are limits set by MDL SYSDBA role) you can used those.

Define both libnames with the same set options in SAS and using a shared connection and dbidirectexec active.    

Bill mentioned this one already. It is a good one as the data we also be cleaned up after your session.    SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

Second = Commit:

A RDBMS like SQLservers is designed for transactional processing.  The approach of updating the table should be fine. The only attention point will by locking (record or as a whole) 50 k records is a lot of IO when each of them gets journaled with before and after images and all of them have to wait on comitting the IO buffers. That is when the processing is done By SQL-server. Setting the DBcommit=0  should minimize that.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition