Learning SAS? Welcome to the exclusive online community for all SAS learners.

Join Tables in Proc SQL

Reply
N/A
Posts: 1

Join Tables in Proc SQL

Hi All,

I have started learning PROC SQL to increase efficiency of my work. Recently I was given work to combine around 15 tables.

Please can anyone help me learn and find the solution to the following:

  1. Join 15 tables having common key "ID".
  2. While joining make sure all the information from all the tables is included in the FINAL DATASET.

I have tried using DATA STEP using the merge statement, but it did not help me.

Please can anyone help me make this work in PROC SQL.

Thank you!!

Super Contributor
Posts: 578

Re: Join Tables in Proc SQL

Posted in reply to beginner_2014

basic syntax is:

proc sql;

create table want as

select

     *

from

     have1 t1

     {join type} have2 t2

          on t1.id=t2.id

     {join type} have3 t3

          on t1.id=t3.id

...

;

quit;

You have to understand the different join types.  Does one of your tables function as a master?

Super User
Super User
Posts: 7,962

Re: Join Tables in Proc SQL

To add to DBailey's text, you also need to be aware of the full structure of your data.  I.e. does any of the tables have the same variable names, are they different types, should there be duplicates, etc.  Simply this, know your data and actually putting it together should be straight forward.

Why for instance is there 15 tables to join, that sounds a fair bit and could be more effective using a datastep merge, however, you may also want to consider creating views depending on scenario, or generating the code.

SAS Employee
Posts: 20

Re: Join Tables in Proc SQL

Posted in reply to beginner_2014

Have a look at the information of the following paper "Exploring DATA Step Merges and PROC SQL Joins". It explains the differences and options of joins using data step and proc sql and gives some examples. Here is a link to the paper: http://support.sas.com/resources/papers/proceedings12/251-2012.pdf 

And here are some samples that may be usefull:

If you have time to read you can also have a look at some books (you can read them online if have access to safaribooks):

Ask a Question
Discussion stats
  • 3 replies
  • 377 views
  • 0 likes
  • 4 in conversation