Hi all,
below is my sample data for two data sets:
data A:
ID VISITNUM maxofdat1dat2 dat1 dat2
304 1 11JUN2019 07MAR2019 11JUN2019
304 101 13JUN2019 13JUN2019 13JUN2019
304 102 12JUL2019 12JUL2019 12JUL2019
304 103 09AUG2019 09AUG2019 09AUG2019
175 107 28JUN2019 28JUN2019 28JUN2019
175 106 29MAY2019 29MAY2019 29MAY2019
175 105 02MAY2019 02MAY2019 02MAY2019
175 1 10JAN2019 15OCT2018 10JAN2019
175 102 07MAR2019 07FEB2019 07MAR2019
175 103 04APR2019 08MAR2019 04APR2019
175 104 05APR2019 05APR2019 05APR2019
175 101 10JAN2019 10JAN2019 10JAN2019
164 106 23MAY2019 23APR2019 23MAY2019
164 103 20JUN2019 25FEB2019 20JUN2019
164 108 16JUL2019 13JUL2019 16JUL2019
164 107 20JUN2019 17JUN2019 20JUN2019
164 104 28MAR2019 25MAR2019 28MAR2019
164 102 31JAN2019 28JAN2019 31JAN2019
164 105 25APR2019 20APR2019 25APR2019
164 109 13AUG2019 13AUG2019 13AUG2019
164 1 21DEC2018 01JUN1995 21DEC2018
164 101 03JAN2019 31DEC2018 03JAN2019
Data B:
ID dat3
304 06MAY2019
304 13JUN2019
304 13JUL2019
304 09AUG2019
175 04FEB2019
175 08MAR2019
175 07MAR2019
175 02MAY2019
175 28JAN2019
175 28JUN2019
175 07MAR2019
175 08MAR2019
175 10JAN2019
175 04FEB2019
175 07MAR2019
175 10JAN2019
175 28JAN2019
175 07MAR2019
175 07MAR2019
164 28FEB2019
164 28MAR2019
164 28MAR2019
164 20JUN2019
164 28MAR2019
164 04JAN2019
Here is my derivation for visitnum:
"Assigned from data A, assign visitnum for each date in data B by comparing to dat1 and dat2 in data A. If there are more than 1 VISITNUM that includes the date, then assign VISITNUM as the larger VISITNUM number. If the date is not included in any VISITNUM, then choose the LAST (largest) VISITNUM where dat3 > dat2."
Can anyone provide me code for this logic.
And please let me know if anything. Thank you in advance!
Regards,
Adi