基于File A两列匹配合并两个SAS文件的技术需求

Got it, let's work through this SAS merging scenario. Your requirement is clear: first match the C1(name) from File A to File B to pull the last used value, and if that doesn't work, check each value in File A's C2(other name) column until we find a match in File B. Here's how to implement this effectively:

Step 1: Create Sample Datasets #

First, let's replicate your sample data so we can test the code directly:

/* Create File A with name and other name columns */
data file_a;
    input C1 $ C2 $;
    datalines;
Apple      "Fruit_1 Fruit_2"
Orange     "Fruit_1 Fruit_2"
Carrot     "Vegetable_1 Vegetable_2"
Potato     "Vegetable_1 Vegetable_2"
;
run;

/* Create File B with name and last used columns */
data file_b;
    input C1 $ C3 $;
    datalines;
Apple      2014
Fruit_1    2011
Carrot     2010
Vegetable_2 2018
;
run;

Step 2: Priority Matching with Data Step #

This approach uses a custom format for fast lookups, then falls back to scanning the C2 column if the initial match fails. It's flexible even if C2 has more than two values:

/* Convert File B into a SAS format for quick value lookup */
proc format cntlin=file_b;
    value $lastused
        other = .; /* Assign missing if no match */
run;

/* Process File A to get the correct last used value */
data merged_result;
    set file_a;
    /* First attempt: match using File A's C1 column */
    C3 = put(C1, $lastused.);
    
    /* If no match found, scan each value in C2 until we get a hit */
    if missing(C3) then do;
        length word $20; /* Define temporary variable for each word in C2 */
        do i = 1 to countw(C2); /* Loop through all words in C2 */
            word = scan(C2, i); /* Extract individual word from C2 */
            C3 = put(word, $lastused.); /* Check if this word exists in File B */
            if not missing(C3) then leave; /* Stop looping once we find a match */
        end;
    end;
    
    drop i word; /* Remove temporary variables from final dataset */
run;

/* Verify the merged result */
proc print data=merged_result noobs;
    title "Final Merged Dataset";
run;

Step 3: Alternative SQL Approach #

If you prefer using PROC SQL, here's a version that handles the priority logic with left joins and COALESCE:

proc sql;
    create table merged_result_sql as
        select 
            a.C1 as "C1(name)" $,
            a.C2 as "C2(other name)" $,
            coalesce(b1.C3, b2.C3) as "C3(last used)" $
        from file_a a
        /* First join: match on C1 */
        left join file_b b1 on a.C1 = b1.C1
        /* Second join: match on either value in C2 */
        left join file_b b2 on scan(a.C2, 1) = b2.C1 or scan(a.C2, 2) = b2.C1
        group by a.C1, a.C2
        having coalesce(b1.C3, b2.C3) is not null;
quit;

Key Notes: #

• The data step method is more scalable if C2 has a variable number of values (it will scan all words until a match is found).
• The SQL method works well if you know C2 always has exactly two values, as we explicitly check the first and second words.

Both methods will produce your desired output:

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