基于96个月数据集生成特定数值模式标识变量的SAS问询

现有一包含96个连续月份的数值数据集，需为每个唯一数值ID生成名为flag的变量：当ID对应的月度数据中任意时段出现「3个连续0→9个连续非0→3个连续0」的模式时，flag赋值为1，否则为0。示例模式：0,0,0,100,200,40,50,40,300,500,50,30,0,0,0。

用户已编写部分SAS代码如下：

data want;
set have;
array pays(*) monthly_pay_201501--monthly_pay_202212;
array months(*) month1-month96;

* populate with first day of each month extracted from var name;
do _i = 1 to 96;
    months(_i) = input(scan(vname(pays(_i)), -1, '_'), yymmn6.);
end;

* ensure the dates are properly bounded;
format strt_dt end_dt date9.;
strt_dt = intnx('month', max(startdate, '1jan2015'd), 0);
end_dt = intnx('month', min(enddate, '31dec2022'd), 0);

* find the index of months array that matches the dates;
strt_indx = whichn(strt_dt, of months(*));
end_indx = whichn(end_dt, of months(*));

* validate the indices accurately reflect the true var;
strt_vname = vname(pays(strt_indx));
end_vname = vname(pays(end_indx));


do _i = strt_indx to end_indx;
    #think i need to do some sort if statement here ?
end;

drop _: monthly_pay: month:;
run;

补充后的完整代码 #

data want;
set have;
array pays(*) monthly_pay_201501--monthly_pay_202212;
array months(*) month1-month96;

* populate with first day of each month extracted from var name;
do _i = 1 to 96;
    months(_i) = input(scan(vname(pays(_i)), -1, '_'), yymmn6.);
end;

* ensure the dates are properly bounded;
format strt_dt end_dt date9.;
strt_dt = intnx('month', max(startdate, '1jan2015'd), 0);
end_dt = intnx('month', min(enddate, '31dec2022'd), 0);

* find the index of months array that matches the dates;
strt_indx = whichn(strt_dt, of months(*));
end_indx = whichn(end_dt, of months(*));

* validate the indices accurately reflect the true var;
strt_vname = vname(pays(strt_indx));
end_vname = vname(pays(end_indx));

* 初始化flag为0;
flag = 0;

* 遍历所有可能的模式起始位置，需预留3+9+3=15个连续月份;
do _start = strt_indx to end_indx - 14 while(flag = 0);
    * 检查前3个连续0;
    _check_zeros1 = sum(of pays(_start) pays(_start+1) pays(_start+2)) = 0;
    * 检查中间9个连续非0;
    _check_nonzeros = sum( (pays(_start+3) ne 0), (pays(_start+4) ne 0), 
                          (pays(_start+5) ne 0), (pays(_start+6) ne 0), 
                          (pays(_start+7) ne 0), (pays(_start+8) ne 0), 
                          (pays(_start+9) ne 0), (pays(_start+10) ne 0), 
                          (pays(_start+11) ne 0) ) = 9;
    * 检查最后3个连续0;
    _check_zeros2 = sum(of pays(_start+12) pays(_start+13) pays(_start+14)) = 0;
    
    * 三个条件同时满足则设flag为1并跳出循环;
    if _check_zeros1 and _check_nonzeros and _check_zeros2 then do;
        flag = 1;
        leave;
    end;
end;

drop _: monthly_pay: month:;
run;

逻辑说明 #

• 先将flag初始化为0，一旦找到符合模式的序列就设为1并终止循环，提升效率
• 遍历的起始位置范围限制在strt_indx到end_indx -14，保证有足够的连续月份匹配完整的15个值模式
• 分三步验证模式：
  1. 前3个值的和为0（确保全是0）
  2. 中间9个值每个都不为0，统计非0的数量等于9
  3. 最后3个值的和为0（确保全是0）
• 三个条件同时满足时，将flag设为1并跳出循环，无需继续检查后续位置

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