How to merge datasets in SAS

Match-merge with BY

proc sort data=orders;    by customer_id; run;
proc sort data=customers; by customer_id; run;

data orders_customers;
  merge orders(in=ino) customers(in=inc);
  by customer_id;
  if ino;            /* left join: keep every order */
run;

Without the sort, the DATA step stops with an error saying the data set isn't sorted by the BY variables.

IN= flags as join types

One-to-many and many-to-many

A one-to-many match-merge (many orders, one customer row) works as expected: the customer's values are carried to each order. If both datasets repeat a BY value, the log shows “NOTE: MERGE statement has more than one data set with repeats of BY values” and the result pairs rows by position, not every combination. Use PROC SQL for true many-to-many joins:

proc sql;
  create table o_c as
  select o.*, c.name, c.country
  from orders as o left join customers as c
    on o.customer_id = c.customer_id;
quit;

PROC SQL doesn't need sorted input.

Stacking with SET

data all_orders;
  set orders_2025 orders_2026 indsname=src;
  source = src;      /* which dataset each row came from */
run;

Variables with the same name but different lengths can truncate values when stacking or merging; set a LENGTH statement before SET or MERGE. The same joins in pandas, R and SQL are compared in merging data in pandas, R and SQL.

Checking the result

After a merge, compare the row count with the input: PROC SQL; SELECT COUNT(*) FROM orders_customers; QUIT;. With a left join from orders, the count should equal the orders dataset; more rows mean duplicate keys in customers.

Worked example with IN= flags

Orders has four observations (customer_id 101, 103, 101, 104) and customers has three (101, 102, 103). After sorting both by customer_id:

You can write several outputs in one pass, which is handy for reconciliation:

data matched no_customer no_orders;
  merge orders(in=ino) customers(in=inc);
  by customer_id;
  if ino and inc then output matched;
  else if ino then output no_customer;
  else output no_orders;
run;

The log reports 3, 1 and 1 observations for the three datasets.

Different key names, types and lengths

• Different names: rename on the fly with a dataset option: merge orders customers(rename=(cust_id=customer_id));.
• Numeric in one dataset, character in the other: the merge fails with an error that the variable has been defined as both character and numeric. Create a converted copy first, for example customer_id = put(cust_num, z6.); for zero-padded IDs, or input() to go the other way.
• Different lengths: when a character key is longer in the second dataset, SAS takes the length from the first dataset it meets, and longer values can be truncated or fail to match. A length customer_id $12; statement before MERGE avoids it.
• Variables with the same name that aren't keys: the value from the dataset listed last overwrites the earlier one. Rename or drop them with (rename=(...)) or (drop=...).

Troubleshooting

DATA step MERGE or PROC SQL?

• DATA step MERGE: fast on sorted data, and IN= flags make it easy to route matched and unmatched observations to separate datasets in one pass.
• PROC SQL: no sorting, handles many-to-many and joins on differently named keys or inequality conditions, and reads like SQL elsewhere.
• Hash objects in a DATA step: an option for large lookups without sorting the big dataset, once you are comfortable with the syntax.

To rerun the merge every month, keep the PROC SORT, MERGE and count checks in one program and point a %let macro variable at the new month's input, so the code itself doesn't change.

Frequently asked questions

Do I have to sort data before a SAS merge?

Yes, for a DATA step MERGE with BY, both datasets must be sorted (or indexed) by the BY variables. PROC SQL joins don't need sorting.

How do I do an inner join in a SAS DATA step?

Use IN= flags on both datasets and keep rows with IF a AND b;.

What does 'repeats of BY values' mean?

Both datasets have duplicate keys, so the DATA step merge pairs rows one-to-one in order. Use PROC SQL for a many-to-many join.

What is the difference between MERGE and SET in SAS?

MERGE with BY combines observations side by side by matching key values. SET stacks datasets one after another, like a union.

How do I merge datasets with different key variable names in SAS?

Rename the key in a dataset option, for example merge a b(rename=(cust_id=customer_id)); by customer_id;, or join on different names in PROC SQL.

How do I keep unmatched observations in separate datasets?

List several output datasets on the DATA statement and route rows with IF ina AND inb THEN OUTPUT matched; ELSE IF ina THEN OUTPUT no_match; and so on.

Related guides

• How to join multiple tables in SQL
• Why a SQL join returns too many rows, and how to fix it
• How to use MERGE (upsert) in BigQuery and Snowflake
• How to merge datasets in Stata
• How to combine two tables in SQL