Performing joins in SAS

sas

#1

Hi all,

I have started using SAS recently and have been stuck on a situation. I use the Enterprise editor for SAS.
Here is the scenario: I have joined 2 tables T1 and T2 for example. And then there are another 2 tables T3 and T4 which I have joined together.

Now I want to join the result of these 2 joins that I performed. So, how can I achieve this in SAS enterprise editor

If anyone can help, I will be very happy. Also screenshots would be of great help.

Best,
vatsy61


#2

Hi @vatsy61,

To join tables together, there must be a column or set of columns that can be used to match rows. The match can be of following type:

1. One-to-one-match: Here one row from the first table is joined with one row from the second table. The values of common column are same in both tables.
Screenshot%20from%202018-06-13%2010-44-42

2. One-to-many-match: Here one row from the first table is joined with multiple rows from second table. The values of common column are unique in only one table.
Screenshot%20from%202018-06-13%2010-45-16

3. Many-to-many-match: When the values of common columns are not unique in either of the table, each row of first table is joined with all the matching rows from the second table.
Screenshot%20from%202018-06-13%2010-45-24

So based on your problem, you can choose any of the above approach to join the tables. You can also modify the type of joins that will help you to join two tables. Various types of joins are:

  • Inner joins: It will keep only rows that match in both the tables.
    Screenshot%20from%202018-06-13%2011-09-05

  • Left joins: It will keep all the rows from the table on the left even if they do not match in the other table.
    Screenshot%20from%202018-06-13%2011-11-20

  • Right joins: It will keep all the rows from the table on the right even if they do not match in the other table.
    Screenshot%20from%202018-06-13%2011-13-07

  • Full outer joins: It will keep all the rows from both the tables.
    Screenshot%20from%202018-06-13%2011-14-07

To join two tables manually, you should select the field by which you want to join the table with another and drag it to the corresponding field of the other table. After you finish dragging, a line connecting the linked fields will appear. Key cardinality symbols are placed at the ends of link when the corresponding relationship exists in the database.


#3

Hi PulkitS,

Thanks for the reply. It did help!

Best,
vatsy61