connecting an ODBC table to a local table

podarum — Sat, 09 Feb 2013 05:12:28 GMT

Hi, I'm trying to connect an ODBC table to a local table.. so if I have 2000 record in the local table, I would expect to match to the ODBC table and get 2000 records from it. here is the code I have (below) but for some reason I get way more than 2000 records..since the ODBC table has 50,000... what am I doing wrong? thanks

rsubmit;

PROC SQL;

connect to teradata(user=&user. password=&password. Mode=Teradata tdpid=sdf);

create table A.Data (compress=yes reuse=yes) as

(select a.v_respn_no, a.*, b.*

from A.Local_Table a inner join

(Select * from connection to teradata

(select * from ODBC.Table2 where l_r_id = 'y')) b

on a.evnt_id = b.evnt_id and a.evnt_sys_id = b.evnt_sys__id

group by 1

);

quit;

endrsubmit;

Re: connecting an ODBC table to a local table

Doc_Duke — Mon, 11 Feb 2013 14:39:10 GMT

We can't tell just from the code why it didn't work; there could be a number of reasons.

One comment: When you do a query that involves a local table and a database table, SAS brings the data from the database table over to the SAS server for the join; that can really hammer your performance. If your teradata DBA will allow it, you could get much better through-put by uploading your IDs from a.local_table to the teradata server and do that part of the join there.

Doc Muhlbaier

Duke

topic connecting an ODBC table to a local table in SAS Procedures

connecting an ODBC table to a local table

Re: connecting an ODBC table to a local table