A tricky data manipulation

jingju11 发表于 2013-1-19 08:27
Thank Pobel again. To fit my code, i did some minor adjustment. To my surprise, there is no optimal  ...

1. this is the code from myself. No significant gain to the previous. it is just for sharing. Jingju
   
2. 
   
3. data rate_all2nonRrr2;
   
4. set rate_all2nonRrr1;
   
5. by clnt_no acct_ref_no;
   
6. retain s0;
   
7. if (first.acct_ref_no) then do;
   
8. s0 =qtr_dt; s =0;
   
9. end;
   
10. s ++1;
    
11. if (s >3) then delete; /*once s adds upto 3, delete any following records till the next id*/
    
12. if (intnx('month', s0, 3*(s-1), 'e') ^=qtr_dt) then do;
    
13. s0 =qtr_dt; s =1;
    
14. end;
    
15. drop s0;
    
16. run;
    
17. data rate_all2nonRrr3;
    
18. do _n_ =1 by 1 until(last.acct_ref_no);
    
19. set rate_all2nonRrr2;
    
20. by clnt_no acct_ref_no;
    
21. i1 =sum(i1,1);
    
22. if (^i2) then if (s =2) then do; /* fetch 1st record @ s =2*/
    
23. i3 =s; i4 =i1; i2 =1;
    
24. end;
    
25. end;
    
26. i5 =i1; /*number of records for that id */
    
27. i6 =s ; /*s @ last record for that id */
    
28. i1 =0 ;
    
29. do _n_ =1 by 1 until(last.acct_ref_no);
    
30. set rate_all2nonRrr2;
    
31. by clnt_no acct_ref_no;
    
32. i1 =sum(i1,1);
    
33. if (i6 =3) then do;/*when s =3 (existing 3-consecutive records) then output the last 3 records*/
    
34. if (i1 >i5 -3) then output;
    
35. end;
    
36. /*if no 3-consecutive records exist then output 1st 2-consecutive records, otherwise no output*/
    
37. else if (i3 =2) then if (i1 >i4-2 & i1 <=i4) then output;
    
38. end;
    
39. drop s i1-i6;
    
40. run;

复制代码

jingju11 发表于 2013-1-19 08:38

在POBEL的程序里，第二步是根据生成的记录序列数来从大的数据集里选择并输出数据。这是个非常简单的sas问题。但是因为数据很大，这个很简单的方法也许值得探索一下。

1. proc sql;
   
2. select a.* from a, b where a.id = b.id
   
3. ;

复制代码

我使用了若干种方法去实现这种很简单选择，以期望得到一个较有效率的方案。但是，有点出乎意料的是，几种方法效率基本相同。也就是说，sas在这个问题上没有很明显的优化。具体的结果参阅我的程序结果和分析。
京剧

1. data have1;
   
2.   merge have have(firstobs=2 keep=id dt rename=(id=id1 dt=dt1)) have(firstobs=3 keep=id dt rename=(id=id2 dt=dt2));
   
3.   if intnx('month',dt,3,'end')=dt1 and id=id1 then grp=2;
   
4.   if intnx('month',dt,3,'end')=dt1 and intnx('month',dt1,3,'end')=dt2 and id=id1=id2 then grp=3;
   
5.   if grp;
   
6.   drop id1 id2;
   
7. run;
   
8. 
   
9. proc sort data=have1 out=have2 nodupkey ;
   
10.   by id descending grp dt;
    
11. run;
    
12. 
    
13. data want;
    
14.   set have2;
    
15.   by id;
    
16.   if first.id;
    
17.   output;
    
18.   dt=dt1;
    
19.   _obs+1;
    
20.   output;
    
21.   if grp=3 then do;
    
22.   dt=dt2;
    
23.   _obs+1;
    
24.   output;
    
25.   end;
    
26.   drop dt1 dt2 grp;
    
27. run;

复制代码

我也写了一个，不过用了一次sort~

jingju11 发表于 2013-1-21 13:31
在POBEL的程序里，第二步是根据生成的记录序列数来从大的数据集里选择并输出数据。这是个非常简单的sas问 ...

There might be another way to do the subsetting: the FORMAT+PUT() way (see codes below).
Maybe sometimes this method is a bit faster.

/*Previous code is omitted*/
              
        *** Get the needed observations;
        fmtname="OBSFMT"; label="OK";
        if last.acct_ref_no then do i =1 by 1 while (scan(_obs_need,i,',') ^=' ');
                start=input(scan(_obs_need,i,','), best.);
                output null_2;
        end;
run;

proc format cntlin=null_2;
run;

data null_3;
           set null_1;
        flag=put(_obs,obsfmt.);
        if flag="OK";
        drop flag;
run;

zhangzachary 发表于 2013-1-21 15:09
我也写了一个，不过用了一次sort~

very good, in particular, for a data not too large. By the way, nodupkey in sort may be redundant.
JingJu

pobel 发表于 2013-1-21 15:13
There might be another way to do the subsetting: the FORMAT+PUT() way (see codes below).
Maybe so ...

Thanks for the advice. I added the proc format as one method in the test.
I had a chance today to rerun the program in our Unix Server  andreported and summarized the results here.
Any comments are welcome.
Jingju