在SAS中按分组对AmountPaid、AmountOwed两列数据进行聚合的问题

如果你的数据集里AmountPaid、AmountOwed两列是带逗号的字符型，需要先转为数值类型才能计算求和；如果本身就是数值类型只是用了COMMA格式显示为带逗号的样式，可以直接跳过预处理步骤。

两种常用实现方法如下：

• 方法1：PROC SQL实现，写法直观灵活

/* 预处理：字符转数值，仅当金额列为字符型时需要执行 */
data temp;
    set 你的原数据集名称;
    num_AmountPaid = input(AmountPaid, comma10.);
    num_AmountOwed = input(AmountOwed, comma10.);
    format num_AmountPaid num_AmountOwed comma10.;
run;

/* 分组聚合计算 */
proc sql;
    create table result as
    select ID, FirstName, LastName,
           sum(num_AmountPaid) as AmountPaid format=comma10.,
           sum(num_AmountOwed) as AmountOwed format=comma10.
    from temp
    group by ID, FirstName, LastName;
quit;

• 方法2：PROC SUMMARY实现，大数据量下计算效率更高

/* 预处理步骤和上面一致，数值型金额列可跳过 */
data temp;
    set 你的原数据集名称;
    num_AmountPaid = input(AmountPaid, comma10.);
    num_AmountOwed = input(AmountOwed, comma10.);
    format num_AmountPaid num_AmountOwed comma10.;
run;

proc summary data=temp nway;
    class ID FirstName LastName;
    var num_AmountPaid num_AmountOwed;
    output out=result(drop=_type_ _freq_)
           sum(num_AmountPaid)=AmountPaid
           sum(num_AmountOwed)=AmountOwed;
run;

最终输出的result数据集就是你需要的聚合结果，数值会保留带逗号的显示格式，和预期输出完全一致。

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