逐字段比对SAS数据行差异及生成状态行、差异列表的技术问题

Got it, let's fix your SAS code issues step by step. The core problem with your original code is that the lag1() function returns a missing value for the first row, so every field gets incorrectly flagged as different. Here's how to resolve that, plus add the status column and generate the diff-only table you need:

1. Fix Row-by-Row Comparison with Status Column #

We'll use a temporary array to store the previous row's values, which avoids the lag function's quirk with the first row. This way, we only compare starting from the second row:

data Have_with_status;
    length variables $1000 status $6;
    set Have;
    array L L_CONTRACT -- L_PRODUCT;
    array lag_vals[13] _temporary_; /* Match the number of fields in your L array (13 here) */
    
    /* Handle first row: no prior row to compare, so mark as OK */
    if _n_ = 1 then do;
        status = 'OK';
        variables = '';
    end;
    /* Compare current row to previous row for all subsequent rows */
    else do;
        variables = '';
        do i = 1 to dim(L);
            if L(i) ne lag_vals[i] then do;
                variables = catx(' ', variables, vname(L(i)));
            end;
        end;
        /* Set status based on whether differences were found */
        status = ifc(length(variables) > 0, 'NOT OK', 'OK');
    end;
    
    /* Update the temporary array with current row values for the next iteration */
    do i = 1 to dim(L);
        lag_vals[i] = L(i);
    end;
    
    drop i;
run;

What this does:

• The _temporary_ array holds the previous row's values, eliminating false positives from the lag function's missing value on the first row.
• The first row is explicitly marked as OK with an empty variables field.
• For the second row (and beyond), we check each field against the stored previous values, build the variables list with differing field names, and set status accordingly.

2. Generate Table with Only Differing Columns #

Once we have the list of differing fields from the second row, we can create a new table that only includes those columns (plus an identifier like REFERENCE_DATE to keep context):

/* Extract the differing columns into a macro variable */
proc sql noprint;
    select variables into :diff_cols separated by ' '
    from Have_with_status
    where _n_ = 2; /* Target the second row where differences exist */
quit;

/* Create the diff-only table */
data diff_columns_only;
    set Have;
    keep REFERENCE_DATE &diff_cols.; /* Keep reference date plus your differing columns */
run;

Result:
For your test data, this will create a table with columns: REFERENCE_DATE, L_CONTRACT_ACTIVITY, L_CONTRACT_CO_CUSTOMER, L_CONTRACT_OBJECT, L_CUSTOMER, L_PRODUCT — exactly the 5 differing fields you mentioned.

Quick Verification #

Running this code on your test Have dataset will produce:

• Have_with_status: First row has status=OK and empty variables; second row has status=NOT OK with the 5 differing field names in variables.
• diff_columns_only: Contains only the columns that showed differences between the two rows.

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