connect to odbc(dsn=DSNname uid=user_id password=XXXXXXX);
create table regis as
FROM connection to odbc (select * from oracle_odbc_table)
where id in (select non_retain.id from local.dataset);
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?
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.