如何在某一变量相同的观测中选出符合条件的观测

有如下数据集

• data a;
• input group indexyear Pair_ID;
• cards;
• case 2011 0000048810
• control 2009 0000048810
• control 2006 0000048810
• control 2007 0000145478
• control 2010 0000145478
• case 2013 0000145478
• case 2010 0000151800
• control 2013 0000151800
• control 2007 0000151800
• control 2010 0000151800
• control 2012 0000151800
• control 2011 0000151800
• run;
  

想在所有pairID相同的几个观测值中，找出 group=control里面的index year最接近case的indexyear的值并输出，不知道该如何做？



比如pair-ID=000048810里面的case的indexyear 是2011， 它有两个control，一个是2009，一个是2006，想选出2009那条观测值并输出
类似的观测值有几十万组，每组里面有一个case和2-6个control

想得到结果如下图


多谢大神们指点留言！

1. data a;
   
2. input group indexyear Pair_ID;
   
3. cards;
   
4. case 2011 0000048810
   
5. control 2009 0000048810
   
6. control 2006 0000048810
   
7. control 2007 0000145478
   
8. control 2010 0000145478
   
9. case 2013 0000145478
   
10. case 2010 0000151800
    
11. control 2013 0000151800
    
12. control 2007 0000151800
    
13. control 2010 0000151800
    
14. control 2012 0000151800
    
15. control 2011 0000151800
    
16. ;
    
17. run;
    
18. 
    
19. proc sql noprint;
    
20.         create table want as
    
21.         select * from
    
22.         (select a.*,abs(b.indexyear-a.indexyear) as gap from a as a left join a(where=(group='case')) as b on a.pair_id eq b.pair_id);
    
23. quit;
    
24. 
    
25. proc sort data=want;
    
26.         by Pair_ID group gap;
    
27. run;
    
28. 
    
29. data want;
    
30.         set want;
    
31.         by  Pair_ID group gap;
    
32.         if first.pair_id then k=1;
    
33.         else k+1;
    
34.         retain k;
    
35.         drop k gap;
    
36.         if k<=2;
    
37. run;

复制代码

/*导入数据*/
data a;
input group $ indexyear Pair_ID;
cards;
case 2011 0000048810
control 2009 0000048810
control 2006 0000048810
control 2007 0000145478
control 2010 0000145478
case 2013 0000145478
case 2010 0000151800
control 2013 0000151800
control 2007 0000151800
control 2010 0000151800
control 2012 0000151800
control 2011 0000151800
;
run;
/*把case 和 control 的数据分开，再横向连接，方便计算最接近的年份，也就是年作差的绝对值。*/
/*                数据分开*/
data a_case  a_control;
set a;
if group="case" then output a_case;
if group="control" then output a_control;
run;
/*                排序*/
proc sort data=a_case out=a_case2(rename=(indexyear=case_year));
by pair_id ;
run;
/*                排序*/
proc sort data=a_control out=a_control2(rename=(indexyear=control_year));
by pair_id descending indexyear ;
run;
/*                横向合并*/
data a_merge;
merge a_case2 a_control2;
by pair_id;
run;
/*                作差*/
data a_merge2;
   set a_merge;
        yeardif=min(abs(control_year-case_year));
        run;
/*                按pair_id yeardif分组排序，*/
proc sort data=a_merge2;
by pair_id yeardif;
run;
/*排序后，取相同pair_id的第yeardif一条，也就是最接近case年份那条*/
data a_end;
        set a_merge2;
        by pair_id yeardif;
        if first.pair_id;
        run;

1. data a;
   
2. input group$ indexyear Pair_ID;
   
3. cards;
   
4. case 2011 0000048810
   
5. control 2009 0000048810
   
6. control 2006 0000048810
   
7. control 2007 0000145478
   
8. control 2010 0000145478
   
9. case 2013 0000145478
   
10. case 2010 0000151800
    
11. control 2013 0000151800
    
12. control 2007 0000151800
    
13. control 2010 0000151800
    
14. control 2012 0000151800
    
15. control 2011 0000151800
    
16. run;
    
17. proc sort data=a;
    
18.         by Pair_ID group indexyear;
    
19. run;
    
20. 
    
21. data b;
    
22.         set a;
    
23.         by Pair_ID group indexyear;
    
24.         if last.group;
    
25. run;

复制代码

1. proc sort data=a;
   
2.     by pair_id group;
   
3. run;
   
4. data a2(keep=indexyear_case indexyear pair_id diff);
   
5.     merge a(rename=(indexyear=indexyear_case group=group1) where=(group1="case")) a(where=(group="control"));
   
6.         by pair_id;
   
7.         diff=abs(indexyear_case-indexyear);
   
8. run;
   
9. proc sort data=a2;
   
10.     by pair_id diff;
    
11. run;
    
12. data a3;
    
13.     set a2;
    
14.         by pair_id diff;
    
15.         if first.pair_id then output;
    
16. run;

复制代码

proc sql;
create table selection as
select control.*,
abs(control.indexyear-case.year) as diff,
min(calculated diff) as min
from (select *
       from A
       where group="control") control
left join (select indexyear as year,
                    Pair_ID as ID
             from A
             where group="case") case
on control.Pair_ID=case.ID
group by Pair_ID;
quit;

proc sql;
create table want as
select group,
       indexyear,
       Pair_ID
from selection
where diff=min;
quit;

data a;
input group $ indexyear Pair_ID;
cards;
case 2011 0000048810
control 2009 0000048810
control 2006 0000048810
control 2007 0000145478
control 2010 0000145478
case 2013 0000145478
case 2010 0000151800
control 2013 0000151800
control 2007 0000151800
control 2010 0000151800
control 2012 0000151800
control 2011 0000151800
;
run;

proc sort data=a; by Pair_ID group indexyear;run;

data b;
        set a;
        by  Pair_ID group indexyear;
        if first.Pair_ID then do;
                benchyear=indexyear;               
        end;
        retain benchyear;
        dif=abs(indexyear-benchyear);               
run;

proc sort data=b(where=(group="control"))    ; by Pair_ID dif;run;
proc sort data=b nodupkey ; by Pair_ID;run;

结果：