DATA Step, Macro, Functions and more

closest match by visit date

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

closest match by visit date

[ Edited ]

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!

 

 

 

 

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎07-11-2017 08:38 PM
PROC Star
Posts: 1,561

Re: closest match by visit date

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

 

View solution in original post


All Replies
Solution
‎07-11-2017 08:38 PM
PROC Star
Posts: 1,561

Re: closest match by visit date

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

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 92 views
  • 1 like
  • 2 in conversation