Match one to multiple roes

reddy19 — Wed, 18 Jul 2018 02:41:43 GMT

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

Re: Match one to multiple roes

tomrvincent — Mon, 23 Jul 2018 18:14:53 GMT

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?

topic Match one to multiple roes in SAS Health and Life Sciences

Match one to multiple roes

Re: Match one to multiple roes