如何在SAS中基于时间窗口计算客户跨交易类型标识字段

实现思路 #

• 第一步：将字符型的交易时间字段转换为SAS可计算的datetime数值型，按客户ID、交易时间升序排序
• 第二步：通过自关联匹配每个客户每笔交易对应的四个时间窗口内的所有交易，判断窗口内是否同时存在两类交易
• 第三步：按客户ID聚合，只要存在任意一笔交易满足对应窗口的判断条件，最终客户维度结果标识为Yes，否则为No

完整SAS实现代码 #

/* 1. 预处理数据：转换时间格式、排序 */
data trantime_pre;
    set trantime_calc;
    /* 字符转SAS datetime格式，精确到百分之一秒 */
    tran_dt = input(TRANTIMESTAMP, datetime21.2);
    format tran_dt datetime21.2;
run;

proc sort data=trantime_pre;
    by Customer_ID tran_dt;
run;

/* 2. 自关联计算每笔交易对应窗口的双交易类型标识 */
proc sql;
    create table tran_flag as
    select 
        a.Customer_ID,
        a.tran_dt as current_tran_dt,
        /* 12小时窗口：datetime单位是秒，12小时=12*3600秒 */
        max(case when b.Type_Of_Tran='Domestic' and a.tran_dt - b.tran_dt <= 43200 then 1 else 0 end) as dom_flag_12h,
        max(case when b.Type_Of_Tran='International' and a.tran_dt - b.tran_dt <= 43200 then 1 else 0 end) as int_flag_12h,
        /* 1天窗口=24*3600秒 */
        max(case when b.Type_Of_Tran='Domestic' and a.tran_dt - b.tran_dt <= 86400 then 1 else 0 end) as dom_flag_1d,
        max(case when b.Type_Of_Tran='International' and a.tran_dt - b.tran_dt <= 86400 then 1 else 0 end) as int_flag_1d,
        /* 7天窗口=7*86400秒 */
        max(case when b.Type_Of_Tran='Domestic' and a.tran_dt - b.tran_dt <= 604800 then 1 else 0 end) as dom_flag_7d,
        max(case when b.Type_Of_Tran='International' and a.tran_dt - b.tran_dt <= 604800 then 1 else 0 end) as int_flag_7d,
        /* 30天窗口=30*86400秒 */
        max(case when b.Type_Of_Tran='Domestic' and a.tran_dt - b.tran_dt <= 2592000 then 1 else 0 end) as dom_flag_30d,
        max(case when b.Type_Of_Tran='International' and a.tran_dt - b.tran_dt <= 2592000 then 1 else 0 end) as int_flag_30d
    from trantime_pre a
    left join trantime_pre b
    on a.Customer_ID = b.Customer_ID and b.tran_dt <= a.tran_dt and a.tran_dt - b.tran_dt <= 2592000 /* 最大窗口30天，减少匹配计算量 */
    group by a.Customer_ID, a.tran_dt;
quit;

/* 3. 客户维度聚合生成最终结果 */
proc sql;
    create table customer_result as
    select 
        Customer_ID,
        case when max(dom_flag_12h * int_flag_12h) = 1 then 'Yes' else 'No' end as has_both_12h length=3,
        case when max(dom_flag_1d * int_flag_1d) = 1 then 'Yes' else 'No' end as has_both_1d length=3,
        case when max(dom_flag_7d * int_flag_7d) = 1 then 'Yes' else 'No' end as has_both_7d length=3,
        case when max(dom_flag_30d * int_flag_30d) = 1 then 'Yes' else 'No' end as has_both_30d length=3
    from tran_flag
    group by Customer_ID
    order by Customer_ID;
quit;

/* 打印查看最终结果 */
proc print data=customer_result noobs;
    title '客户维度交易类型匹配结果';
run;

输出结果说明 #

代码运行后生成的customer_result数据集就是符合要求的客户维度统计结果，和需求预期逻辑一致：

• 客户111：仅30天窗口内存在两类交易，其余窗口无，输出No/No/No/Yes
• 客户121：仅30天窗口内存在两类交易，其余窗口无，输出No/No/No/Yes
• 客户341：仅存在一类交易，所有窗口均输出No

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