如何将SAS数据集Type列值合并至同描述的Description列

原始SAS代码 #

data have;
infile cards dlm=',';
informat product_Id $8. type $8. Description $20.;
input Product_ID    Type      Description;
cards;
12, A, Made of wood
12, B, Made of wood
25, C, Made of steel
15, D, Made of iron
12, F, Made of paper
;

proc sort data=have;
by product_id description type;
run;

data want;
set have;
length type_cat $20.;
by product_id description type;
retain type_cat;
if first.product_id then call missing(type_cat);
type_cat = catx(", ", type_cat, type);

if last.description then do;
description = catt(description, " (", type_cat, ")");
output;
end;

run;

预期输出 #

Product_ID description
12         Made of paper(F)
12         Made of Wood(A,B)
25         Made of steel (C)
15         Made of iron ( D)

问题根源 #

你当前代码的核心错误是重置type_cat的时机不对：

• 需求是按「同一Product_ID下的相同Description」分组收集Type值，但你是在first.product_id时清空type_cat，这会导致同一个Product_ID下的所有不同Description的Type被累加进同一个type_cat，最终造成不同Description的Type混乱混合。
• 正确的重置时机应该是当进入同一Product_ID下的新Description组时，也就是触发first.description的时候清空type_cat。

修正后的SAS代码 #

data have;
infile cards dlm=',';
informat product_Id $8. type $8. Description $20.;
input Product_ID    Type      Description;
cards;
12, A, Made of wood
12, B, Made of wood
25, C, Made of steel
15, D, Made of iron
12, F, Made of paper
;

proc sort data=have;
by product_id description type;
run;

data want;
set have;
length type_cat $20.;
by product_id description type;
retain type_cat;
/* 修正：在同一Product_ID下的新Description组时清空type_cat */
if first.description then call missing(type_cat);
type_cat = catx(", ", type_cat, type);

if last.description then do;
description = catt(description, " (", type_cat, ")");
output;
end;

run;

修正后输出 #

运行修正后的代码，即可得到符合预期的结果：

Product_ID description
12         Made of paper (F)
12         Made of wood (A, B)
25         Made of steel (C)
15         Made of iron (D)

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