conditional merge of two data sets

[newbe]

Hi,

I am trying to merge two data sets. One of the datasets looks like:

ID testdate1

1 09/30/2015

1 06/15/2016

1 01/05/2017

2 03/20/2017

2 04/22/2017

The number of records for each person varies.

The 2nd data set looks like:

ID testdate2

1 07/06/2016

1 01/02/2017

2 05/19/2017

2 10/20/2017

I can only match them using ID. What I want is:

ID  testdate1      testdate2    Indicator

1 06/15/2016   07/06/2016     1

1 01/05/2017  01/02/2017      1

If the interval between testdate1 and testdate2 is greater than 6 months, an indicator variable will be coded 1, otherwise the indicator variable is coded 0.

When I used the ID to merge the two datasets, it turns out to be

ID  testdate1      testdate2   Indicator

1  09/30/2015 07/06/2016  0

1 06/15/2016   01/02/2017 0

1 01/05/2017  01/02/2017  1

In the merged dataset above, the first person is mismatched. I want testdate1=06/15/2016 to be matched with 07/06/2016 and testdate1=09/30/2015 should not be matched with any records in data set 2.

Thanks!

[novinosrin]

Show us your code and log. Let's see if we can modify it. If nothing works out, we shall work on a new solution

[newbe]

My code is simple, but I don't think it is correct.

proc sort data=one;

by ID descending testdate1;

run;

proc sort data=two;

by ID descending testdate2;

run;

data three;

merge one two;

by ID;

run;

I created another variable in data set 1 and 2 by counting the number of observations for each person and then merge the two datasets using ID and the count. I am not sure whether this is correct.

data one1;

set one;

by ID descending testdate1;

if first.ID then count=0;

count+1;

run;

data two1;

set two;

by ID descending testdate2;

if first.ID then count=0;

count+1;

run;

data all;

merge one1 two1;

by ID count;

run;

[novinosrin]

seems like sql is better for this this:

data have1;
input iD testdate1 :mmddyy10.;
format testdate1 mmddyy10.;
datalines;
1 09/30/2015
1 06/15/2016
1 01/05/2017
2 03/20/2017
2 04/22/2017
;

data have2;
input iD testdate1 :mmddyy10.;
format testdate1 mmddyy10.;
datalines;
1 07/06/2016
1 01/02/2017
2 05/19/2017
2 10/20/2017
;

Try and let me know
proc sql;
create table want as
select *,intck('month',testdate1,testdate2)>6 as indicator
from have1 a, have2(rename=(testdate1=testdate2)) b
where a.id=b.id;

quit;

[newbe]

My description of the problem is not clear. Let me try again. What I really want is an indicator variable in the merged data set with a code of 1 indicating the time interval between testdate2 and testdate1 is less than 6 months and a code of 0 if the time interval between testdate2 and testdate1 is more than 6 months.

[novinosrin]

changing do lt (less than< 
proc sql;
create table want as
select *,intck('month',testdate1,testdate2)<6 as indicator
from have1 a, have2(rename=(testdate1=testdate2)) b
where a.id=b.id;

quit;

[newbe]

Thanks for your help! It doesn't work. Testdate1 and testdate2 are not in the same data set.

[newbe]

Thanks for your input! Problem is solved.

[Reeza · Accepted solution]

---

@newbe wrote:

Thanks for your input! Problem is solved.

---

@newbe Please mark the appropriate solution or post the solution you found in the end. 

Thanks.

[kiranv_]

code copied from @novinosrin , add a case statement.

proc sql;
create table want as
select *, case when intck('month',a.testdate1,b.testdate2)<6 then 1 else 0 end as indicatior
from have1 a, have2 b
where a.id=b.id;

quit;