如何用SAS/SQL匹配变量名不同的两张表的共同变量？

Hey Tom, let's work through this problem together—it's less of a logic puzzle than it is a matter of mapping your key relationships and targeting the shared variables properly. Here's a step-by-step breakdown with actionable SAS/SQL code:

First, let's anchor on the given facts to ground our approach:

• Table 1 (1000 observations, 20 variables) has a primary key (let’s call it table1_key) that maps to Table 2’s primary key (say table2_key), and every row in Table 1 has a matching row in Table 2 via this key.
• No full rows are identical between the two tables, but you need to align all shared variables (variables with the same name in both tables) for each matched primary key pair.

Before writing the join, confirm exactly which variables exist in both tables. You can use SAS's built-in dictionary tables to automate this instead of listing them manually:

proc sql;
  select name
  from dictionary.columns
  where libname='WORK' and memname='TABLE1' /* Replace WORK with your library, TABLE1 with your actual table name */
  intersect
  select name
  from dictionary.columns
  where libname='WORK' and memname='TABLE2';
quit;

This query returns every variable name that appears in both tables—your official "common variables" list.

Now use the primary key relationship to join the tables, and pull in shared variables from both sides (since no full rows are identical, you’ll want clear aliases to distinguish which values come from which table).

Option 1: Manual PROC SQL Join (Great for Small Common Variable Lists) #

If you have a short list of shared variables, write out the join explicitly for clarity:

proc sql;
  create table matched_common_vars as
  select
    t1.table1_key as primary_key, /* Standardize the key name for consistency */
    /* Pull each shared variable from both tables with distinct aliases */
    t1.var1 as t1_var1, t2.var1 as t2_var1,
    t1.var2 as t1_var2, t2.var2 as t2_var2,
    t1.var3 as t1_var3, t2.var3 as t2_var3
    /* Add more variables as needed */
  from table1 t1
  inner join table2 t2
  on t1.table1_key = t2.table2_key; /* Critical: This is your primary key match condition */
quit;
The `inner join` works perfectly here because every row in Table 1 has a match in Table 2—you won’t lose any of Table 1’s observations.

Option 2: Automated PROC SQL Join (For Long Common Variable Lists) #

If you have lots of shared variables, use a macro to avoid typing every pair manually:

/* First, store the list of common variables in a macro variable */
proc sql noprint;
  select name into :common_vars separated by ', '
  from dictionary.columns
  where libname='WORK' and memname='TABLE1'
  intersect
  select name
  from dictionary.columns
  where libname='WORK' and memname='TABLE2';
quit;

/* Generate paired aliases for each common variable (e.g., t1.var as t1_var) */
%let var_pairs = ;
%do i=1 %to %sysfunc(countw(&common_vars));
  %let current_var = %scan(&common_vars, &i);
  %let var_pairs = &var_pairs t1.&current_var as t1_&current_var, t2.&current_var as t2_&current_var,;
%end;
/* Trim the trailing comma from the variable list */
%let var_pairs = %substr(&var_pairs, 1, %length(&var_pairs)-1);

/* Run the automated join */
proc sql;
  create table matched_common_vars as
  select
    t1.table1_key as primary_key,
    &var_pairs
  from table1 t1
  inner join table2 t2
  on t1.table1_key = t2.table2_key;
quit;

Option 3: SAS Data Step Merge #

If you prefer using a Data Step instead of SQL, sort both tables by their primary keys first, then merge:

/* Sort tables by their respective primary keys */
proc sort data=table1; by table1_key; run;
proc sort data=table2; by table2_key; run;

/* Merge and align shared variables with clear aliases */
data matched_common_vars;
  merge table1 (rename=(table1_key=primary_key var1=t1_var1 var2=t1_var2) in=t1)
        table2 (rename=(table2_key=primary_key var1=t2_var1 var2=t2_var2) in=t2);
  by primary_key;
  if t1; /* Keep only rows that exist in Table 1 (all have matches in Table 2) */
run;

Adjust the rename clauses to include all your shared variables, pairing each with a table-specific alias.

• Double-check your primary key match condition: Ensure t1.table1_key = t2.table2_key correctly links rows (you might need to adjust if key values are formatted differently, e.g., character vs numeric—use INPUT() or PUT() functions to convert if needed).
• If shared variables have mismatched data types between tables, convert them before joining to avoid errors (e.g., input(t2.var1, best12.) if Table 2’s var1 is character but Table 1’s is numeric).

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