如何在SAS中统计各账户对应列属性的变更频次？代码报错求助

数据集与需求说明 #

现有一个100列的SAS数据集，唯一键为As_of_Date和Account_Number，除前3列（As_of_Date、Account、Account_Number）外，其余列的内容均可变更。需要统计每个Account_Number分组下，各列的内容变更次数。

简化版数据集如下：

单列统计的可行代码 #

已实现单列变更统计的代码，可正确输出结果：

proc sort data = database; by Account_Number Account As_of_Date; run;

data want;
set database;
format lag_value mmddyy10.;
by Account_Number;
lag_value = lag(Anticipated_close_date);
if first.Account_Number then value_change= 0;
else value_change = (Anticipated_close_date^=lag_value);
run;

批量处理的问题代码与报错 #

尝试批量处理所有列时编写的代码：

proc contents data = database out=contents noprint; run;

proc sql;
select cats('_',varnum), cats(quote(trim(name),"'"),'N')
into
  :temp_column_vars separated by ' '
  :column_vars separated by ' '
from contents
where (name like '%Date%' and name ne 'As_of_Date');
quit;

proc sort data = database; by Account_Number Account As_of_Date; run;

data final;
set database;
by Account_Number;
array column &column_vars;
array temp_column &temp_column_vars;
do over column;
    lag_value_&temp_column. = lag(&temp_column);
    if first.Account_Number then value_change_&temp_column.= 0;
    else value_change_&temp_column. = (&temp_column^=lag_value_&temp_column.);
end;
run;    

遇到两个报错：

1. Statement is not valid or it is used out of proper order
2. 加入Request_Type、Stage等字符型列时，提示All variables in array list must be the same type, i.e. all numeric or character.

问题解决与修正代码 #

核心问题分析 #

• SAS数组要求所有元素为同一类型（全数值或全字符），混合类型会触发第二个报错
• 原代码中do over循环结合宏变量的用法错误，导致语法报错

修正后的完整代码 #

/* 1. 获取数据集变量信息，分离数值型和字符型变量（排除前3列） */
proc contents data = database out=contents noprint; run;

proc sql noprint;
/* 数值型变量列表 */
select name into :num_vars separated by ' '
from contents
where varnum > 3 and type = 1; /* type=1表示数值型 */

/* 字符型变量列表 */
select name into :char_vars separated by ' '
from contents
where varnum > 3 and type = 2; /* type=2表示字符型 */
quit;

/* 2. 按分组键排序 */
proc sort data = database; by Account_Number As_of_Date; run;

/* 3. 批量计算各列变更标记 */
data final;
set database;
by Account_Number;

/* 处理数值型变量 */
array num_cols[*] &num_vars;
array num_change[*] value_change_&num_vars; /* 生成变更标记变量 */
do i = 1 to dim(num_cols);
    lag_val = lag(num_cols[i]);
    if first.Account_Number then do;
        num_change[i] = 0;
        call missing(lag_val); /* 重置lag值，避免跨组污染 */
    end;
    else do;
        /* 处理缺失值：一个缺失一个非缺失时也算变更 */
        num_change[i] = (num_cols[i] ne lag_val) or (missing(num_cols[i]) ne missing(lag_val));
    end;
end;

/* 处理字符型变量 */
array char_cols[*] &char_vars;
array char_change[*] value_change_&char_vars;
do i = 1 to dim(char_cols);
    lag_val_char = lag(char_cols[i]);
    if first.Account_Number then do;
        char_change[i] = 0;
        call missing(lag_val_char); /* 重置lag值 */
    end;
    else do;
        char_change[i] = (char_cols[i] ne lag_val_char) or (missing(char_cols[i]) ne missing(lag_val_char));
    end;
end;

drop i lag_val lag_val_char;
run;

/* 4. 统计每个Account_Number的各列变更总次数 */
proc sql;
create table change_summary as
select Account_Number,
       sum(value_change_Anticipated_Close_Date) as Anticipated_Close_Date_changes,
       sum(value_change_Need_by_Date) as Need_by_Date_changes,
       sum(value_change_Date_Accepted) as Date_Accepted_changes,
       sum(value_change_Date_Confirmed) as Date_Confirmed_changes,
       sum(value_change_Request_Type) as Request_Type_changes,
       sum(value_change_Stage) as Stage_changes,
       sum(value_change_Amount) as Amount_changes
from final
group by Account_Number;
quit;

修正说明 #

• 分离数值型和字符型数组，解决类型不兼容问题
• 使用数组下标循环替代do over，避免宏变量引用错误
• 处理缺失值场景：当当前值与前值一个缺失一个非缺失时，判定为变更
• 在分组起始行重置lag值，避免不同Account_Number之间的lag值污染
• 新增统计步骤，直接输出每个Account_Number下各列的总变更次数

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