SAS Programming

DATA Step, Macro, Functions and more
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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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