在SAS中实现将月份列名赋值至新列并识别每行首次出现'b'值的对应月份列名

Alright, let's solve this problem where we need to identify the earliest month (extracted from column names) where the value 'b' first appears in each row, then store that month in a new column output. Let's start with the original dataset you provided:

data have;
input v_202002 $1. v_202003 $1. v_202001 $1.;
datalines;
a . b
. . b
a b b
. b a
b b a
;

Key Requirement Breakdown #

The goal is to create a new column output that holds the month string (like 202001) from the first column containing 'b' when checking columns in chronological month order (202001 → 202002 → 202003), not the input column order. Your expected output confirms this—for example, in the third row where both v_202003 and v_202001 have 'b', we pick 202001 since it's the earlier month.

Solution Code #

Here's the SAS code that achieves exactly what you need:

data want;
    set have;
    * Create array of variables ordered by chronological month;
    array month_vars[3] $ v_202001 v_202002 v_202003;
    * Temporary array to map variables to their month strings;
    array months[3] $6 _temporary_ ('202001', '202002', '202003');
    
    * Initialize output column as missing character value;
    length output $6;
    output = '';
    
    * Loop through months in order to find the first 'b';
    do i = 1 to dim(month_vars);
        if month_vars[i] = 'b' then do;
            output = months[i];
            leave; * Exit loop immediately after finding the first match;
        end;
    end;
    drop i; * Remove loop counter from final dataset;
run;

How This Works #

• Array Order: The month_vars array is sorted by month, so we check the earliest month first. This ensures we capture the first chronological occurrence of 'b'.
• Mapping Months: The months temporary array directly pairs each variable with its corresponding month string, making the assignment simple.
• Early Exit: The leave statement stops the loop as soon as we find a 'b', so we don't waste resources checking later columns once we have our answer.

Result Dataset #

When you run this code, you'll get your exact expected output:

If you ever need to check in the original input column order instead, just adjust the month_vars array to match the input sequence (v_202002 v_202003 v_202001) and update the months array accordingly.

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