在SAS环境中实现两张Snowflake表的循环计算需求

核心逻辑 #

按Category分组，从表A的初始记录开始，利用RETAIN语句保留上期期末金额，迭代计算后续月份的期初、期末值。

代码示例 #

/* 从Snowflake拉取并合并初始数据与净额数据 */
proc sql;
    connect to snowflake (server='你的服务器地址' database='库名' schema='模式名' warehouse='仓库名' role='角色名' user='用户名' password='密码');
    create table combined_data as
        select Category, Month, Beginning_Dollar_AMT, End_Dollar_AMT, . as Net_Dollar_balance
        from connection to snowflake (select * from 表A)
        union all
        select Category, Month, . as Beginning_Dollar_AMT, . as End_Dollar_AMT, Net_Dollar_balance
        from connection to snowflake (select * from 表B);
    disconnect from snowflake;
quit;

/* 按类别和月份排序，保证迭代顺序 */
proc sort data=combined_data;
    by Category Month;
run;

/* 迭代计算期初、期末金额 */
data table_c;
    set combined_data;
    by Category;
    retain curr_end;
    
    if first.Category then do;
        /* 初始化：取表A的初始期末值 */
        curr_end = End_Dollar_AMT;
        /* 输出初始记录 */
        if not missing(Beginning_Dollar_AMT) then do;
            Beginning_balance_amt = Beginning_Dollar_AMT;
            output;
        end;
    end;
    
    /* 处理后续月份的净额数据 */
    if missing(Beginning_Dollar_AMT) then do;
        Beginning_balance_amt = curr_end;
        End_Dollar_AMT = min(Beginning_balance_amt, Net_Dollar_balance);
        curr_end = End_Dollar_AMT;
        output;
    end;
    
    keep Category Month Beginning_balance_amt End_Dollar_AMT;
run;

核心逻辑 #

用递归CTE按Category分组，从表A的初始记录出发，逐行关联表B的后续净额数据，迭代计算每期的期初、期末值。

代码示例 #

WITH initial_records AS (
    -- 提取表A的初始数据
    SELECT 
        Category,
        Month,
        Beginning_Dollar_AMT AS Beginning_balance_amt,
        End_Dollar_AMT
    FROM 表A
),
sorted_net_data AS (
    -- 给表B的净额数据按类别和月份排序，生成迭代序号
    SELECT 
        Category,
        Month,
        Net_Dollar_balance,
        ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Month) AS iter_seq
    FROM 表B
),
recursive_calculation AS (
    -- 递归起始：初始记录
    SELECT 
        Category,
        Month,
        Beginning_balance_amt,
        End_Dollar_AMT,
        0 AS iter_seq
    FROM initial_records
    
    UNION ALL
    
    -- 递归迭代：关联下一期净额数据，计算当期值
    SELECT 
        snd.Category,
        snd.Month,
        rc.End_Dollar_AMT AS Beginning_balance_amt,
        MIN(rc.End_Dollar_AMT, snd.Net_Dollar_balance) AS End_Dollar_AMT,
        snd.iter_seq
    FROM recursive_calculation rc
    JOIN sorted_net_data snd 
        ON rc.Category = snd.Category 
        AND snd.iter_seq = rc.iter_seq + 1
)
-- 生成最终表C
SELECT 
    Category,
    Month,
    Beginning_balance_amt,
    End_Dollar_AMT
INTO 表C
FROM recursive_calculation
ORDER BY Category, Month;

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