SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Join Multiple tables from different database

Accepted Solution Solved
Reply
Super Contributor
Posts: 625
Accepted Solution

Join Multiple tables from different database

I'm extracting fields from different databases (DB2, Teradata..)/tables as shown in the image below and now I'm not certain to join all those variables to create a one single SAS dataset. Appreciate if someone of you guide me here.

 

E.g. Assume I'm extracting field A from table X, field B from table Y and field C from table Z and now I want to create a table XYZ which should have the variables A, B and C

 

SAS JOB_3.JPG

 

 


Accepted Solutions
Solution
‎04-09-2018 03:55 AM
Respected Advisor
Posts: 4,672

Re: Join Multiple tables from different database

@Babloo

You've got the following questions to answer first:

1. What's the logic?

- relationships between the tables

- Join logic

2. Performance

- How can you reduce volumes most efficiently on the data base side before pulling the data into SAS for further processing (joining the tables).

 

Only when you're really clear about what needs to be done start with implementation using DIS.

View solution in original post


All Replies
Respected Advisor
Posts: 2,812

Re: Join Multiple tables from different database

proc sql;
    create table xyz as select x.a, y.b, z.c from
         x left join y on  .....  left join z on .....  ;
quit;
        

Since you didn't specify the joining criteria, I have left those as ......

--
Paige Miller
Super Contributor
Posts: 625

Re: Join Multiple tables from different database

Posted in reply to PaigeMiller
What if I don't have common variables between those tables?
Solution
‎04-09-2018 03:55 AM
Respected Advisor
Posts: 4,672

Re: Join Multiple tables from different database

@Babloo

You've got the following questions to answer first:

1. What's the logic?

- relationships between the tables

- Join logic

2. Performance

- How can you reduce volumes most efficiently on the data base side before pulling the data into SAS for further processing (joining the tables).

 

Only when you're really clear about what needs to be done start with implementation using DIS.

Super Contributor
Posts: 625

Re: Join Multiple tables from different database

If there is a relationship between the tables, can I also lookup
transformation instead of Join transformation?
Respected Advisor
Posts: 2,812

Re: Join Multiple tables from different database


@Babloo wrote:
If there is a relationship between the tables, can I also lookup
transformation instead of Join transformation?

These are questions you have to answer, by learning about the databases involved.

--
Paige Miller
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 240 views
  • 3 likes
  • 3 in conversation