SAS中LEFT JOIN与MERGE结果不一致问题及SAS代码转BigQuery实现需求

嘿，我来帮你理清这个问题！首先得搞清楚为什么你SAS里的PROC SQL LEFT JOIN和DATA STEP MERGE结果不一样，然后再把这些逻辑转换成BigQuery的SQL。

先说说两者结果不同的核心原因 #

SAS的两种匹配逻辑本质上处理多对多匹配的方式完全不一样：

• PROC SQL LEFT JOIN：只要满足list相等就会匹配，属于笛卡尔积式的连接。比如你的数据里，mydata有1条Mar，mydata2有2条Mar，这时候会生成1×2=2条重复记录。
• DATA STEP MERGE（先排序再按list合并，加in=t1）：是按排序后的顺序逐行匹配，属于一对一/一对多的匹配。同一list组内，mydata的第N行只会匹配mydata2的第N行，不会生成多对多的重复，最终只保留mydata的所有行，多余的mydata2行不会被保留。

拿你的示例数据算的话：

• X1（LEFT JOIN）最终会有6条记录
• X2（MERGE）最终会有5条记录

接下来是BigQuery的实现代码 #

第一步：创建对应SAS的两个源表

先把你的SASdatalines数据转换成BigQuery的建表语句，记得替换成你的项目和数据集名称：

-- 创建mydata表，对应SAS的mydata数据集
CREATE OR REPLACE TABLE `your-project.your-dataset.mydata` AS
SELECT 'Nov' AS list, 11 AS NDC, 50 AS discount UNION ALL
SELECT 'Dec' AS list, 12 AS NDC, 20 AS discount UNION ALL
SELECT 'Nov' AS list, 11 AS NDC, 50 AS discount UNION ALL
SELECT 'May' AS list, 12 AS NDC, NULL AS discount UNION ALL
SELECT 'Mar' AS list, 11 AS NDC, 30 AS discount;

-- 创建mydata2表，对应SAS的mydata2数据集
CREATE OR REPLACE TABLE `your-project.your-dataset.mydata2` AS
SELECT 'Jan' AS list, 11 AS NDC, 40 AS discount UNION ALL
SELECT 'Dec' AS list, 12 AS NDC, 20 AS discount UNION ALL
SELECT 'Mar' AS list, 10 AS NDC, 50 AS discount UNION ALL
SELECT 'May' AS list, 12 AS NDC, NULL AS discount UNION ALL
SELECT 'Mar' AS list, 11 AS NDC, 30 AS discount;

第二步：实现SAS的PROC SQL LEFT JOIN逻辑（生成X1）

这个直接用BigQuery的LEFT JOIN就能完美对齐，逻辑完全一致：

-- 对应SAS的X1结果：LEFT JOIN
CREATE OR REPLACE TABLE `your-project.your-dataset.x1` AS
SELECT 
  t1.*,
  t2.NDC AS ndc_t2,  -- 给t2的字段加后缀区分，避免重名
  t2.discount AS discount_t2
FROM `your-project.your-dataset.mydata` t1
LEFT JOIN `your-project.your-dataset.mydata2` t2
ON t1.list = t2.list;

第三步：实现SAS的DATA STEP MERGE逻辑（生成X2）

要模拟SAS的merge逻辑，需要给每个表的同list行加上行号，让同组内的行按顺序匹配，避免多对多的笛卡尔积：

-- 对应SAS的X2结果：按list排序后的merge（in=t1）
WITH mydata_ranked AS (
  SELECT 
    *,
    -- 按list分组生成行号，模拟SAS排序后的行顺序
    ROW_NUMBER() OVER(PARTITION BY list ORDER BY list) AS rn
  FROM `your-project.your-dataset.mydata`
),
mydata2_ranked AS (
  SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY list ORDER BY list) AS rn
  FROM `your-project.your-dataset.mydata2`
)
CREATE OR REPLACE TABLE `your-project.your-dataset.x2` AS
SELECT 
  t1.*,
  t2.NDC AS ndc_t2,
  t2.discount AS discount_t2
FROM mydata_ranked t1
LEFT JOIN mydata2_ranked t2
ON t1.list = t2.list AND t1.rn = t2.rn;

这样你就能在BigQuery里得到和SAS完全一致的X1和X2结果啦！

备注：内容来源于stack exchange，提问作者Matheus Coelho