SAS: How to copy local sas table in oracle via SQL-pass-through
After some searching that has not led to anything useful to me, I would like to ask you this question.
Some prerequisites: I would like to create an oracle table using two different methods to compare characteristics. Actually I want to copy a table from one of my local SAS libraries to Oracle.
I used the first method (which works great) with libname for 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 am also trying another method through the SQL proxy which does not 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 was not found by this procedure.
So here is my question: Is it possible to copy a local SAS table to oracle via a pass-through SQL query? If so, how to proceed.
thank
source to share
In SQL Passthrough, the connected session cannot see your SAS libraries unless it is connected separately via DBMS connection methods (unlikely). Pass-through literally takes code and sends it to be executed on a remote DBMS, SAS is just a dumb terminal.
The only way to access information from SAS in explicit end-to-end access is to pass it as text in a macro variable. Thus, you can execute queries in
by putting a list of values in a macro variable and sending them; but you cannot directly reference the SAS table. You can pass the contents of an entire table into a macro variable or a set of macro variables and then use inserts to include them, but that's not a good idea; it won't be faster than using the libname join and has a much higher risk of error (and more work).
In most cases, you just need to use libname access when you want to see both SAS and RDBMS data at the same time. SAS will use implicit pass-through whenever possible to speed things up when using libname access.
If you are trying to use pass-through to avoid SAS to load data to make a connection, one thing you can do is load SAS data into a global temporary table using name access. Then you can connect via transit and use this temporary table. Just keep in mind that these two connections are separate, so they will not transfer non-global temporary tables. (I believe in newer versions they added a function to let them exchange connections, but I couldn't find the syntax and don't remember using it myself with much success.)
source to share