如何在SAS数据集中保留值？实现指定District的Racemajor值批量填充

Hey there! Let's solve this problem where we need to fill the racemajor field with "Yes" for every row in a district, if that district has at least one row where racemajor is already "Yes". Here are a couple of easy-to-implement approaches:

Approach 1: Using PROC SQL with Window Functions #

This method uses a window function to check for the presence of "Yes" in each district, then applies the value to all rows in that district. It's concise and efficient:

proc sql;
    create table want as
    select 
        pop,
        district,
        /* Check if any row in the district has 'Yes', then set all to 'Yes' */
        case 
            when max(case when racemajor = 'Yes' then 1 else 0 end) over (partition by district) = 1 
            then 'Yes' 
            else racemajor 
        end as racemajor
    from have;
quit;

How it works: #

• The inner case converts "Yes" values to 1 and all others to 0.
• max() over (partition by district) calculates the maximum value (1 if any "Yes" exists) for each district.
• The outer case sets racemajor to "Yes" if the district has a 1, otherwise keeps the original value.

Approach 2: Using a Lookup Table + Data Step Merge #

If you prefer a more step-by-step approach, create a lookup table of districts that have "Yes", then merge it back to the original data to update the field:

/* Step 1: Create a lookup table of districts with at least one 'Yes' */
proc sql;
    create table district_has_yes as
    select distinct district, 'Yes' as racemajor_new
    from have
    where racemajor = 'Yes';
quit;

/* Step 2: Merge the lookup table with original data to update values */
data want;
    merge have district_has_yes(in=has_yes);
    by district;
    /* If the district is in the lookup table, set racemajor to 'Yes' */
    if has_yes then racemajor = racemajor_new;
    drop racemajor_new;
run;

How it works: #

• First, we identify all districts that have at least one "Yes" entry and store them in a separate table.
• When merging, we use the in=has_yes flag to check if the current district is in our lookup table. If yes, we overwrite racemajor with "Yes".

Approach 3: Data Step with Retain (For Row-by-Row Processing) #

If you want to use pure Data Step logic (no SQL), you can sort the data first, then use a retained variable to track if the district has a "Yes":

/* Sort data by district first */
proc sort data=have;
    by district;
run;

/* Propagate 'Yes' to all rows in the district */
data want;
    set have;
    by district;
    retain has_yes;
    
    /* Reset the flag at the start of a new district */
    if first.district then do;
        has_yes = 'No';
        /* Scan all rows in the current district to check for 'Yes' */
        do _i = _n_ to nobs until (has_yes = 'Yes');
            set have(keep=district racemajor) point=_i nobs=nobs;
            if racemajor = 'Yes' then has_yes = 'Yes';
        end;
        /* Return to the current row's position */
        set have point=_n_;
    end;
    
    /* Update racemajor if the district has a 'Yes' */
    if has_yes = 'Yes' then racemajor = 'Yes';
    drop has_yes _i;
run;

All three methods will produce the same result: every row in the "Adelaid" district will have racemajor = 'Yes', while rows in other districts keep their original racemajor values.

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