在SAS中为账户组生成12个月滚动窗口二元变量最大值字段的问题

需求说明 #

需创建字段Defaulted_Ind_12m_Max，按账户分组、每月计算：取当前月份t及未来12个月内Defaulted_Ind的最大值，赋值到对应时间点。

现有代码问题 #

原代码中哈希表采用逐行加载逻辑：在set Default_Ind_01过程中，每读取一行就执行h.add()存入哈希表。但SAS是顺序处理数据，当处理较早月份（如Jan2020）时，该账户后续月份的数据还未被读入，哈希表中无对应记录，导致h.find()无法获取未来月份的Defaulted_Ind值，最终计算的最大值不符合预期。

示例数据 #

输入数据集（have） #

DATA have;
INPUT acct 1. Date $10. DefaultInd_g1 2.;
DATALINES;
1   01Jan2020   0
1   01Feb2020   0
1   01Mar2020   0
1   01Apr2020   1
2   01Jan2020   1
2   01Feb2020   0
2   01Mar2020   0
2   01Apr2020   1
;
RUN;

期望输出数据集（want） #

注：示例为当前及未来3个月的最大值（Worst_Ever_3m），对应需求中12个月的逻辑：

DATA want;
INPUT acct 1. Date $10. DefaultInd_g1 2. Worst_Ever_3m 2.;
DATALINES;
1   01Jan2020   0   0  /* Jan-Mar无违约，最大值0 */
1   01Feb2020   0   1  /* Feb-Apr中Apr违约，最大值1 */
1   01Mar2020   0   1  /* Mar-Apr中Apr违约，最大值1 */
1   01Apr2020   1   1  /* Apr本身违约，最大值1 */
2   01Jan2020   1   1  /* Jan-Mar中Jan违约，最大值1 */
2   01Feb2020   0   0
2   01Mar2020   0   0
2   01Apr2020   0   0
;
RUN;

修正方案 #

方法1：修正哈希表加载逻辑（先全量加载所有数据） #

先将整个数据集存入哈希表，再重新读取原数据集进行未来月份查找，确保所有未来数据已存在于哈希中：

/* 第一步：预加载所有数据到哈希表并保存为临时数据集 */
data _null_;
    if _N_ = 1 then do;
        declare hash h(dataset:'Default_Ind_01');
        h.defineKey('main_acct_number', 'date');
        h.defineData('defaultind_g1', 'defaultind_g0');
        h.defineDone();
        h.output(dataset:'hash_data');
    end;
    stop;
run;

/* 第二步：读取原数据集，查找当前及未来12个月的指标值并计算最大值 */
data Default_Ind_02;
    if _N_ = 1 then do;
        declare hash h(dataset:'hash_data');
        h.defineKey('main_acct_number', 'date');
        h.defineData('defaultind_g1', 'defaultind_g0');
        h.defineDone();
    end;

    set Default_Ind_01;
    by main_acct_number date;

    /* 初始化临时数组存储未来12个月的指标值 */
    array g1_value[12] _temporary_;
    array g0_value[12] _temporary_;

    call missing(of g1_value[*], of g0_value[*]);

    /* 遍历当前月到未来11个月的日期，查找对应指标值 */
    do i = 0 to 11;
        target_date = intnx('month', date, i, 'same');
        rc = h.find(key: main_acct_number, key: target_date);
        if rc = 0 then do;
            g1_value[i+1] = defaultind_g1;
            g0_value[i+1] = defaultind_g0;
        end;
    end;

    /* 计算最大值（自动忽略缺失值） */
    Worst_g1_12m = max(of g1_value[*]);
    Worst_g0_12m = max(of g0_value[*]);

    drop rc i target_date;
run;

方法2：使用PROC SQL窗口函数（简洁高效） #

利用窗口函数的范围定义，直接取当前行及后续11行的最大值（假设每个账户每月仅一条记录）：

PROC SQL;
    CREATE TABLE Default_Ind_02 AS
    SELECT 
        main_acct_number,
        date,
        defaultind_g1,
        defaultind_g0,
        /* 取当前月+未来11个月共12个月的defaultind_g1最大值 */
        MAX(defaultind_g1) OVER (PARTITION BY main_acct_number 
                                ORDER BY date 
                                ROWS BETWEEN CURRENT ROW AND 11 FOLLOWING) AS Worst_g1_12m,
        MAX(defaultind_g0) OVER (PARTITION BY main_acct_number 
                                ORDER BY date 
                                ROWS BETWEEN CURRENT ROW AND 11 FOLLOWING) AS Worst_g0_12m
    FROM Default_Ind_01
    ORDER BY main_acct_number, date;
QUIT;

关键注意事项 #

• 若原数据中date为字符型，需先转换为SAS日期格式：date = input(date, date9.);
• 窗口函数方法要求数据已按main_acct_number和date排序，若存在缺失月份，需先补全月份再计算。

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