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!!