Using PROC SQL and MERGE in SAS

Welcome back to Mindful Data Minds! In this session, we’ll learn how to use PROC SQL in SAS to run SQL queries, and how to join datasets using both PROC SQL and the Data Step MERGE statement.

Watch the Full Tutorial

What You Will Learn

• How to write SQL queries in SAS using PROC SQL.
• The difference between INNER, FULL, LEFT, and RIGHT joins.
• How to replicate joins using Data Step MERGE.
• Key differences: MERGE requires sorted data and identical key names, while PROC SQL is more flexible.

Writing SQL in SAS

In SAS, SQL queries are written using PROC SQL:

proc sql;
   select * from sashelp.cars;
quit;

Note: Always end with QUIT (not RUN) to terminate the SQL session.

You can also create new datasets:

proc sql;
   create table a as
   select * from sashelp.cars;
quit;

Types of Joins

SQL supports four common joins:

• INNER JOIN → Only matching rows from both tables.
• FULL JOIN → All rows from both tables.
• LEFT JOIN → All rows from the left table + matching rows from the right.
• RIGHT JOIN → All rows from the right table + matching rows from the left.

Example Tables

We’ll create two sample tables left & right:

data left;
input id name $;
cards;
1 A
2 B
4 D
5 E
6 F
;
run;

data right;
input id salary;
cards;
1 45421
2 1512
3 54512
4 1265
5 24821
6 54256
;
run;

Joining with PROC SQL

Inner Join

proc sql;
   create table s_inner as
   select a.id, a.name, b.salary
   from left as a
   inner join right as b
   on a.id = b.id;
quit;

Result: IDs 1, 2, 4, 5, 6.

Full Join

proc sql;
   create table s_full as
   select coalesce(a.id, b.id) as id,
          a.name, b.salary
   from left as a
   full join right as b
   on a.id = b.id;
quit;

Result: IDs 1–6, including 3 from the right table.

Left Join

proc sql;
   create table s_left as
   select a.id, a.name, b.salary
   from left as a
   left join right as b
   on a.id = b.id;
quit;

Result: All rows from left table.

Right Join

proc sql;
   create table s_right as
   select coalesce(a.id, b.id) as id,
          a.name, b.salary
   from left as a
   right join right as b
   on a.id = b.id;
quit;

Result: All rows from right table.

Joining with Data Step MERGE

You can also join datasets using MERGE:

data d_inner;
   merge left(in=x) right(in=y);
   by id;
   if x and y; /* Inner join */
run;

• in= option → Creates Boolean flags (1 if record exists, 0 otherwise).
• BY statement → Requires sorted data by the key variable.
• Conditions:
  • if x and y; → Inner join.
  • if x; → Left join.
  • if y; → Right join.
  • No condition → Full join.

MERGE requires:

• Common key with same name and type.
• Data sorted by the key.

Note: PROC SQL does not have these restrictions.

Next Step

Continue learning by exploring the next tutorial in this series. Also subscribe to get notified about new lessons.

Have a Question?

Drop your doubts in the comments below or contact us.