BookmarkSubscribeRSS Feed
Jay
Calcite | Level 5 Jay
Calcite | Level 5
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?
2 REPLIES 2
Doc_Duke
Rhodochrosite | Level 12
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
Jay
Calcite | Level 5 Jay
Calcite | Level 5
>>>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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1049 views
  • 0 likes
  • 2 in conversation