怎样寻找数据中连续三天的子数据集

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

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

立即领取

感谢您参与论坛问题回答

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

+2 论坛币

如题，data structure 是按照一个日期一条观测，需要找到连续三天的观测，求大神给个sample macro！

扫码加我 拉你入群

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

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

分享0 收藏0 回帖

关键词：寻找数据 找数据 数据集 Structure Sample structure

1. data in;
   
2.         input date test $;
   
3.         format date ddmmyy10.;
   
4.         informat date ddmmyy10.;
   
5.         cards;
   
6.         01/02/2014 a1
   
7.         01/02/2014 a2
   
8.         02/02/2014 b
   
9.         03/02/2014 c1
   
10.         03/02/2014 c2
    
11.         05/02/2014 d
    
12.         06/02/2014 e
    
13.         07/02/2014 f1
    
14.         07/02/2014 f2
    
15.         08/02/2014 g
    
16.         09/02/2014 h
    
17.         11/02/2014 i
    
18.         12/02/2014 j
    
19.         14/02/2014 k
    
20.         16/02/2014 l
    
21.         18/02/2014 m
    
22.         19/02/2014 n
    
23.         20/02/2014 o
    
24.         ;
    
25.         proc sort;by date;
    
26. run;
    
27. 
    
28. data base_dt;
    
29.         set in;
    
30.         retain pdt ppdt;
    
31.         if date-pdt=1 & pdt-ppdt=1 then output;
    
32.         if ppdt<pdt then ppdt=pdt;
    
33.         if pdt<date then pdt=date;
    
34.         format pdt ppdt ddmmyy10.;
    
35.         keep date;
    
36.         proc sort nodupkey;by date;
    
37. run;
    
38. 
    
39. proc sql noprint;
    
40.         create table out as
    
41.         select distinct a.* from in a
    
42.         inner join base_dt b on b.date-a.date>=0 & b.date-a.date<=2;
    
43. quit;

复制代码

A simple SQL will do it if a data set is small. The first solution is slight better.

Both solutions involve a Cartesian product join which will cause a performance issue if the data set has more than 10 thousands  rows. If this is a case, a more efficient way will be a data step.


data tmp;
/*   do date='1jan1980'd to '31jan2014'd;*/
   do date='1jan2014'd to '31jan2014'd;
      if ranuni(123)>0.3 then output;
        end;
        format date yymmdd10.;
run;


proc sql;
  create table tmp2 as
  select a.date , b.date as date2
  from tmp a join tmp b on (b.date=a.date or b.date=a.date+1 or b.date=a.date+2)
  group by 1
  having count(a.date)=3
  order by 1,2
  ;
  quit;

proc sql;
  create table tmp2 as
  select a.date , b.date as date2
  from tmp a , tmp b
  where 0<=(b.date-a.date)<=2
  group by 1
  having count(a.date)=3
  order by 1,2
  ;
  quit;