topic SQL - ODBC - using "in" from a local dataset in SAS Procedures

SQL - ODBC - using "in" from a local dataset

Jay — Mon, 18 May 2009 15:56:11 GMT

proc sql;
connect to odbc(dsn=DSNname uid=user_id password=XXXXXXX);
create table regis as
SELECT distinct
ID,
other_vars,
last_var

FROM connection to odbc (select * from oracle_odbc_table)
where id in (select non_retain.id from local.dataset);

QUIT;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The above code creates the error message: ERROR: The following columns were not found in the contributing tables: (and it list the vars from the select statement)

The only var in the local dataset are the ids that I want to pass to the where clause.

Is this possible to pass information through a where clause via ODBC from a local dataset or am I doing something wrong?

Re: SQL - ODBC - using "in" from a local dataset

Doc_Duke — Mon, 18 May 2009 19:18:59 GMT

You are effectively doing a join between a Oracle table and a local table, but you appear to be mixing pass-through and LIBNAME syntax. If you want to joint an ODBC connected table to a local table, you have to use the LIBNAME syntax. Be warned, however, that the join will occur completely on the SAS side; Oracle has to send the entire table across the wire.

If you have adequate permissions, you might be able to upload the ID list to Oracle and do the join there.

Another approach, sort of a last resort, would be to use DATA step to build the code with the WHERE clause listing the actual IDs, saving the code with PUT statements, and the %INCLUDE-ing it later in the job. I use this approach only when the two approaches above are not feasible.

Doc Muhlbaier
Duke

Re: SQL - ODBC - using "in" from a local dataset

Jay — Mon, 18 May 2009 19:43:10 GMT

>>>mixing pass-through and LIBNAME syntax

Yes, exactly what I was trying to do.

>>Oracle has to send the entire table across the wire

Trying to join it via the libname was just to slow.

Speed / time wise, found it faster to just change my where clause, bring over all of the records from the next period and perform the join on my local SAS session.

Thxs,
Jay