CODEWITHSUNDEEP
rstata
Aline
Aline

Reputation: 347

Is there a way to create Stata's _merge indicator variable with R's merge()?

Stata automatically creates a variable called "_merge" indicating the matched variables in both datasets after merge. Is there a way to get such variable generated by R's merge() function?

Upvotes: 7

Views: 2761

Answers (3)

rwbuie
rwbuie

Reputation: 31

Here is (I think) a far simpler and more efficient version of the previous person's stata.merge function. This assumes you don't have variables named "new1" or "new2" in your data frames. If this assumption is wrong, change the variable names in this function. This function takes 3 variables, the first data frame, the second data frame, and the value to enter into the "by =" part of the merge function.

stata.merge <- function(x,y, name){
  x$new1 <- 1
  y$new2 <- 2
  df <- merge(x,y, by = name, all = TRUE)
  df$stat.merge.variable <- rowSums(df[,c("new1", "new2")], na.rm=TRUE)
  df$new1 <- NULL
  df$new2<- NULL
  df
}

Upvotes: 1

Aline
Aline

Reputation: 347

I have written the following function based on @Metrics answer. It creates a variable "merge" in the resulting dataset that indicates observations as Stata does.

stata.merge <- function(x,y, by = intersect(names(x), names(y))){

x[is.na(x)] <- Inf
y[is.na(y)] <- Inf

matched <- merge(x, y, by.x = by, by.y = by, all = TRUE)
matched <- matched[complete.cases(matched),]
matched$merge <- "matched"
master <- merge(x, y, by.x = by, by.y = by, all.x = TRUE)
master <- master[!complete.cases(master),]
master$merge <- "master"
using <- merge(x, y, by.x = by, by.y = by, all.y = TRUE)
using <- using[!complete.cases(using),]
using$merge <- "using"

df <- rbind(matched, master,using)
df[sapply(df, is.infinite)] <- NA
df
}

Test.

df1 <- data.frame(id = letters[c(1:5,8:9)], v1=c(1:5,8:9))
df1

   id v1
1  a  1
2  b  2
3  c  3
4  d  4
5  e  5
6  h  8
7  i  9

df2 <- data.frame(id = letters[1:8], v1=c(1:7,NA))
df2

  id v1
1  a  1
2  b  2
3  c  3
4  d  4
5  e  5
6  f  6
7  g  7
8  h NA

stata.merge(df1,df2, by = "id")

   id v1.x v1.y   merge
1   a    1    1 matched
2   b    2    2 matched
3   c    3    3 matched
4   d    4    4 matched
5   e    5    5 matched
6   h    8   NA matched
7   i    9   NA  master
71  f   NA    6   using
8   g   NA    7   using

Upvotes: 4

Metrics
Metrics

Reputation: 15458

The possible values of _merge in Stata are (note merge can also have values 4 and 5)

              1       master             observation appeared in master only
              2       using              observation appeared in using only
              3       match              observation appeared in both

In R, you can do that by entering the argument as either all=TRUE or all.x=TRUE or all.y=TRUE

e.g., 

merge(x, y, by = intersect(names(x), names(y)),by.x = by, by.y = by, all = TRUE)
 merge(x, y, by = intersect(names(x), names(y)),by.x = by, by.y = by, all.x = TRUE)
 merge(x, y, by = intersect(names(x), names(y)),by.x = by, by.y = by, all.y = TRUE)

Upvotes: 6

Related Questions