BookmarkSubscribeRSS Feed
alperebb
Calcite | Level 5

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?

7 REPLIES 7
s_lassen
Meteorite | Level 14

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
Super User

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
Calcite | Level 5

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. Screenshot (10).png

alperebb
Calcite | Level 5

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. Screenshot (10).png

SASKiwi
Opal | Level 21

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
Calcite | Level 5

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. Screenshot (10).png

SASKiwi
Opal | Level 21

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;

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 244 views
  • 0 likes
  • 4 in conversation