Comparing Proc SQL Joins and DATA Step

 

 

DATA step match-merges and PROC SQL joins can produce the same results. However, there are some differences:

• A DATA step match-merge requires that the data be sorted, which is not required in Proc SQL.
• PROC SQL joins do not require that the columns in join expressions have the same name.
• PROC SQL joins can use comparison operators other than the equal sign (=).

Let's try to understand how these two are similar with the help of a simple example. Suppose we have two tables Auto Loan and Home Loan. Now we want a report where we have Cust_Id wise Auto and Home Loan Amount.

Auto Loan Table

Cust_Id	Auto_Loan_Amt
1001	Rs. 50,000
1002	Rs. 1,00,000
1003	Rs. 75,000
1004	Rs. 60,000
1005	Rs. 2,50,000
1008	Rs. 3,00,000
1009	Rs. 6,00,000

Home Loan Table

Cust_id	Home_Loan_Amt
1001	Rs. 35,00,000
1002	Rs. 80,00,000
1003	Rs. 50,00,000
1006	Rs. 70,00,000
1007	Rs. 90,00,000

Let’s compare the use of SQL joins and DATA step match-merges:

1. You want all the observations from Auto Loan as well as Home Loan tables:

Output:

Cust_Id	Auto_Loan_Amt	Home_Loan_Amt
1001	Rs. 50,000	Rs. 35,00,000
1002	Rs. 1,00,000	Rs. 80,00,000
1003	Rs. 75,000	Rs. 50,00,000
1004	Rs. 60,000
1005	Rs. 2,50,000
1006		Rs. 70,00,000
1007		Rs. 90,00,000
1008	Rs. 3,00,000
1009	Rs. 6,00,000

2. You want all the Cust_Id from Auto Loan and its matching observations from Home Loan table:

Output:

Cust_Id	Auto_Loan_Amt	Home_Loan_Amt
1001	Rs. 50,000	Rs. 35,00,000
1002	Rs. 1,00,000	Rs. 80,00,000
1003	Rs. 75,000	Rs. 50,00,000
1004	Rs. 60,000
1005	Rs. 2,50,000
1008	Rs. 3,00,000
1009	Rs. 6,00,000

3. You want all the Cust_Id from Home Loan and its matching observations from Auto Loan table:

Output:

Cust_Id	Auto_Loan_Amt	Home_Loan_Amt
1001	Rs. 50,000	Rs. 35,00,000
1002	Rs. 1,00,000	Rs. 80,00,000
1003	Rs. 75,000	Rs. 50,00,000
1006		Rs. 70,00,000
1007		Rs. 90,00,000

4. You want only the matching observations from Auto and Home Loan tables:

Output:

Cust_Id	Auto_Loan_Amt	Home_Loan_Amt
1001	Rs. 50,000	Rs. 35,00,000
1002	Rs. 1,00,000	Rs. 80,00,000
1003	Rs. 75,000	Rs. 50,00,000