[R] Matching where the source file has one record/ subject and the to-be-matched file has multiple records/subect

[Chris Evans]

Perhaps I am misunderstanding but in tidyverse dialect I think this works:

library(tidyverse)
tibble(subject1 = 1:3,
        date = as.Date(c("01/01/2020", "01/01/2021", NA_Date_),
                       format = "%d/%m/%Y")) %>%
   mutate(rowN1 = row_number()) -> tmpTib1

tibble(subject2 = c(100, 100, 101),
        date = as.Date(c("01/01/2020", "01/01/2021", "02/01/2021"),
                       format = "%d/%m/%Y")) %>%
   mutate(rowN2 = row_number()) -> tmpTib2

tmpTib1
### gives
# A tibble: 3 × 3
#  subject1 date       rowN1
#     <int> <date>     <int>
#1        1 2020-01-01     1
#2        2 2021-01-01     2
#3        3 NA             3

tmpTib2
### gives
# A tibble: 3 × 3
#  subject2 date       rowN2
#     <dbl> <date>     <int>
#1      100 2020-01-01     1
#2      100 2021-01-01     2
#3      101 2021-01-02     3


tmpTib2 %>%
   left_join(tmpTib1, by = "date") %>%
   group_by(subject2) %>%
   ungroup() %>%
   filter(row_number() == 1)
### gives
# A tibble: 1 × 5
#  subject2 date       rowN2 subject1 rowN1
#     <dbl> <date>     <int>    <int> <int>
#1      100 2020-01-01     1        1     1

I think this would be highly inefficient if file 2 is huge with many records with the same date (matching a date from file1).
I have also assumed that you just want the first match in the default file sort order but you could rearrange the files to get the
order you want before doing the above.
My rowN variables are not needed!

But I suspect I am misunderstanding and I am sure there are other ways to do this without tidyverse.

Very best all,

Chris

On 22/07/2026 20:58, Sorkin, John wrote:
> I am not showing code, because I don't know how to write the code. I know that MatchIt performs propensity score matching, but I don't know how to use the function to perform the match I want to conduct.
>
> I have two files
> File 1: a list of n  records, 1 record/subject. Each record has a subject number and a date
> File 2: a list of m records m>>n with multiple records/subject but each record has a distinct date. All records for a given subject in file 2 have the same subject number, but each record has a different date.
>
> I want to match each record from file 1 with a single record from file 2 based on date. Once a record from file 2 is matched with a record from file 1, I want to remove all records from file 2 for the subject who was used in the match with the subject from file 1 so that the subject from file 2 will not be used in any future match.
>
>
> Example
> file 1                                          file 2
> subject   date                              subject   date
> 1              01/01/2020                  100   01/01/2020
> 2              01/01/2021                  100   01/10/2021
> 3                                                   101  02/01/2021
>    
> Subject 1 from file 1 is a perfect match for subject 100 from file2. The subjects are matched.
> All data from file 2 is removed for subject 100.
> Subject 2 from file 1 is then matched with subject 101 from file 2.
> If all records from subject 100 had not been removed from file2, subject 2 would have been matched to subject 100 date 01/10/2021. I don't want this to happen. Once subject 100 is used to match subject 1, I don't want any other person from file 1 to be matched with subject 100.
>
> I hope you can help me with this, I suspect, not uncommon matching problem!
>
> Thank you,
> John
>
> John David Sorkin M.D., Ph.D.
> Professor of Medicine, University of Maryland School of Medicine;
> Associate Director for Biostatistics and Informatics, Baltimore VA Medical Center Geriatrics Research, Education, and Clinical Center;
> Former PI Biostatistics and Informatics Core, University of Maryland School of Medicine Claude D. Pepper Older Americans Independence Center;
> Senior Statistician University of Maryland Center for Vascular Research;
>
> Division of Gerontology, Geriatrics and Palliative Medicine,
> 10 North Greene Street
> GRECC (BT/18/GR)
> Baltimore, MD 21201-1524
> Cell phone 443-418-5382
>
>
>
> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.