Help using Base SAS procedures

connecting an ODBC table to a local table

Reply
Super Contributor
Posts: 401

connecting an ODBC table to a local table

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;

Trusted Advisor
Posts: 2,116

Re: connecting an ODBC table to a local table

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

Ask a Question
Discussion stats
  • 1 reply
  • 569 views
  • 0 likes
  • 2 in conversation