Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

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!


Accepted Solutions
Solution
‎03-28-2016 03:23 PM
Respected Advisor
Posts: 4,641

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

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;
PG

View solution in original post


All Replies
Solution
‎03-28-2016 03:23 PM
Respected Advisor
Posts: 4,641

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

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;
PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 308 views
  • 1 like
  • 2 in conversation