topic Re: How to query two databases at once. in SAS Programming

How to query two databases at once.

cellurl — Wed, 26 Jul 2017 14:38:37 GMT

I want to do something like this:

proc ??
   create table tmp as 
   select 
      t.*,
      o.*
   from
      teradadata t,
      oracle o
   ;

Re: How to query two databases at once.

RW9 — Wed, 26 Jul 2017 14:49:06 GMT

Well, there is FedSQL:

http://support.sas.com/documentation/cdl/en/fedsqlref/67364/HTML/default/viewer.htm#fedsqlrefwhatsnew94.htm

Never used it myself. What is it your trying to do as that code doesn't seem logical or valid. Why not just pull in both data sources then process, or pull in the smaller and use that in a query to the larger.

Re: How to query two databases at once.

nirajp91 — Wed, 26 Jul 2017 15:08:40 GMT

If you have a common identifier between the two sources, you can simple do a join/merge operation

proc sql;

create table test as

select

a.id,

a.name,

b.age

from table1 as a

left join table 2 as b on (a.id=b.id);

quit;

Refer to: https://communities.sas.com/t5/SAS-Procedures/LEFT-JOIN-in-SAS-using-PROC-SQL/td-p/158240

Re: How to query two databases at once.

cellurl — Wed, 26 Jul 2017 15:40:00 GMT

the teradata part is GB's.

Re: How to query two databases at once.

cellurl — Wed, 26 Jul 2017 15:43:36 GMT

proc sql;
   connect ... to Teradata;
   connect ... to oracle;
   create table tmp as select * from connection to oracle, Teradata
   (
       select 
           t.*,
           o.*
       from
           Teradata t,
           oracle o
      );

//something like this,   DOUBLE connection. Is that possible???

Re: How to query two databases at once.

LinusH — Wed, 26 Jul 2017 20:57:51 GMT

No, not like that.
I'm missing a join criteria in you example.
Your options are joining via SAS implicit pass through , risking that both tables would download to SAS.
If your Oracle table is relative small try to copy it to a TD volatile table so that the join will performed completely in TD.
Or perhaps there is a possibility to connect directly to Oracle from TD?