SAS崩溃日期 [英] SAS collapse dates

问题描述

我有一个看起来像这样的数据集：

I have a dataset which looks like this:

cust date 1 2 3... 600
1    1    5 . . ... .
1    2    5 . . ... .
1    2    . 4 . ... .
1    2    . . 6 ... .
2    1    1 . . ... .
2    1    . 5 . ... .
2    2    . . . ... 10

我想按客户（客户）对每个日期的变量1到600进行折叠，因此数据集如下所示：

I want to collapse variables 1 to 600 for each date by customer (cust), so that the dataset looks like this:

cust date 1 2 3... 600
1    1    5 . . ... .
1    2    5 4 6 ... .
2    1    1 5 . ... .
2    2    . . . ... 10

我从以下代码开始（也许有点复杂），但它没有不能正常工作：

I started with the following code (maybe it's a bit complicated), and it doesn't work:

data want ;
set have;
array vars &list.; *stored array of variables 1-600;
retain count vars;
by cust date;
if first.date then do;
do _i=1 to dim(vars);
vars[_i]=.; 
end;
count=0;
end;
count=count+1;
vars[_1]=vars;
if last.date then do;
output;
end;
drop count;
run;

你有什么主意吗？另一个想法是使用proc扩展，但是因为日期重复，所以也不起作用。

Do you have any idea? Another idea was to use proc expand, but it doesn't work either because the dates are duplicates.

非常感谢您的帮助！

推荐答案

有一个巧妙的技巧可以使用UPDATE语句来实现。对现有表的第一个引用（obs = 0）创建具有所需结构的空表，第二个引用使用值进行更新。 BY语句确保每个BY值仅输出一条记录。希望这有道理。

There's a neat trick to achieve this using the UPDATE statement. The first reference to the existing table (with the obs=0) creates an empty table with the required structure, the second reference updates with the values. The BY statement ensures it only outputs one record per BY value. Hope this makes sense.

data have;
input cust date v1 v2 v3 v600;
datalines;
1    1    5 . . .
1    2    5 . . .
1    2    . 4 . .
1    2    . . 6 .
2    1    1 . . .
2    1    . 5 . .
2    2    . . . 10
;
run;

data want;
update have (obs=0) have;
by cust date;
run;

这篇关于SAS崩溃日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！