SAS: How to copy a local sas table into oracle via SQL pass-through

Question

After making some searches that didn't lead to anything useful for me, I would like to ask you this question.

Some background: I would like to create an oracle table via two different methods in order to compare performances. Actually I want to copy a table from one of my local SAS libraries to Oracle.

I used a first method (that works perfectly) with a libname to oracle:

LIBNAME dblib ORACLE USER=usr PASSWORD="&dbpwd_sas" PATH="DM_CT_TEST" SCHEMA="SAS";

PROC SQL NOPRINT;
  CREATE TABLE dblib.TEST_WIN7 AS SELECT *
    FROM SASHELP.CARS
    WHERE STRIP(UPCASE(make)) EQ "ACURA"
  ;
QUIT;

LIBNAME dblib CLEAR;

But I also try to use another method via SQL pass-through that doesn't work:

PROC SQL NOPRINT;
  CONNECT TO ORACLE (USER=usr PASSWORD="&dbpwd_sas" PATH="DM_CT_TEST");

  EXECUTE (  CREATE TABLE sas.TEST_WIN7 AS
             SELECT * FROM SASHELP.CARS 
             WHERE STRIP(UPCASE(make)) EQ "ACURA"
          )  BY ORACLE;

  DISCONNECT FROM ORACLE;
QUIT;

With this method, SASHELP.cars is not found by the procedure.

So here is my question: Is it possible to copy a local SAS table into oracle via an SQL pass-through? If yes, how to proceed.

Thank you

Joe Joe · Accepted Answer · 2015-05-08T14:25:19

In SQL Passthrough, the connected session cannot see your SAS libraries, unless it is separately connected through DBMS-specific connection methods (unlikely). Pass-through is literally taking code and submitting it for execution on the remote DBMS, SAS is just a dumb terminal.

The only way you can access information from SAS in explicit pass-through is to transmit it as text in a macro variable. As such, you can perform in queries by putting the list of values in a macro variable and submitting it; but you cannot reference a SAS table directly. You could pass the contents of an entire table in a macro variable or a set of macro variables and then use insert statements to include them, but it's not a good idea; it won't be faster than using a libname connection and has a lot more risk of error (and is more work).

In most instances, you should simply use libname access when you want to see both SAS and RDBMS data at the same time. SAS will use implicit pass-through when it can to speed things up when using libname access.

If you're trying to use passthrough in order to avoid SAS having to download data to perform a join, one thing you can do is upload the SAS data to a global temporary table using libname access. Then you can connect via passthrough and use that temporary table. Just be aware that the two connections are separate, so they won't share non-global temporary tables. (I believe in newer versions they have added a feature to make them share connections, but I couldn't find the syntax and don't recall having much success using it myself.)

SAS: How to copy a local sas table into oracle via SQL pass-through

1 Answers