Find Event in Time Period

[JulianGER]

Hallo all together,
I have a short question. It would be great if someone could help me.
My data looks a follows. I have some events happening on a specific date in Time for several IDs. Now I want to find out if EVENT2 happens in an specific time frame of EVENT1 e.g. Minus 5 days, plus 5 Days. IF Yes I want to have a Dummy with 1 in the Line of OBS of EVENT1, otherwise 0.

E.g.

ID	DATE	EVENT1	EVENT2
1	15.10.2003	YES	
1	16.10.2003		YES
1	14.02.2004	YES	
1	17.02.2004		YES
1	16.05.2005	YES	
1	16.05.2005		YES
2	16.10.2003		YES
2	17.10.2003	YES	
2	15.02.2004		YES
2	18.02.2004	YES	
2	17.05.2005		YES
2	17.05.2005	YES

Desired Output

ID	DATE	EVENT1	EVENT2 DUM
1	15.10.2003	YES		1
1	16.10.2003		YES	
1	14.02.2004	YES		0
1	17.02.2004		YES	
1	16.05.2005	YES		1
1	16.05.2005		YES	
2	16.10.2003		YES	
2	17.10.2003	YES		1
2	15.02.2004		YES	
2	18.02.2004	YES		0
2	17.05.2005		YES	
2	17.05.2005	YES		1

Maybe someone of you guys can help me. Would be great.

Thanks in advance

 

[JulianGER]

One possible Solution for me would be, that I Isolate EVENT2 and generate additional dates for it via a loop function. Then I Will merge it back with EVENT1 data. However I couldn't manage too.

Step 1

ID	DATE	EVENT2
1	16.10.2003	YES
1	17.02.2004	YES
1	16.05.2005	YES
2	16.10.2003	YES
2	15.02.2004	YES
2	17.05.2005	YES

Step 2

ID	DATE	EVENT2
1	15.10.2003	YES
1	16.10.2003	YES
1	17.10.2003	YES
1	16.02.2004	YES
1	17.02.2004	YES
1	18.02.2004	YES
1	15.05.2005	YES
1	16.05.2005	YES
1	17.05.2005	YES
2	15.10.2003	YES
2	16.10.2003	YES
2	17.10.2003	YES
2	14.02.2004	YES
2	15.02.2004	YES
2	16.02.2004	YES
2	16.05.2005	YES
2	17.05.2005	YES
2	18.05.2005	YES

Step 3

ID	DATE	EVENT2	EVENT1
1	15.10.2003	YES	YES
1	16.10.2003	YES	
1	17.10.2003	YES	
1	14.02.2003		YES
1	16.02.2004	YES	
1	17.02.2004	YES	
1	18.02.2004	YES	
1	15.05.2005	YES	
1	16.05.2005	YES	YES
1	17.05.2005	YES	
2	15.10.2003	YES	
2	16.10.2003	YES	
2	17.10.2003	YES	YES
2	14.02.2004	YES	
2	15.02.2004	YES	
2	16.02.2004	YES	
2	18.02.2004		YES
2	16.05.2005	YES	
2	17.05.2005	YES	YES
2	18.05.2005	YES

Step 4

ID	DATE	EVENT2	EVENT1	DUM
1	15.10.2003	YES	YES	1
1	16.10.2003	YES		
1	17.10.2003	YES		
1	14.02.2003		YES	0
1	16.02.2004	YES		
1	17.02.2004	YES		
1	18.02.2004	YES		
1	15.05.2005	YES		
1	16.05.2005	YES	YES	1
1	17.05.2005	YES		
2	15.10.2003	YES		
2	16.10.2003	YES		
2	17.10.2003	YES	YES	1
2	14.02.2004	YES		
2	15.02.2004	YES		
2	16.02.2004	YES		
2	18.02.2004		YES	0
2	16.05.2005	YES		
2	17.05.2005	YES	YES	1
2	18.05.2005	YES

Step 5

ID	DATE	EVENT2	EVENT1	DUM
1	15.10.2003	YES	YES	1
1	14.02.2003		YES	0
1	16.05.2005	YES	YES	1
2	17.10.2003	YES	YES	1
2	18.02.2004		YES	0
2	17.05.2005	YES	YES	1

 

[JulianGER]

Sorry. I my Example the time frame is Minus 1 plus 1 days

 

[ChrisW75]

Easiest way to do this is to join the Event 2 records to the Event 1 records using proc sql. Soemthing like this

proc sql;
  create table valid as
  select A.ID
        ,A.Date
        ,B.date as event2_date
        ,1  as dummy
  from dset1(where=(event1='YES'))  A
   left join
       dset1(where=(event1='YES'))  B
       on A.ID = B.ID
  where A.date-B.date between -5 and 5
  ;
quit;

Then, dedupe this file by ID and Date, then join it back to your original file again using ID and Date.
That should do the trick.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.