Match one to multiple roes

[reddy19]

I have lab data set with below information:

subject    visit   vst_date      test    results
001    visit1  04FEB2018     abc  y
002    visit2  07FEB2018     abc  y

In the database , i have multiple visits for this subject. Here is the sample data

subject    visit    vst_date      test    results
001    visit1wk1    04FEB2018     xyz  y
001    visit2wk2   07FEB2018     XYZ  y
001    visit1wk3    08FEB2018     xyz  y
002    visit2   09FEB2018     XYZ  y

I need to combine both these files,output should look like this

subject    visit    vst_date      test    results
001    visit1wk1    04FEB2018     xyz  y
001    visit1wk1   04FEB2018     abc  y
001    visit2wk2   07FEB2018     XYZ  y
001    visit1wk3    08FEB2018     xyz  y
002    visit2   09FEB2018     XYZ  y

For few visits, single visit in lab data we have multiple visits in database.
link with ‘Visit1’ and vst_date and where a match in database change lab visit to database visit name. If multiple database ‘Visit1’ and vst_date found with same
visit date, lab visit mapped to the first database combination. For example for visist1,  i need to check against visit1wk1, visit1wk2, visit1wk3 with which
visit date value matches, then i need to replace value of the visit in vendor file with that visit. I can use proc format, but there are 200 different visits need to specify. Is there any other look up method i can use to solve this scenario while joining??

Thanks in advance!!

[tomrvincent]

You could union the 2 tables together...maybe then pick max(visit) per subject and date if you need both 2/4 records to show visit1wk1..

Why does the 2/7 visit for subj 2 drop off?