682526_en-US

liliconcom

date differences in two datasets

Hi,

 

I have two datasets on a cohort of patients.  One has dates of test “a” and the other has dates of test “b”.  the number of tests a and b on a subject vary (i.e., some have a lot of a and few b or visa versa)

 

I need to find all instances where tests a and b are within a timeframe (90 days for this project).  How could I automate the comparison of all combinations of test a and b to find the tests within this parameter?  Stated differently, how do I compare every test a date against all test b dates to caculate a date difference (and isolate those <90d)?  if I just get a list of all the date-diff combinations I can easily sort to find those <90d

 

Example below

 

DATASET1

Subject #         Test A date                             

1                      1/1/23                                    

1                      2/1/23

1                      3/1/23

1                      4/1/23

2                      2/1/10

2                      5/1/10

 

 

DATASET2

Subject #         Test B date

1                      1/1/14

1                      6/1/23

1                      11/1/22

2                      1/1/10

2                      1/1/23

2                      1/5/23

 

RESULTS
subject            test a date       test b date       date diff         

1                      1/1/23             11/1/22           60d

1                      3/1/23             6/1/23             90d

2                      2/1/10             1/1/10             30d     

Data Blending and Cleanup