A tricky data manipulation

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

数据抽象如下:
data have;
_obs ++1;
input id dt :date9.;
format dt date9.;
cards;
0 31oct2011
1 31jan2010
1 30apr2010
1 31oct2010
1 31jan2011
1 30apr2011
1 31jul2011
1 31jan2012
2 31jan2010
2 31jan2011
2 30apr2011
2 31oct2011
3 31oct2011
;
问题是:
数据按照季度更新(每三个月的最后一天).数据已经按照ID和DT顺序排列.注意:每三个月的更新叫做连续更新.
要求是:
1.如果某个ID里,有至少一个三个或以上连续更新的数据(比如ID=1,从31OCT2010到31JUL2011的连续4次更新),那取出第一个连续更新的前三个记录(只需要这连续排列的前三个记录)--如ID=1所示;
2.如果三个或以上的连续记录不存在,取第一个有连续两个更新的记录--如ID=2所示.
3.否则,去掉该ID--如ID=0和3所示.
问题的来源是:我们只需要这样的数据来做模型.
另外请不要经常SORT数据.因为我们的实际数据大概是一百万的ID,每个ID的可能更新次数从1到1000.数据包含大概50个变量.

京剧

结果 results:

Obs _obs id dt
1   4    1 31OCT2010
2   5    1 31JAN2011
3   6    1 30APR2011
4  10    2 31JAN2011
5  11    2 30APR2011

扫码加我 拉你入群

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

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

分享0 收藏1 回帖

关键词：manipulation ulation tricky Trick ATION

Try this:

1. data _null_;
   
2.     set have end=last;
   
3.         by id dt;
   
4.         _qtr=qtr(dt)+year(dt)*4;
   
5.         length _obs_need $20 _haha $200;
   
6.         retain _maxcon _obs_need _haha;
   
7. 
   
8.         *** Order consecutive records;
   
9.         _lagqtr=lag(_qtr);
   
10.         if first.id then do;
    
11.                                call missing(_obs_need,_maxcon,_lagqtr);
    
12.                   _con=0;
    
13.                 end;
    
14.         if _qtr-_lagqtr=1 then _con+1;
    
15.         else _con=1;
    
16. 
    
17.         *** Get the _obs for the first 3(or 2) consecutive records.;
    
18.         if _con=2 then do;
    
19.                 if missing(_maxcon) then _obs_need=catx(",",_obs-1,_obs);
    
20.             if missing(_maxcon) then _maxcon=2;
    
21.                 _currid=id;
    
22.         end;                     
    
23.         if _con=3 then do;
    
24.                 if _maxcon ne 3 then _obs_need=catx(",",_obs-2,_obs-1,_obs);
    
25.                 _maxcon=3;
    
26.         end;
    
27. 
    
28.         *** Get the needed observations;
    
29.         if last.id and ^missing(_obs_need) then _haha=catx(",",_haha,_obs_need);
    
30.         if last then call symputx("_haha",_haha);
    
31. run;
    
32. 
    
33. data wanted;
    
34.     set have;
    
35.         if _n_ in (&_haha);
    
36. run;
    
37.        

复制代码

pobel 发表于 2013-1-17 09:44
Try this:

very smart code.
let‘s say, records from 10，000 to 19，999, you need a length of mv about 10000*5+10000-1 =59999. in fact,i have about 4,800,000 records, not considering more upcoming records with the next update.
京剧

pobel 发表于 2013-1-17 09:44
Try this:

大概看明白了
能不能先求两个Lead
然后每个观测在三个日期变量上做判断
导出符合条件的观测就好了，即时间间隔各差3个月
这里唯一体现复杂度的就是求两个Lead吧
在SAS里没有Lead函数，只能有proc expand，速度好像不快

playmore 发表于 2013-1-17 10:22
大概看明白了
能不能先求两个Lead
然后每个观测在三个日期变量上做判断

what do you mean "lead"?

jingju11 发表于 2013-1-17 10:22
very smart code.
let‘s say, records from 10，000 to 19，999, you need a length of mv about 10000 ...

If the macro-variable way is not doable, you can generate a dataset with one variable "_obs" which contains the number of observation you need. Then merge it back to the original dataset by _obs.

That is, at the last.id, output the needed obs to the variable _obs. (It's easy for you to do that.)

Hope you know what I mean.

ic.
...
if last.id then do i =1 by 1 while(scan(_haha, i,',') ne '');
_obs = input(scan(_haha,i,','), best.); output;
end;
....
Sounds good. Thanks, JingJu

jingju11 发表于 2013-1-17 10:29
what do you mean "lead"?

与lag相对的
SAS只有lag函数
而没有lead函数

pobel 发表于 2013-1-17 10:35
If the macro-variable way is not doable, you can generate a dataset with one variable "_obs" which ...

嗯，很好的办法

我现在写宏，一般有两种参数，ByFactors和OrderFactors，分别用于by子句和order子句
若初始设置为空的话，则会临时生成这两个变量
这在一些需要变量顺序保持一致的过程，如surveyselect中比较重要

Thank Pobel again. To fit my code, i did some minor adjustment. To my surprise, there is no optimal way to output selected observation numbers from a large data sets. it costs me about 17 minutes to finish a 500M data set.
jingju

data null_1(drop =_:)
null_2(keep =_x rename =(_x =n_obs));
set rate_all2nonRrr1;
by clnt_no acct_ref_no;
n_obs ++1;
_qtr=qtr(qtr_dt)+year(qtr_dt)*4;
length _obs_need $32;
retain _maxcon _obs_need;
*** Order consecutive records;
_lagqtr=lag(_qtr);
if first.acct_ref_no then call missing(_obs_need,_maxcon,_lagqtr,_con);
if _qtr-_lagqtr =1 then _con +1;
else _con =1;
*** Get the n_obs for the first 3(or 2) consecutive records.;
if _con =2 then do;
if missing(_maxcon) then _obs_need=catx(",",n_obs-1,n_obs);
if missing(_maxcon) then _maxcon=2;
end;
if _con=3 then do;
if _maxcon ne 3 then _obs_need=catx(",",n_obs-2,n_obs-1,n_obs);
_maxcon=3;
end;
output null_1;
*** Get the needed observations;
if last.acct_ref_no then do i =1 by 1 while (scan(_obs_need,i,',') ^=' ');
_x =input(scan(_obs_need,i,','), best.);
output null_2;
end;
run;
proc sql;
create table null_3 as
select a.*
from
null_1 as a
inner join
null_2 as b
on a.n_obs =b.n_obs;
quit;