BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lillymaginta
Lapis Lazuli | Level 10

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

6 REPLIES 6
Ksharp
Super User

But as your logic , 2 should be include too. 

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

 

only have 8 month between them .

 

Ksharp
Super User

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;
Kurt_Bremser
Super User

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;
Ksharp
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1023 views
  • 4 likes
  • 3 in conversation