SAS Proc SQL中子查询GROUP BY失效致重复结果问题求助

核心问题原因 #

1. 子查询关联逻辑错误：原代码中子查询的WHERE C.ID = CITATIONS.ID是将子查询表与主查询已关联的CITATIONS表做自关联，未绑定当前设施的ID，导致统计的是整个CITATIONS表的总行数，而非单设施的罚单数量。
2. 重复行与GROUP BY失效：主查询直接LEFT JOIN CITATIONS表，每个罚单对应一条结果行，造成数据重复；同时GROUP BY仅指定CITATIONS.ID，但SELECT子句未使用聚合函数，SAS会自动丢弃GROUP BY子句，无法实现去重和聚合效果。

修正方案 #

方案1：预聚合罚单数据后关联 #

先对CITATIONS表按设施ID聚合统计罚单数量，再与主表关联，避免重复行并得到正确计数：

PROC SQL;
SELECT
    HP.Area
    ,HP.Name
    ,HP.NPI
    ,FACILITIES.ID
    ,COALESCE(CITATION_COUNT.Total_Citations, 0) AS Total_Citations
FROM EVAL.HP HP
LEFT JOIN EVAL.FACILITIES FACILITIES
    ON FACILITIES.NPI = HP.NPI
LEFT JOIN (
    SELECT ID, COUNT(*) AS Total_Citations
    FROM EVAL.CITATIONS
    GROUP BY ID
) CITATION_COUNT
    ON CITATION_COUNT.ID = FACILITIES.ID;
QUIT;

方案2：主查询中直接使用聚合函数 #

在主查询中通过COUNT函数统计，并将所有非聚合列加入GROUP BY，确保数据去重和正确计数：

PROC SQL;
SELECT
    HP.Area
    ,HP.Name
    ,HP.NPI
    ,FACILITIES.ID
    ,COUNT(CITATIONS.ID) AS Total_Citations
FROM EVAL.HP HP
LEFT JOIN EVAL.FACILITIES FACILITIES
    ON FACILITIES.NPI = HP.NPI
LEFT JOIN EVAL.CITATIONS CITATIONS
    ON CITATIONS.ID = FACILITIES.ID
GROUP BY HP.Area, HP.Name, HP.NPI, FACILITIES.ID;
QUIT;

方案说明 #

• 方案1优势：先聚合罚单数据，减少JOIN阶段的数据量，性能更优，适合CITATIONS表数据量大的场景。
• 方案2说明：COUNT(CITATIONS.ID)会自动忽略NULL值（无罚单的设施），统计结果为0；GROUP BY必须包含SELECT中所有非聚合列，否则SAS会报错或丢弃GROUP BY子句。
• COALESCE函数：用于将无罚单设施的NULL值转换为0，符合期望输出的显示要求。

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