topic Join Different DB's in SAS Programming

Join Different DB's

alperebb — Wed, 22 Mar 2023 07:08:09 GMT

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?

Re: Join Different DB's

s_lassen — Wed, 22 Mar 2023 08:56:09 GMT

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>;

Re: Join Different DB's

ballardw — Wed, 22 Mar 2023 09:01:03 GMT

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.

Re: Join Different DB's

SASKiwi — Wed, 22 Mar 2023 19:56:04 GMT

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.

Re: Join Different DB's

alperebb — Thu, 23 Mar 2023 06:03:43 GMT

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.

Re: Join Different DB's

alperebb — Thu, 23 Mar 2023 06:04:32 GMT

Re: Join Different DB's

alperebb — Thu, 23 Mar 2023 06:06:11 GMT

Re: Join Different DB's

SASKiwi — Thu, 23 Mar 2023 20:06:28 GMT

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;