closest match by visit date

[Chung-Li]

Hi guys,

What I'm trying to do is to merge several data sets and then calculate length with first variable variables.

For example,

data set 1:

                      Obs    MRN                     DM_Med_Date             DM_Med

                        1    CGMHLK0001              2014/06/18                 1
                        2    CGMHLK0002              2014/07/09                12
                        3    CGMHLK0003              2014/06/24                 1
                        4    CGMHLK0004              2014/07/09                12

data set 2:

                       Obs    MRN                     NSAID_Date              NSAID

                         1    CGMHLK0001              2014/06/18                1
                         2    CGMHLK0002              2014/07/09                1
                         3    CGMHLK0003              2014/06/24                1
                         4    CGMHLK0004              2014/07/09                1

data set 3:

                                                                             NSAID_
                      Obs    MRN                     NSAID_freq_Date          freq

                        1    CGMHLK0001              2014/06/18                 1
                        2    CGMHLK0002              2014/07/09                 2
                        3    CGMHLK0003              2014/06/24                 1
                        4    CGMHLK0004              2014/07/09                 1

What I want:

Obs	MRN	        DM_Med_Date	DM_Med	NSAID_Date	NSAID	DateLength_DM_Med_VS_NSAID	NSAID_freq_Date	NSAID_freq	DateLength_DM_Med_VS_NSAID_freq
1	CGMHLK0001	2014/6/18	1	2014/6/18	1	0 	                        2014/6/18	1	        0
2	CGMHLK0001	2014/6/18	1	2014/6/18	1	0                        	2014/7/9	2	        21
3	CGMHLK0001	2014/6/18	1	2014/6/18	1	0 	                        2014/6/24	1	        6
4	CGMHLK0001	2014/6/18	1	2014/6/18	1	0                         	2014/7/9	1	        21
5	CGMHLK0001	2014/6/18	1	2014/7/9	1	21                        	2014/6/18	1	        0
6	CGMHLK0001	2014/6/18	1	2014/7/9	1	21                        	2014/7/9	2	        21
7	CGMHLK0001	2014/6/18	1	2014/7/9	1	21 	                        2014/6/24	1	        6
8	CGMHLK0001	2014/6/18	1	2014/7/9	1	21 	                        2014/7/9	1	        21
9	CGMHLK0001	2014/6/18	1	2014/6/24	1	6 	                        2014/6/18	1	        0
10	CGMHLK0001	2014/6/18	1	2014/6/24	1	6 	                        2014/7/9	2	        21
11	CGMHLK0001	2014/6/18	1	2014/6/24	1	6 	                        2014/6/24	1	        6
12	CGMHLK0001	2014/6/18	1	2014/6/24	1	6 	                        2014/7/9	1	        21
13	CGMHLK0001	2014/6/18	1	2014/7/9	1	21                         	2014/6/18	1	        0
14	CGMHLK0001	2014/6/18	1	2014/7/9	1	21 	                        2014/7/9	2	        21
15	CGMHLK0001	2014/6/18	1	2014/7/9	1	21                         	2014/6/24	1	        6
16	CGMHLK0001	2014/6/18	1	2014/7/9	1	21                       	2014/7/9	1	        21
17	CGMHLK0002	2014/7/9	12	2014/6/18	1	21                      	2014/6/18	1	        21
18	CGMHLK0002	2014/7/9	12	2014/6/18	1	21                        	2014/7/9	2	        0
19	CGMHLK0002	2014/7/9	12	2014/6/18	1	21                       	2014/6/24	1	        15
20	CGMHLK0002	2014/7/9	12	2014/6/18	1	21                      	2014/7/9	1	        0
21	CGMHLK0002	2014/7/9	12	2014/7/9	1	0                       	2014/6/18	1	        21
22	CGMHLK0002	2014/7/9	12	2014/7/9	1	0 	                        2014/7/9	2	        0
23	CGMHLK0002	2014/7/9	12	2014/7/9	1	0 	                        2014/6/24	1	        15
24	CGMHLK0002	2014/7/9	12	2014/7/9	1	0 	                        2014/7/9	1	        0
25	CGMHLK0002	2014/7/9	12	2014/6/24	1	15                      	2014/6/18	1	        21
26	CGMHLK0002	2014/7/9	12	2014/6/24	1	15                      	2014/7/9	2	        0
27	CGMHLK0002	2014/7/9	12	2014/6/24	1	15                             	2014/6/24	1	        15
28	CGMHLK0002	2014/7/9	12	2014/6/24	1	15                             	2014/7/9	1	        0
29	CGMHLK0002	2014/7/9	12	2014/7/9	1	0                        	2014/6/18	1	        21
30	CGMHLK0002	2014/7/9	12	2014/7/9	1	0                        	2014/7/9	2	        0
31	CGMHLK0002	2014/7/9	12	2014/7/9	1	0                         	2014/6/24	1	        15
32	CGMHLK0002	2014/7/9	12	2014/7/9	1	0                        	2014/7/9	1	        0
33	CGMHLK0003	2014/6/24	1	2014/6/18	1	6                       	2014/6/18	1	        6
34	CGMHLK0003	2014/6/24	1	2014/6/18	1	6                       	2014/7/9	2	        15
35	CGMHLK0003	2014/6/24	1	2014/6/18	1	6                        	2014/6/24	1	        0
36	CGMHLK0003	2014/6/24	1	2014/6/18	1	6                        	2014/7/9	1	        15
37	CGMHLK0003	2014/6/24	1	2014/7/9	1	15                       	2014/6/18	1	        6
38	CGMHLK0003	2014/6/24	1	2014/7/9	1	15                       	2014/7/9	2	        15
39	CGMHLK0003	2014/6/24	1	2014/7/9	1	15                       	2014/6/24	1	        0
40	CGMHLK0003	2014/6/24	1	2014/7/9	1	15                      	2014/7/9	1	        15
41	CGMHLK0003	2014/6/24	1	2014/6/24	1	0                       	2014/6/18	1	        6
42	CGMHLK0003	2014/6/24	1	2014/6/24	1	0                       	2014/7/9	2	        15
43	CGMHLK0003	2014/6/24	1	2014/6/24	1	0                        	2014/6/24	1	        0
44	CGMHLK0003	2014/6/24	1	2014/6/24	1	0                            	2014/7/9	1	        15
45	CGMHLK0003	2014/6/24	1	2014/7/9	1	15                       	2014/6/18	1	        6
46	CGMHLK0003	2014/6/24	1	2014/7/9	1	15                      	2014/7/9	2	        15
47	CGMHLK0003	2014/6/24	1	2014/7/9	1	15                        	2014/6/24	1	        0
48	CGMHLK0003	2014/6/24	1	2014/7/9	1	15                        	2014/7/9	1	        15
49	CGMHLK0004	2014/7/9	12	2014/6/18	1	21                          	2014/6/18	1	        21
50	CGMHLK0004	2014/7/9	12	2014/6/18	1	21                          	2014/7/9	2	        0
51	CGMHLK0004	2014/7/9	12	2014/6/18	1	21                        	2014/6/24	1	        15
52	CGMHLK0004	2014/7/9	12	2014/6/18	1	21                          	2014/7/9	1	        0
53	CGMHLK0004	2014/7/9	12	2014/7/9	1	0                       	2014/6/18	1	        21
54	CGMHLK0004	2014/7/9	12	2014/7/9	1	0                        	2014/7/9	2	        0
55	CGMHLK0004	2014/7/9	12	2014/7/9	1	0 	                        2014/6/24	1	        15
56	CGMHLK0004	2014/7/9	12	2014/7/9	1	0                       	2014/7/9	1	        0
57	CGMHLK0004	2014/7/9	12	2014/6/24	1	15                            	2014/6/18	1	        21
58	CGMHLK0004	2014/7/9	12	2014/6/24	1	15                      	2014/7/9	2	        0
59	CGMHLK0004	2014/7/9	12	2014/6/24	1	15                            	2014/6/24	1	        15
60	CGMHLK0004	2014/7/9	12	2014/6/24	1	15                       	2014/7/9	1	        0
61	CGMHLK0004	2014/7/9	12	2014/7/9	1	0                       	2014/6/18	1	        21
62	CGMHLK0004	2014/7/9	12	2014/7/9	1	0                       	2014/7/9	2	        0
63	CGMHLK0004	2014/7/9	12	2014/7/9	1	0                       	2014/6/24	1	        15
64	CGMHLK0004	2014/7/9	12	2014/7/9	1	0                           	2014/7/9	1	        0

In this example, I set DM_Med as the first variable, so for each data set, I'll need to calculate the length difference with DM_Med_date.

While I'm still trying how to achieve this, very welcom if you have any idea!

Thanks in advance!

[ChrisNZ · Accepted solution]

Like this?

proc sql;
  select HAVE1.*
       , HAVE2.NSAID_Date
       , HAVE2.NSAID 
       , HAVE3.NSAID_freq_Date
       , HAVE3.FREQ
       , NSAID_Date     -DM_Med_Date as DateLength_DM_Med_VS_NSAID	
       , NSAID_freq_Date-DM_Med_Date as DateLength_DM_Med_VS_NSAID_freq
  from HAVE1, HAVE2, HAVE3;
quit;
 

MRN	DM_Med_Date	DM_Med	NSAID_Date	NSAID	NSAID_freq_Date	freq	DateLength_DM_Med_VS_NSAID	DateLength_DM_Med_VS_NSAID_freq
CGMHLK0001	2014-06-18	1	2014-06-18	1	2014-06-18	1	0	0
CGMHLK0001	2014-06-18	1	2014-07-09	1	2014-06-18	1	21	0
CGMHLK0001	2014-06-18	1	2014-06-24	1	2014-06-18	1	6	0
CGMHLK0001	2014-06-18	1	2014-07-09	1	2014-06-18	1	21	0
CGMHLK0001	2014-06-18	1	2014-06-18	1	2014-07-09	2	0	21
CGMHLK0001	2014-06-18	1	2014-07-09	1	2014-07-09	2	21	21
CGMHLK0001	2014-06-18	1	2014-06-24	1	2014-07-09	2	6	21
CGMHLK0001	2014-06-18	1	2014-07-09	1	2014-07-09	2	21	21
CGMHLK0001	2014-06-18	1	2014-06-18	1	2014-06-24	1	0	6
CGMHLK0001	2014-06-18	1	2014-07-09	1	2014-06-24	1	21	6
CGMHLK0001	2014-06-18	1	2014-06-24	1	2014-06-24	1	6	6
CGMHLK0001	2014-06-18	1	2014-07-09	1	2014-06-24	1	21	6
CGMHLK0001	2014-06-18	1	2014-06-18	1	2014-07-09	1	0	21
CGMHLK0001	2014-06-18	1	2014-07-09	1	2014-07-09	1	21	21
CGMHLK0001	2014-06-18	1	2014-06-24	1	2014-07-09	1	6	21
CGMHLK0001	2014-06-18	1	2014-07-09	1	2014-07-09	1	21	21
CGMHLK0002	2014-07-09	12	2014-06-18	1	2014-06-18	1	-21	-21
CGMHLK0002	2014-07-09	12	2014-07-09	1	2014-06-18	1	0	-21
CGMHLK0002	2014-07-09	12	2014-06-24	1	2014-06-18	1	-15	-21
CGMHLK0002	2014-07-09	12	2014-07-09	1	2014-06-18	1	0	-21
CGMHLK0002	2014-07-09	12	2014-06-18	1	2014-07-09	2	-21	0
CGMHLK0002	2014-07-09	12	2014-07-09	1	2014-07-09	2	0	0
CGMHLK0002	2014-07-09	12	2014-06-24	1	2014-07-09	2	-15	0
CGMHLK0002	2014-07-09	12	2014-07-09	1	2014-07-09	2	0	0
CGMHLK0002	2014-07-09	12	2014-06-18	1	2014-06-24	1	-21	-15
CGMHLK0002	2014-07-09	12	2014-07-09	1	2014-06-24	1	0	-15
CGMHLK0002	2014-07-09	12	2014-06-24	1	2014-06-24	1	-15	-15
CGMHLK0002	2014-07-09	12	2014-07-09	1	2014-06-24	1	0	-15
CGMHLK0002	2014-07-09	12	2014-06-18	1	2014-07-09	1	-21	0
CGMHLK0002	2014-07-09	12	2014-07-09	1	2014-07-09	1	0	0
CGMHLK0002	2014-07-09	12	2014-06-24	1	2014-07-09	1	-15	0
CGMHLK0002	2014-07-09	12	2014-07-09	1	2014-07-09	1	0	0
CGMHLK0003	2014-06-24	1	2014-06-18	1	2014-06-18	1	-6	-6
CGMHLK0003	2014-06-24	1	2014-07-09	1	2014-06-18	1	15	-6
CGMHLK0003	2014-06-24	1	2014-06-24	1	2014-06-18	1	0	-6
CGMHLK0003	2014-06-24	1	2014-07-09	1	2014-06-18	1	15	-6
CGMHLK0003	2014-06-24	1	2014-06-18	1	2014-07-09	2	-6	15
CGMHLK0003	2014-06-24	1	2014-07-09	1	2014-07-09	2	15	15
CGMHLK0003	2014-06-24	1	2014-06-24	1	2014-07-09	2	0	15
CGMHLK0003	2014-06-24	1	2014-07-09	1	2014-07-09	2	15	15
CGMHLK0003	2014-06-24	1	2014-06-18	1	2014-06-24	1	-6	0
CGMHLK0003	2014-06-24	1	2014-07-09	1	2014-06-24	1	15	0
CGMHLK0003	2014-06-24	1	2014-06-24	1	2014-06-24	1	0	0
CGMHLK0003	2014-06-24	1	2014-07-09	1	2014-06-24	1	15	0
CGMHLK0003	2014-06-24	1	2014-06-18	1	2014-07-09	1	-6	15
CGMHLK0003	2014-06-24	1	2014-07-09	1	2014-07-09	1	15	15
CGMHLK0003	2014-06-24	1	2014-06-24	1	2014-07-09	1	0	15
CGMHLK0003	2014-06-24	1	2014-07-09	1	2014-07-09	1	15	15
CGMHLK0004	2014-07-09	12	2014-06-18	1	2014-06-18	1	-21	-21
CGMHLK0004	2014-07-09	12	2014-07-09	1	2014-06-18	1	0	-21
CGMHLK0004	2014-07-09	12	2014-06-24	1	2014-06-18	1	-15	-21
CGMHLK0004	2014-07-09	12	2014-07-09	1	2014-06-18	1	0	-21
CGMHLK0004	2014-07-09	12	2014-06-18	1	2014-07-09	2	-21	0
CGMHLK0004	2014-07-09	12	2014-07-09	1	2014-07-09	2	0	0
CGMHLK0004	2014-07-09	12	2014-06-24	1	2014-07-09	2	-15	0
CGMHLK0004	2014-07-09	12	2014-07-09	1	2014-07-09	2	0	0
CGMHLK0004	2014-07-09	12	2014-06-18	1	2014-06-24	1	-21	-15
CGMHLK0004	2014-07-09	12	2014-07-09	1	2014-06-24	1	0	-15
CGMHLK0004	2014-07-09	12	2014-06-24	1	2014-06-24	1	-15	-15
CGMHLK0004	2014-07-09	12	2014-07-09	1	2014-06-24	1	0	-15
CGMHLK0004	2014-07-09	12	2014-06-18	1	2014-07-09	1	-21	0
CGMHLK0004	2014-07-09	12	2014-07-09	1	2014-07-09	1	0	0
CGMHLK0004	2014-07-09	12	2014-06-24	1	2014-07-09	1	-15	0
CGMHLK0004	2014-07-09	12	2014-07-09	1	2014-07-09	1	0	0