按特定航线及航空公司填充SAS数据集Arr_Delay缺失值求助

Got it, let's solve this problem! Your original PROC STDIZE code fills missing values with the overall dataset mean, but it doesn't account for grouping by carrier (airline) and origin-dest (route) pairs. Here are two reliable methods to fill missing Arr_Delay values using the average delay of their specific carrier-route group:

Method 1: One-Step Solution with PROC SQL #

This approach calculates group averages and fills missing values in a single query—clean and efficient:

PROC SQL;
    CREATE TABLE cs1.Complete_data AS
    SELECT 
        p.*,
        /* Use COALESCE to keep original value if present, else use group average */
        COALESCE(p.Arr_Delay, g.group_avg_delay) AS Arr_Delay
    FROM cs1.Planes p
    LEFT JOIN (
        /* Subquery to compute average delay per carrier-origin-dest group */
        SELECT 
            carrier,
            origin,
            dest,
            AVG(Arr_Delay) AS group_avg_delay
        FROM cs1.Planes
        WHERE Arr_Delay IS NOT MISSING  /* Ignore missing values when calculating averages */
        GROUP BY carrier, origin, dest
    ) g 
        ON p.carrier = g.carrier 
        AND p.origin = g.origin 
        AND p.dest = g.dest;
QUIT;

How it works: #

• The subquery first generates the average Arr_Delay for every unique combination of carrier, origin, and dest.
• We use a LEFT JOIN to attach this group average to each row in the original dataset.
• The COALESCE function checks if Arr_Delay is missing: if yes, it replaces it with the group average; if not, it keeps the original value.

Method 2: Step-by-Step with PROC MEANS + DATA Step #

If you prefer breaking the process into separate steps (easier to debug), use this method:

Step 1: Calculate Group Averages #

PROC MEANS DATA=cs1.Planes NOPRINT;
    CLASS carrier origin dest;  /* Group by these three variables */
    VAR Arr_Delay;
    OUTPUT OUT=group_means MEAN=group_avg_delay;  /* Store group averages in a new table */
RUN;

Step 2: Clean Up the Group Averages Table #

PROC MEANS generates summary rows for different group levels—we only keep rows where all three grouping variables are used:

DATA group_means_clean;
    SET group_means;
    WHERE _TYPE_ = 7;  /* _TYPE_=7 means all three CLASS variables are included in the group */
    KEEP carrier origin dest group_avg_delay;  /* Keep only necessary columns */
RUN;

Step 3: Merge and Fill Missing Values #

DATA cs1.Complete_data;
    MERGE cs1.Planes (IN=original) group_means_clean;
    BY carrier origin dest;
    IF original;  /* Ensure we only keep rows from the original dataset */
    /* Fill missing Arr_Delay with the group average */
    IF Arr_Delay IS MISSING THEN Arr_Delay = group_avg_delay;
RUN;

Important Note #

If a specific carrier-origin-dest group has all missing Arr_Delay values, the group average will also be missing. If you need a fallback (like using the overall dataset average for these cases), let me know and I can adjust the code!

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