get test values within 60 days [+/-] of each othe

[jeetendersinghc]

data labtest;
   informat t1date t2date  yymmdd10.;
   input patid t1date t2date t1result t2result;
   format t1date t2date yymmddD10.;
datalines;
001 2012-05-19 2012-05-21 20 25
001 2013-04-21 2012-07-08 14 38
001 2012-09-08 2012-07-08 22 23
001 2015-07-27 2012-07-31 65 34
001 	.	   2013-04-19  . 23
001  . 		   2013-04-19  . 32
001 .  		   2013-05-29  . 35
001  . 		   2014-06-30  . 54
001 .          2015-07-27  . 34


;
run;

Hi ,

I need your help for above datasets.

I want to keep only those values  of test1 and test2  which are within 60 days [+/-] of each other. I tried by merging the datsets with PATID but i am getting note in my log" MERGE statement has more than one data set with repeats of BY values". 

thanks,

jeetender

[Reeza]

Based on the above input data set, what would you expect as output?

[jeetendersinghc]

For example if the test date for test1 is 8th sep2012 then i want the record for test2 which are with in +- 60 days  (8th july 2012 and 31july 2012.

[jeetendersinghc]

Pl refer this code. as i have these results in 2 different datasets .

data labtest1;
   informat t1date   yymmdd10.;
   input patid t1date  t1result ;
   format t1date  yymmddD10.;
datalines;
001 2012-05-19  20 
001 2013-04-21  14
001 2012-09-08  22 
001 2015-07-27  65 



;
run;
proc sort ;
by patid;
run;


data labtest2;
   informat  t2date  yymmdd10.;
   input patid  t2date  t2result;
   format  t2date yymmddD10.;
datalines;
001 2012-05-21  25
001 2012-07-08  38
001 2012-07-08  23
001 2012-07-31  34
001 2013-04-19  23
001 2013-04-19   32
001 2013-05-29   35
001 2014-06-30  54
001 2015-07-27   34


;
run;

proc sort ;
by patid;
run;

data labs; 
merge  labtest1 (in=b) labtest2 (in=c);
by patid ;
if b and c and t1date-60 le t2date le t1date+60;
difference=abs(t1date-t2date);
run;

[Reeza]

---

@jeetendersinghc wrote:

For example if the test date for test1 is 8th sep2012 then i want the record for test2 which are with in +- 60 days  (8th july 2012 and 31july 2012.

---

I understand that, what I don't know is how you want to format your results. Or what happens when a record belongs to two 60 day groups? Nothing you've posted answers that.

[jeetendersinghc]

I want to keep all those results which are falling under +-60 days windows.

[Reeza]

One attempt then. Note that data steps cannot do many to many merges though so if you're only merging on ID and you have multiple ID's in each data set you won't get what you expect. But I'm still not sure what you're expecting. If this is not what you want, you do need to show what you expect based on the input data, which you also need to clarify since you've changed the scenario from your first post.

data labs; 
merge  labtest1 (in=b) labtest2 (in=c);
by patid ;

if b and c;

days_diff = t2date - t1date;

if not ( -60 <= t2date - t1date <= 60) then delete; 

run;

---

@jeetendersinghc wrote:

I want to keep all those results which are falling under +-60 days windows.

---

[jeetendersinghc]

my research query states that  keep only those patients who has "Values of test1 and test2 within 60 days [+/-] of each other".

So i am expecting to keep value of test1 and test2 only if they are  with in 60 days to each other or else i cannot keep those records.

for example if test1 is conduted on 1st April then i need test2 value if it falls with in +60days(1June) or -60days(1feb) .

I belive you r right , i need to use many to many merge using proc sql which cannot be done through data merge statement.I would appreciate if you can help me.

thanks,

jeetender

[Reeza]

Show it with the sample data. 

[neil011 · Accepted solution]

hi Jeetendra

PS below. this might help.

proc sql noprint;
create table _cnt as
select a.*, b.*,
datdif(t1date,t2date,'act/act') as dif
from labtest1 as a full join labtest2 as b
on a.patid = b.patid;
quit;

proc print data=_cnt (where=(dif between -60 and 60));
by patid;
run;

[jeetendersinghc]

Thanks Neil.

This is exactly i need for my report. Thanks a lot for your help.

Jeetender

[Reeza]

Mark Neil’s answer as the solution please.