SQl PAssthrough in SAS

Question

Are there any advantages in using SQL PAssthrough facility along with SAS.

Answer 1

Although this question is overly broad, I can provide an overly broad answer.

The pass-through SQL in SAS allows you to communicate directly with a database. This becomes very advantageous when you are using database specific functions. An example would be Oracle's stats functions. You do not have to worry about how SAS will handle your coding or translate your SQL.

Additionally, it has also been a benefit to us that Pass-through SQL requires very little processing on the SAS side. If you have an extremely busy SAS box, you can opt to send the processing logic directly to the database. This is possible without using Pass-through SQL, but you have a higher degree of control when utilizing it.

This is by no means an exhaustive list of benefits, simply a few high level perks to using pass-through SQL. If you have a more concrete use case, we can discuss the specific differences in coding techniques.

Answer 2

If you don't use the pass-through, then you have to import all the records (that you need for the processing) from the database to sas. By using the pass-through, you can have some processing done on the database side and bring over only the resulting records into sas. The difference (in terms of processing time and network usage) can very, from tiny to huge, depending on what you do.

Answer 3

PROC SQL will try and pass as much of the logic as it can to the database, but there are various times that it cannot. Using SAS functions that do not have equivalent in the database (or in the SAS/ACCESS engine for the database), will prevent passing the whole query to the database. When the query is not fully passed to the database, then the data is pulled into SAS and processed there. The more complicated your SQL is the more likely it will end up being processed in SAS. Here is a case that makes a larger difference than you might realize.

libname db path=dbserver user=... password=...; proc sql; create table db.new as select * from db.largedata where flag=1; quit;

This will actually (at least thru SAS 9.1.3) pull all the data that matches flag=1 down to SAS and then load it back into the database. It this is millions of rows it really slows down.

You would find expliect pass through much faster in this case.

proc sql; connect dbase (server=dbserver user=... password=...); execute (create table db.new as select * from db.largedata where flag=1) as dbase; disconnect dbase; quit;

I recently did an example using Oracle and a table with about 250,000 rows. The first way took 20 seconds and the second way to 2 seconds.

Answer 4

There are advantages to using passthrough, but it depends on what you're trying to accomplish. Generally, I use standard proc sql without the passthrough when doing queries. Recently, however, I've used it to generate some stored procs.

proc sql;
    connect to mysql(user = 'xxxxx' pass = 'xxxxx' server = 'localhost');
        execute(set @id = &id.) by mysql;
        execute(select (@lit:=image_text) from quality.links_image_text where image_id = @id) by mysql;
        execute(set @lidx = locate('ninja',@lit)) by mysql;
        execute(set @lidx2 = locate(' ',@lit,@lidx)) by mysql;
        execute(set @lidxd = @lidx2 - @lidx) by mysql;
        execute(set @lf = substr(@lit,@lidx,@lidxd)) by mysql;

        create table asdf as
        select &id. as id, a as ws from connection to mysql
        (select @lf as a)
        ;
    disconnect from mysql;
quit;

Clearly, that's not something that can be done outside of passthrough (at least not that I know of). So yea ... it all depends on what it is you're trying to accomplish.

Answer 5

Put simply, SQL pass-through statements give you more control over what gets sent to the database.