Find the closest event date prior to another date; sets linked by same ID

janet0102 — Mon, 28 Mar 2016 17:39:22 GMT

I have a dataset with the ID and key date for each individual. I have another dataset with the ID and multiple event dates (each is an observation) for each individual. I need to find the event date that is closest to (before) or the same as the key date for each individual. Individuals may have as few as two event dates or as many as 50 event dates.

Below are ID and key date found in one data set:

ID	Key_Dt
15269	01/16/10
18658	07/31/08
23227	06/18/10
24189	10/27/08
24341	03/31/09

Here are IDs and event dates, currently residing in a second data set:

ID	Evnt_Dt
15269	10/15/10
15269	10/10/10
15269	09/02/10
15269	10/12/10
15269	04/28/10
15269	08/07/10
15269	05/13/10
15269	05/31/10
18658	06/13/08
18658	06/14/08
18658	06/19/08
18658	07/05/08
18658	07/14/08
18658	08/05/08
18658	03/02/09
23227	01/28/10
23227	03/30/10
23227	04/17/10
23227	04/18/10
23227	05/10/10
23227	06/02/10
23227	06/07/10
23227	06/11/10
23227	06/26/10
23227	07/01/10
23227	07/13/10
23227	08/09/10
24189	06/04/08
24189	07/23/08
24189	08/08/08
24189	08/15/08
24189	08/19/08
24189	09/01/08
24189	12/14/08
24189	01/28/09
24341	10/25/08
24341	11/11/08
24341	01/16/09
24341	03/09/09
24671	10/14/08
24671	11/30/08
24671	12/15/08
24671	02/10/09
24671	04/02/09
24671	05/18/09
24671	06/25/09

I'm using SAS 9.3. How do I do that? Thank you!

Re: Find the closest event date prior to another date; sets linked by same ID

PGStats — Mon, 28 Mar 2016 18:07:48 GMT

A simple SQL query:

data have1;
input ID	Key_d :mmddyy8.;
format key_d yymmdd10.;
datalines;
15269	01/16/10
18658	07/31/08
23227	06/18/10
24189	10/27/08
24341	03/31/09
;
data have2;
input ID	Evnt_d :mmddyy.;
format Evnt_d yymmdd10.;
datalines;
15269	10/15/10
15269	10/10/10
15269	09/02/10
15269	10/12/10
15269	04/28/10
15269	08/07/10
15269	05/13/10
15269	05/31/10
18658	06/13/08
18658	06/14/08
18658	06/19/08
18658	07/05/08
18658	07/14/08
18658	08/05/08
18658	03/02/09
23227	01/28/10
23227	03/30/10
23227	04/17/10
23227	04/18/10
23227	05/10/10
23227	06/02/10
23227	06/07/10
23227	06/11/10
23227	06/26/10
23227	07/01/10
23227	07/13/10
23227	08/09/10
24189	06/04/08
24189	07/23/08
24189	08/08/08
24189	08/15/08
24189	08/19/08
24189	09/01/08
24189	12/14/08
24189	01/28/09
24341	10/25/08
24341	11/11/08
24341	01/16/09
24341	03/09/09
24671	10/14/08
24671	11/30/08
24671	12/15/08
24671	02/10/09
24671	04/02/09
24671	05/18/09
24671	06/25/09
;

proc sql;
create table want as
select a.id, max(evnt_d) as date format=yymmdd10.
from have1 as a left join have2 as b on a.id=b.id and a.key_d >= b.evnt_d
group by a.id;
select * from want;
quit;

topic Re: Find the closest event date prior to another date; sets linked by same ID in SAS Procedures

Find the closest event date prior to another date; sets linked by same ID

Re: Find the closest event date prior to another date; sets linked by same ID