SAS技术问询：如何合并含内容一致但变量名不同的唯一标识的两个数据集

Hey there! No worries at all—this is a super common scenario when working with datasets, and it’s totally straightforward once you know the right tools for the job. Since you’ve got unique identifiers that match perfectly (just different column names), you won’t have to deal with messy duplicate matches, which makes this even easier.

Here’s how to do it in the most common tools:

Python (Pandas) #

Use the pd.merge() function, where you explicitly specify which columns to match from each dataset with left_on and right_on:

import pandas as pd

# Example datasets
df1 = pd.DataFrame({"customer_id": [101, 102, 103], "purchase_amount": [50, 75, 20]})
df2 = pd.DataFrame({"client_number": [101, 102, 104], "email": ["a@example.com", "b@example.com", "c@example.com"]})

# Merge on the matching unique IDs
merged_df = pd.merge(df1, df2, left_on="customer_id", right_on="client_number")

# Optional: If you want to keep all customers (even those missing from one dataset), use how="outer"
# merged_df = pd.merge(df1, df2, left_on="customer_id", right_on="client_number", how="outer")

This will combine rows where the IDs match, and you can adjust the how parameter to control whether you keep only matching rows (how="inner", default), all rows from the first dataset (how="left"), all from the second (how="right"), or all rows (how="outer").

R #

Use the base merge() function with by.x and by.y to specify the matching columns:

# Example datasets
df1 <- data.frame(customer_id = c(101, 102, 103), purchase_amount = c(50, 75, 20))
df2 <- data.frame(client_number = c(101, 102, 104), email = c("a@example.com", "b@example.com", "c@example.com"))

# Merge on the matching unique IDs
merged_df <- merge(df1, df2, by.x = "customer_id", by.y = "client_number")

# Optional: Keep all customers with all=TRUE
# merged_df <- merge(df1, df2, by.x = "customer_id", by.y = "client_number", all = TRUE)

Just like in Pandas, the all parameter lets you control the join type—all=TRUE is equivalent to a full outer join, all.x=TRUE is a left join, and all.y=TRUE is a right join.

SQL #

If you’re working with database tables, use a JOIN clause with an ON condition that matches the two ID columns:

-- Get all columns from both tables where IDs match (inner join)
SELECT *
FROM table1 t1
JOIN table2 t2
ON t1.customer_id = t2.client_number;

-- Optional: Full outer join to keep all customers
SELECT *
FROM table1 t1
FULL OUTER JOIN table2 t2
ON t1.customer_id = t2.client_number;

You can swap JOIN for LEFT JOIN or RIGHT JOIN to keep all rows from one table even if there’s no match in the other.

Since your IDs are unique and perfectly matched, you don’t have to worry about duplicate rows from the merge—each customer will only appear once in the result (assuming no duplicates in the original datasets, which you said are unique identifiers, so that’s covered!).

内容的提问来源于stack exchange，提问作者Fablad