解决SAS汇总统计重合并警告及Access SQL转Proc SQL问题

问题描述 #

将Access查询转换为SAS Proc SQL后，返回大量重复记录，且触发警告：The query requires remerging summary statistics back with the original data，但Access执行无此问题。核心差异在于ComboQuery中SumofSum类变量的处理逻辑，Access与SAS的聚合规则存在不同，需优化代码改写方案。

原始代码 #

Access SQL #

Rev_Query

SELECT
       [Rev].Contract,
       [Rev].[Posting Date],
       [Rev].[Documnet Number],
       Sum([Rev].Amount) AS SumOfAmount,
       Sum([Rev].[Reclass Amt]) AS [SumOfReclass Amt]
FROM   [Rev]
GROUP  BY 
       [Rev].Contract,     
       [Rev].[Posting Date],   
       [Rev].[Documnet Number];  

Remit_Query

SELECT 
       [Remit].[ID],
       Sum([Remit].[CCHS Amt]) AS [SumOfCCHS Amt]
FROM   [Remit Data]
GROUP  BY 
       [Remit Data].[ID];  

ComboQuery

SELECT 
       [Rev_Query].Contract,
       Sum([Rev_Query].SumOfAmount) AS SumOfSumOfAmount,
       Sum([Rev_Query].[SumOfReclass Amt]) AS [SumOfSumOfReclass Amt],
       Sum([Remit_Query].[SumOfCCHS Amt]) AS [SumOfSumOfCCHS Amt],
       Sum(Round(Nz([Rev_Query]![SumOfReclass Amt]) + Nz([Remit_Query]![SumOfCCHS Amt]), 2)) AS Expr1
FROM   [Remit_Query]
RIGHT JOIN 
       [Rev_Query]
ON 
       [Remit_Query].[ID] = [Rev_Query].[Documnet Number]
GROUP  BY 
       [Rev_Query].Contract
HAVING 
       (((Sum(Round(Nz([Rev_Query]![SumOfReclass Amt]) + Nz([Remit_Query]![SumOfCCHS Amt]), 2)) ) <> 0 ));  

SAS 原始转换代码 #

Rev_Query

proc sql;
create table hw.Rev_Query as
select 
   t1.Contract,
   t1.Posting_Date,
   t1.Document_Number,
   sum(t1.Amount) as SumOfAmount,
   sum(t1.Reclass_Amt) as SumOfReclassAmt
from hw.Revenue as t1
group by 1,2,3;
quit;

Remit_Query

proc sql;
create table hw.Remit_Query as
select 
   t1.ID,
   sum(t1.CCHS_Amt) as SumOfCCHS_Amt
from hw.Remit as t1
group by 1;
quit;

ComboQuery

proc sql;
create table hw.ComboQuery as
select 
   t1.Contract,
   sum(t1.SumOfAmount) as SumOfSumOfAmount,
   sum(t1.SumOfReclassAmt) as SumOfSumOfReclassAmt,
   sum(t2.SumOfCCHS_Amt) as SumOfSumOfCCHS_Amt,
   sum(t1.SumOfReclassAmt,t2.SumOfCCHS_Amt) as Expr1
from hw.Remit_Query t2
right join hw.Rev_Query t1 on t2.ID = t1.Document_Number
group by 1
having Expr1 <> 0;
quit;

问题原因分析 #

1. 重复记录来源：Rev_Query按Contract, Posting_Date, Document_Number聚合，每个Contract对应多条记录；与Remit_Query（按ID聚合）Join后，若一个ID对应多个Rev_Query记录，会产生重复行，后续对已聚合字段再次SUM会重复计算值，导致结果膨胀。
2. 重合并警告：SAS Proc SQL中，当GROUP BY的字段未包含所有非聚合字段（此处SUM(t1.SumOfAmount)的基础字段SumOfAmount未在GROUP BY中），会触发重合并逻辑，将聚合结果与原始数据重新匹配，导致警告且效率低下。Access的SQL引擎对这种嵌套聚合的处理更宽松，自动规避了此类问题。

优化方案 #

方案1：合并为单查询，直接从原始表聚合 #

跳过中间表，直接在主查询中完成两次聚合逻辑，避免Join后的重复计算：

proc sql;
create table hw.ComboQuery as
select 
    rev_agg.Contract,
    sum(rev_agg.SumOfAmount) as SumOfSumOfAmount,
    sum(rev_agg.SumOfReclassAmt) as SumOfSumOfReclassAmt,
    sum(remit_agg.SumOfCCHS_Amt) as SumOfSumOfCCHS_Amt,
    round(sum(rev_agg.SumOfReclassAmt) + sum(coalesce(remit_agg.SumOfCCHS_Amt, 0)), 2) as Expr1
from (
    -- 先完成Rev表的第一次聚合，同原Rev_Query
    select 
        Contract,
        Document_Number,
        sum(Amount) as SumOfAmount,
        sum(Reclass_Amt) as SumOfReclassAmt
    from hw.Revenue
    group by Contract, Document_Number, Posting_Date
) rev_agg
right join (
    -- 先完成Remit表的聚合，同原Remit_Query
    select 
        ID,
        sum(CCHS_Amt) as SumOfCCHS_Amt
    from hw.Remit
    group by ID
) remit_agg on rev_agg.Document_Number = remit_agg.ID
group by rev_agg.Contract
having calculated Expr1 <> 0;
quit;

方案2：优化ComboQuery，先聚合再Join #

先对Rev_Query按Contract预聚合，再与Remit_QueryJoin，避免重复计算：

-- 先创建Rev的合同级聚合表
proc sql;
create table hw.Rev_Contract_Agg as
select 
    Contract,
    sum(SumOfAmount) as SumOfSumOfAmount,
    sum(SumOfReclassAmt) as SumOfSumOfReclassAmt
from hw.Rev_Query
group by Contract;
quit;

-- 创建Remit的合同级关联聚合（按Rev的Contract匹配）
proc sql;
create table hw.Remit_Contract_Agg as
select 
    rev.Contract,
    sum(remit.SumOfCCHS_Amt) as SumOfSumOfCCHS_Amt
from hw.Rev_Query rev
left join hw.Remit_Query remit on rev.Document_Number = remit.ID
group by rev.Contract;
quit;

-- 最终合并
proc sql;
create table hw.ComboQuery as
select 
    a.Contract,
    a.SumOfSumOfAmount,
    a.SumOfSumOfReclassAmt,
    b.SumOfSumOfCCHS_Amt,
    round(a.SumOfSumOfReclassAmt + coalesce(b.SumOfSumOfCCHS_Amt, 0), 2) as Expr1
from hw.Rev_Contract_Agg a
left join hw.Remit_Contract_Agg b on a.Contract = b.Contract
where calculated Expr1 <> 0;
quit;

优化说明 #

• 使用coalesce()替代Access的Nz()，实现一致的空值处理逻辑。
• 方案1通过子查询合并两次聚合逻辑，减少中间表存储，从根源避免Join后的重复行导致的重复计算。
• 方案2先按Contract完成聚合，确保每个Contract仅对应一条记录，彻底消除重复记录和重合并警告。

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