怎么利用hash或sql将不同观测按要求重新组合？

数据集中有几个变量，subjn(病人) dt(服药日期) 服药种类(rx), 有的病人同一天服了多种药，即同样的dt有多个不同的rx，这个时候要将数据按subjn和dt重新分组，具体说明如下

Sample data(样本数据):
--------------------
data test;
input subjn dt Rx $ x;
cards;
1 1 A 11
1 2 B 12
1 3 C 13
2 3 A 21
2 7 C 22
2 7 B 23
2 12 D 24
3 1 B 31
3 1 C 32
3 5 A 33
3 8 B 34
3 11 B 35
3 11 D 36
3 11 E 37
;;;;



new output date should look like（结果需要象下面一样）:
caseID
1 1 A 11 1
1 2 B 12 1
1 3 C 13 1

2 3 A 21 1
2 7 C 22 1
2 12 D 24 1

2 3 A 21 2
2 7 B 23 2
2 12 D 24 2

3 1 B 31 1
3 5 A 33 1
3 8 B 34 1
3 11 B 35 1
<and 5 more thread for this patient>

对于2号病人，由于在第7天服用了2种药，就将数据分成了两组，第3个病人，第1天服用2种，第11天服用3种，因此要分成2*3=6组数据，每组的dt均是1,5,8,11。

坐等高手

程序已修改，为测验程序，增加了数据复杂度。
经测试，下面程序可以使用。

data test;
input subjn dt Rx $ x;
cards;
1 1 A 11
1 2 B 12
1 3 C 13
2 3 A 21
2 7 C 22
2 7 B 23
2 12 D 24
2 12 B 25
3 1 B 31
3 1 C 32
3 5 A 33
3 5 C 58
3 8 B 34
3 11 B 35
3 11 D 36
3 11 E 37
4 1 A 41
4 2 B 42
4 2 D 43
4 3 C 44
;run;

%macro test();
proc sql;
  drop table t1;
drop table t7;
quit;
proc sql;
  create table t1 as
select distinct subjn
  from test;
quit;
data _null_;
  set t1 nobs=n;
call symput('n', n);
stop;
run;
%do ii = 1 %to &n.;
data _null_;
  set t1(firstobs=&ii. obs=&ii.);
  call symput('subjn', subjn);
  stop;
run;
data t2;
  set test;
  where subjn = &subjn.;
run;
proc sort data = t2;
  by dt rx;
run;
proc sql;
  create table t2_only as
  select *
   from t2
   where dt in (select distinct dt from ( select dt, count(*) as cnt from t2 group by dt) where cnt = 1 );
quit;
proc sql;
  create table t2_else as
  select *
   from t2
   where dt not in (select dt from t2_only)
   order by dt;
quit;
data t2_else;
  set t2_else end=eof;
  by dt;
  if _n_ = 1 then fake_id1 = 0;
  if first.dt then fake_id1 + 1;
  if eof then call symput('sql_join_times', fake_id1-1);
run;
proc sql noprint;
  select count(*) into:flg1 from t2_else;
quit;
%if &flg1. ne 0 %then %do;
  proc sql noprint;
   select count(distinct fake_id1) into:flg2 from t2_else;
  quit;
  %if &flg2. = 1 %then %do;
   data t3_1(keep=subjn dt_1 rx_1);
    set t2_else(rename=(dt=dt_1 rx=rx_1));
    output t3_1;
   run;
  %end;
  %do jj = 1 %to &sql_join_times.;
   %let name_j1 = %eval(&jj.+0);
   %let name_j2 = %eval(&jj.+1);
   %if &jj. = 1 %then %do;
    data t3_1 t3_2(drop = fake_id1);
     set t2_else;
     where fake_id1 in (1,2);
     if fake_id1 = 1 then output t3_1;
     else output t3_2;
    run;
    proc sql;
     create table t4 as
     select a.subjn, a.dt as dt_1, a.rx as rx_1, b.dt as dt_2, b.rx as rx_2
      from t3_1 as a
      full join t3_2 as b
      on a.subjn = b.subjn and a.dt < b.dt;
    quit;
   %end;%else %do;
    proc sql;
     create table t4 as
     select a.*, b.dt as dt_&name_j2., b.rx as rx_&name_j2.
      from t3_1 as a
      full join t3_2 as b
      on a.subjn = b.subjn and a.dt_&name_j1. < b.dt;
    quit;
   %end;
   data t3_1;
    set t4;
   run;
   %if &jj. ne &sql_join_times. %then %do;
    data t3_2;
     set t2_else;
     where fake_id1 = &jj. + 2;
    run;
   %end;
  %end;
  data t5_else(keep = subjn dt rx grp);
   set t3_1 end=eof;
   grp + 1;
   %do jj = 1 %to %eval(&sql_join_times.+1);
    dt = dt_&jj.;
    rx = rx_&jj.;
    output;
   %end;
   if eof then call symput('grp_no', grp);
  run;
  data t5_only(keep = subjn dt rx grp);
   set t2_only;
   do grp = 1 to &grp_no.;
    output;
   end;
  run;
  data t6;
   set t5_only
    t5_else;
  run;
%end;%else %do;
  data t6;
   set t2_only(keep = subjn dt rx);
   grp = 1;
  run;
%end;
proc sort data = t6;
  by subjn grp dt rx;
run;
proc append base = t7 data = t6; run;
proc sql;
  drop table t2;
  drop table t2_only;
  drop table t2_else;
  drop table t3_1;
  drop table t3_2;
  drop table t4;
  drop table t5_only;
  drop table t5_else;
  drop table t6;
quit;
%end;
%mend;
%test();

不只求答案，更求各种方法，望各位高手赐教

maidenhan 发表于 2012-11-5 18:44
data test;
input subjn dt Rx $ x;
cards;

佩服对hash和sort强大的应用! 要好好学习一下。

Imasasor 发表于 2012-11-5 19:10
佩服对hash和sort强大的应用! 要好好学习一下。

对不起楼主，我的思路有问题，2*3的时候ok，2*2的时候结论是错的，容我晚上在好好想想~~

mark，等明白再回来看看

1. proc sort data = test; by subjn dt rx; run;
   
2. proc sql;
   
3.         create table test_step1 as
   
4.         select subjn, dt, count(*) as cnt from test
   
5.         group by 1, 2;
   
6. quit;
   
7. data test_step2(index = (subjn));
   
8.         set test_step1; by subjn dt;
   
9.         retain tot_rcd;
   
10.         if first.subjn then tot_rcd = 1;
    
11.         tot_rcd = tot_rcd * cnt;
    
12.         if last.subjn;
    
13.         keep subjn tot_rcd;
    
14. run;
    
15. data test_step3;
    
16.         merge test test_step1;
    
17.         by subjn dt;
    
18. 
    
19.         retain order pre_grp;
    
20.         if first.subjn then  pre_grp = 1;
    
21.         if first.dt then order = 0;
    
22.         order + 1;
    
23. 
    
24.         set test_step2 key = subjn/unique;
    
25.         _error_ = 0; if _iorc_ = 0;
    
26. 
    
27.         rcd = tot_rcd / cnt;
    
28.         output;
    
29.         if last.dt then pre_grp = pre_grp * cnt;
    
30. run;
    
31. 
    
32. data test_step4;
    
33.         set test_step3;
    
34.         do i = 1 to rcd;
    
35.                 group = ((ceil(i/pre_grp) - 1) * cnt + order - 1) * pre_grp + mod(i, pre_grp);
    
36.                 output;
    
37.         end;
    
38.         keep subjn dt Rx x group;
    
39. run;
    
40. proc sort data = test_step4; by subjn group dt ; run;

复制代码

感觉group的逻辑很难。

真心学习了。

支持下

I think the key is to figure out how many duplicates one needs to make for each and every observation in a given group. The first couple of steps are to calculate number of duplicates. The rest is to merge it back and to have a desired data set.

data test;
input subjn dt Rx $ x;
cards;
1 1 A 11
1 2 B 12
1 3 C 13
2 3 A 21
2 7 C 22
2 7 B 23
2 12 D 24
3 1 B 31
3 1 C 32
3 5 A 33
3 8 B 34
3 11 B 35
3 11 D 36
3 11 E 37
;;;;

data cnt_dt(keep=subjn dt cnt) cnt_subjn(keep= totprod subjn);
  retain totprod;
  set test;
  by subjn dt;
  if first.subjn then totprod=1;
  if first.dt then cnt=0;
  cnt+1;
  if last.dt then do;
     totprod=totprod*cnt;
     output cnt_dt;
  end;
  if last.subjn then output cnt_subjn;
  run;

  data all;
     merge cnt_dt cnt_subjn;
         by subjn;
         num_out=totprod/cnt;
         drop totprod cnt;
  run;

data test2;
  merge test all;
  by subjn dt;
  do i=1 to num_out;
     output;
  end;
  drop num_out i;
run;

data test3;
  set test2;
  by subjn dt;
  if first.dt then g=0;
  g+1;

  run;

proc sort data=test3 out=need; by subjn  g dt; run;

proc print;run;