SAS SQL Pass Through

Question

I would like to know what gets executed first in the SAS SQL pass thru in this code:

Connect To OLEDB As MYDB ( %DBConnect( Catalog = MYDB ) ) ;

 Create table MYDB_extract as
   select put(Parent,$ABC.) as PARENT,
          put(PFX,z2.) as PFX,* 
    From Connection To MYDB
     ( SELECT
           Appointment,Parents,Children,Cats,Dogs 
       FROM MYDB.dbo.FlatRecord
        WHERE Appointment between '20150801' and '20150831' 
           And Children > 2);
Disconnect from MYDB;

Since MS SQL-Server doesn't support the PUT function will this query cause ALL of the records to be processed locally or only the resultant records from the DBMS?

Note that you are referencing a variable `PFX` that is not included in your query. Also you are referencing PARENT instead of PARENTS. — Tom, Sep 12 '16 at 19:39

score 0 · Answer 1 · answered Sep 12 '16 at 19:38

The explicit pass-through query will still process and will return to SAS what it returns (however many records that is). Then, SAS will perform the put operations on the returned rows.

So if 10000 rows are in the table, and 500 rows meet the criteria in where, 500 records will go to SAS and then be put; SQL will handle the 10000 -> 500.

If you had written this in implicit pass through, then it's possible (if not probable) that SAS might have done all of the work.

Note that the errors Tom noted would still be problems here. — Joe, Sep 12 '16 at 19:45

score 0 · Answer 2 · answered Sep 13 '16 at 00:03

First the code in the inline view will be executed on the server:

SELECT Appointment,Parents,Children,Cats,Dogs 
FROM   MYDB.dbo.FlatRecord
WHERE  Appointment between '20150801' and '20150831' And Children > 2

Rows that meet that WHERE clause will be returned by the DBMS to SAS over the OLDEB connection.

Then SAS will (try and) select from that result set, applying any other code, including the put functions.

This isn't really any different from how an inline view works in any other DBMS, except that here you have two different database engines, one running the inner query and SAS running the outer query.

SAS SQL Pass Through

2 Answers2

Linked