Appreciate your time! First disregard the expected result tab shown on my last sample file. After I spoke with my cooworker here's the rules we thought may help you understand. Any id (see the latest file sample ) that has 3 consective years should go to group1. For example, if an Id has their first date on 1/2005 then the group will span to 1/2008 and any date that comes during that period will be part of group1. Another example, if an Id has their first date in 1/2005 and same id has another date 1/2009 that means this id will be splitted 2 groups because there're 4 years between 2005 and 2009. This code worked for our group1 and group2, althougth it may not be the most efficient way. Please let me know your thoughts if there's away to tweek my code below. I data tst; set sample_08; three_yr = date + (365.25*3); format three_yr MMDDYY10.; run; proc sort data = tst; by id date; run; data group1; set tst; by id date; if first.id; run; ****Detail of group1*******************; proc sql; create table group1_detail as select b.*, a.three_yr from group1 as a inner join tst as b on a.id = b.id where a.date <= b.date <= a.three_yr ; quit; proc sort data = group1_detail; by id date; run; ****Detail of group2*******************; proc sql; create table group2_detail as select b.*, a.three_yr from group1 as a inner join tst as b on a.id = b.id where b.date > a.three_yr; quit; proc sort data = group2_detail; by id date; run; data group2; set group2_detail; by id date; if first.id; run;
... View more
