Join Different DB's

[alperebb]

Hello,

Before we dive into data analysis in SAS Viya, we are currently doing data mapping. And we found that some of the data we need to merge are in different databases. For example, I need to join a table in the SAPHANA database with a table in the INCADEA database. But I can't figure out how to do this, can you help me?

[s_lassen]

Use SAS. I assume that the mentioned databases are hosted on e.g. SQL Server or Oracle databases. So you can assign libnames for the databases in SAS and make the join there, e.g.

libname saphana <odbc or oracle connect options>;
libname incadea <odbc or oracle connect options>;
proc sql;
  create table joined as select <columns>
    from saphana.tableX join incadea.tableY
       on <join conditions>;
            

[ballardw]

If the databases involved are different external databases you may need a separate module licenses for each one such as Oracle, DB2, SQL. 

You may have some headaches with getting certain data types, especially date, time or datetime values, into a form that allows working with them if in different data bases.

[alperebb]

I'm sharing a screenshot below, it's all in Data Sources. I want to go into INCADEA-PIST and take a table from there and a table from SAPHANA a few rows below and take a table from there and merge them. By the way, I took the screenshot from SAS Data Explorer.

[alperebb]

I'm sharing a screenshot below, it's all in Data Sources. I want to go into INCADEA-PIST and take a table from there and a table from SAPHANA a few rows below and take a table from there and merge them. By the way, I took the screenshot from SAS Data Explorer.

[SASKiwi]

Are these databases in the same database product (Oracle, SQL Server etc) or not? If they are in the same database product are they on the same database server or not? If they are in different database products you will most likely have to read the required tables into SAS before doing any joining. If they are in the same database product you may be able to do joins between databases and extract the results back to SAS.

[alperebb]

I'm sharing a screenshot below, it's all in Data Sources. I want to go into INCADEA-PIST and take a table from there and a table from SAPHANA a few rows below and take a table from there and merge them. By the way, I took the screenshot from SAS Data Explorer.

[SASKiwi]

The screenshot indicates that INCADEA-PIST is an SQL Server database and SAPHANA is a SAP HANA database - no indication of the underlying database product here. I can only suggesting trying an SQL join using the SAS libraries first and see how you get on:

proc sql;
  create table Want as 
  select *
  from INCADEA.Table1 as A
  INNER JOIN SAPHANA.Table2 as B
  on A.column = B.column
;
quit;