topic Re: Find ids with at least two events that are less than 12 months apart in SAS Programming

Find ids with at least two events that are less than 12 months apart

lillymaginta — Thu, 30 Jun 2016 04:04:08 GMT

I have a dataset with the ID and event date for each id. Each id can have many events up to 40 event dates. I want to find id who have at least 2 events that happened within no more than 12 months from each other. In the case below, the new dataset should keep id 1 and 3 because both have at least 2 even dates that are less than 12 months from each other

id     event_Date
1      1/1/2005
1      2/21/2007
1      2/22/2007
1     12/5/2004
2     1/1/2004
2     3/3/2007
2     7/5/2006
3    11/2/2004
3     1/5/2005

Re: Find ids with at least two events that are less than 12 months apart

Ksharp — Thu, 30 Jun 2016 07:21:32 GMT

But as your logic , 2 should be include too.

2 3/3/2007
2 7/5/2006

only have 8 month between them .

Re: Find ids with at least two events that are less than 12 months apart

Kurt_Bremser — Thu, 30 Jun 2016 07:23:59 GMT

Your rule and your wanted result don't match.

id 2 has less than 12 months between July 5, 2006 and March 3, 2007.

Re: Find ids with at least two events that are less than 12 months apart

Ksharp — Thu, 30 Jun 2016 07:26:36 GMT

OK. Assuming I follow your logic .

data have;
input id     event_Date : mmddyy10.;
format  event_Date  mmddyy10.;
cards;
1      1/1/2005
1      2/21/2007
1      2/22/2007
1     12/5/2004
2     1/1/2004
2     3/3/2006
2     7/5/2007
3    11/2/2004
3     1/5/2005
;
run;
proc sql;
create table want as
select * from have where id in (
 select distinct a.id 
  from have as a,have as b
   where a.id=b.id and a.event_Date ne b.event_Date and 
    abs(intck('month',a.event_Date,b.event_Date,'c')) le 12
);
quit;

Re: Find ids with at least two events that are less than 12 months apart

Kurt_Bremser — Thu, 30 Jun 2016 07:27:58 GMT

If the aforementioned discrepancy was just a typo, this might be a solution:

data have;
input id event_date :mmddyy10.;
format event_date mmddyy10.;
cards;
1 1/1/2005
1 2/21/2007
1 2/22/2007
1 12/5/2004
2 1/1/2004
2 3/3/2007
2 7/5/2005
3 11/2/2004
3 1/5/2005
;
run;

proc sort data=have;
by id event_date;
run;

data want (keep=id);
set have;
by id;
retain flag;
prev_date = lag(event_date);
if first.id then flag = 0;
else do;
  if intck('month',prev_date,event_date,'cont') <= 12 then flag = 1;
end;
if last.id and flag then output;
run;

Re: Find ids with at least two events that are less than 12 months apart

lillymaginta — Thu, 30 Jun 2016 16:04:25 GMT

Thank you both worked!

Re: Find ids with at least two events that are less than 12 months apart

Ksharp — Fri, 01 Jul 2016 00:50:05 GMT

For the sake of safety and speed, I suggest you to use @Kurt_Bremser 's code ,

if there are two same event_date for the same ID , My code wouldn't be right.