topic Re: get test values within 60 days [+/-] of each othe in New SAS User

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

jeetendersinghc — Fri, 22 Feb 2019 16:51:21 GMT

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

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

Reeza — Fri, 22 Feb 2019 17:09:35 GMT

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

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

jeetendersinghc — Fri, 22 Feb 2019 17:16:37 GMT

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.

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

jeetendersinghc — Fri, 22 Feb 2019 17:28:11 GMT

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;

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

Reeza — Fri, 22 Feb 2019 17:35:40 GMT

@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.

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

jeetendersinghc — Fri, 22 Feb 2019 17:38:58 GMT

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

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

Reeza — Fri, 22 Feb 2019 18:17:54 GMT

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.

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

jeetendersinghc — Fri, 22 Feb 2019 18:27:36 GMT

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

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

neil011 — Fri, 22 Feb 2019 18:59:43 GMT

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;

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

Reeza — Fri, 22 Feb 2019 19:01:16 GMT

Show it with the sample data.

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

jeetendersinghc — Fri, 22 Feb 2019 19:06:33 GMT

Thanks Neil.

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

Jeetender

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

Reeza — Fri, 22 Feb 2019 19:40:31 GMT

Mark Neil’s answer as the solution please.