求每条记录出现的次数和时间[升级版]

1202

收藏 2012-08-02

有一个程序问题想请教大家，以下是原始数据：
number             date                金额
600203             2012-01-01       1
600203             2012-01-15       2
600204             2012-01-14       3
600205             2012-01-04       4
600205             2012-01-19    5
600206             2012-01-03    5
600206             2012-01-28    6
600207             2012-01-05    7
600208             2012-01-09    8

要求结果如下：
number             date1          金额1    date2          金额2       date3 金额3          date4    金额4
600203             2012-01-01 1    2012-01-15    2
600204             2012-01-14 3
600205             2012-01-04 4    2012-01-19       5
600206             2012-01-03 5    2012-01-28       6
600207             2012-01-05 7
600208             2012-01-09 8
比上次的帖子多了个金额，用单纯的tranpose只能做到输出date。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

全部回复

maidenhan

2012-8-2 16:46:07

U can use macro and array to realize it.
For more information, check the following code.

data test1;
input number day $10. amount;
cards;
600203 2012-01-01 1
600203 2012-01-15 2
600204 2012-01-14 3
600205 2012-01-04 4
600205 2012-01-19 5
600205 2012-01-19 20
600205 2012-02-01 10
600206 2012-01-03 5
600206 2012-01-28 6
600207 2012-01-05 7
600208 2012-01-09 8
;run;
proc sql;
create table test2 as
select number, day, sum(amount) as amount
  from test1
  group by 1,2
  order by 1;
quit;
proc sql noprint;
select max(n) into:an
  from (select number, count(*) as n from test2 group by 1);
quit;
%macro test();
data test3(drop = day amount fid i);
format number 8.
   %do ii = 1 %to &an.;
day_&ii. $10. amount_&ii. 8.
   %end;;
array d{&an.} $ %do ii = 1 %to &an.; day_&ii. %end;;
array a{&an.} %do ii = 1 %to &an.; amount_&ii. %end;;
retain d;
retain a;
set test2;
by number;
if first.number then do;
  fid = 0;
  do i = 1 to &an.;
d{i} = '';
a{i} = .;
  end;
end;
fid + 1;
d{fid} = day;
a{fid} = amount;
if last.number;
run;
%mend;
%test();

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

ziyenano

2012-8-2 18:06:29

分别转制，然后merge:
proc transpose data=ex out=ex1(drop=_name_)
      prefix=date;
            by  number;
      var date;
proc transpose data=ex out=ex2(drop=_name_)
      prefix=money;
            by  number;
      var money;
data ex3;
      merge ex1 ex2;
            by number;
      run;

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

扫码加我 拉你入群

扫码加我 拉你入群

扫码加我 拉你入群

分享

扫码加好友，拉您进群

扫码加我拉你入群

扫码加我拉你入群

扫码加我拉你入群