R语言合并带时间戳的异构数据帧报错求助（SAS转R）

Hey there! Let's work through this issue step by step—since you're new to R and converting SAS code, those little syntax and type hiccups are totally normal, so don't stress.

First, let's break down the error messages you're seeing:

• The main error ("table name2 has no column named...") almost always points to a column name mismatch—either a typo, case sensitivity (SQLite, which sqldf uses, is case-sensitive for column names unlike SAS), or hidden spaces/special characters in your column names.
• The warning about field_types usually happens when sqldf struggles to auto-infer the data types of your columns, especially if you have date/time fields stored as characters instead of proper date/time objects.

Here's how to fix this, with two approaches (one sticking to sqldf, another using dplyr which might be more intuitive for R beginners):

Step 1: First, double-check your column names #

Make sure the columns you're referencing actually exist and are spelled correctly:

# Print out column names for both data frames to verify
colnames(name1)
colnames(name2)

Confirm name1 has p_start_time and name, and name2 has start_time and name. If any column names have spaces or special characters (like start time instead of start_time), you'll need to wrap them in backticks ` in your SQL query.

Step 2: Ensure your time columns are proper date/time types #

SAS handles dates/times automatically, but R needs explicit types. If your start_time and p_start_time are stored as character strings, convert them to POSIXct (for datetime) or Date (for just dates) first:

# Replace the format argument with your actual timestamp format (e.g., "%m/%d/%Y %H:%M")
name1$p_start_time <- as.POSIXct(name1$p_start_time, format = "%Y-%m-%d %H:%M:%S")
name2$start_time <- as.POSIXct(name2$start_time, format = "%Y-%m-%d %H:%M:%S")

Approach 1: Fix the sqldf query #

Once your columns are sorted, here's the corrected sqldf code to replicate your SAS inner join with filtering:

library(sqldf)

# If your column names are clean (no spaces/special chars):
name_c <- sqldf("SELECT * 
                 FROM name1 a
                 INNER JOIN name2 b
                 ON a.name = b.name
                 WHERE b.start_time <= a.p_start_time")

# If you have columns with spaces/special chars, wrap them in backticks:
# name_c <- sqldf("SELECT * 
#                  FROM name1 a
#                  INNER JOIN name2 b
#                  ON a.`name` = b.`name`
#                  WHERE b.`start_time` <= a.`p_start_time`")

To fix the field_types warning, you can explicitly tell sqldf what each column's type is:

# Get column types for both data frames
col_types <- c(sapply(name1, class), sapply(name2, class))

name_c <- sqldf("SELECT * 
                 FROM name1 a
                 INNER JOIN name2 b
                 ON a.name = b.name
                 WHERE b.start_time <= a.p_start_time",
               field_types = col_types)

Approach 2: Use dplyr (more R-friendly for beginners) #

If you're open to trying a non-SQL approach, dplyr's syntax is more intuitive for R workflows and avoids some of sqldf's type quirks:

library(dplyr)

name_c <- name1 %>%
  # Inner join on the 'name' column
  inner_join(name2, by = "name") %>%
  # Filter rows where name2's start_time is <= name1's p_start_time
  filter(start_time <= p_start_time)

This will give you exactly the same full-column merged result you're looking for, with easier debugging if something goes wrong.

Either way, after running this, name_c should be your desired merged and filtered data frame!

内容的提问来源于stack exchange，提问作者newbie_146