如何通过补全空白扩展数据集：为每个ID生成2017-2020年份记录

Hey there! Let's work through this problem to get exactly what you need—each ID paired with the 2017-2020 vintage years, with blank values ready for your later array-based fills.

First, let's fix the "duplicating all observations" issue #

It sounds like your initial code was probably repeating entire rows instead of expanding each ID across multiple years. The key here is to use a do loop (either directly over the years or with an array) to generate a new row for each vintage year per ID.

Method 1: Simple Do Loop (Straightforward for Fixed Years) #

If you just need 2017-2020, this is the easiest approach. We'll loop through each year, set your target variables to blank/missing, and output a row for each iteration:

data want;
    set your_dataset_name; /* Replace with your actual dataset name */
    /* Loop through each vintage year */
    do vintage_year = 2017 to 2020;
        /* Reset variables you want blank to missing */
        /* For numeric variables: use call missing(var_name); */
        /* For character variables: var_name = ''; */
        /* Example: call missing(sales, profit); or revenue = ''; */
        /* If you want ALL variables except ID and vintage_year blank: */
        call missing(of _all_ except ID vintage_year);
        output; /* Write the row to the new dataset */
    end;
run;

Method 2: Using an Array (For Flexible Year Lists) #

Since you mentioned you have an array for later years, we can adapt this to use an array of vintage years. This is great if you might need to adjust the year list later:

data want;
    set your_dataset_name;
    /* Define a temporary array holding your vintage years */
    array vintage_years[4] _temporary_ (2017, 2018, 2019, 2020);
    
    /* Loop through each element in the array */
    do i = 1 to dim(vintage_years);
        vintage_year = vintage_years[i]; /* Assign the current year */
        /* Reset variables to blank (same as above) */
        call missing(of _all_ except ID vintage_year);
        output;
    end;
    drop i; /* Clean up the loop counter */
run;

Pro Tip: If Your Original Data Has Duplicate IDs #

If your source dataset has multiple rows per ID, first deduplicate it so each ID appears once before expanding:

proc sort data=your_dataset_name nodupkey;
    by ID;
run;

/* Then run either of the data steps above */

This way, you won't end up with duplicate ID-year combinations.

The main idea is that we're generating a new row for each year per ID, rather than just copying existing rows. The call missing() function is a quick way to set variables to blank/missing without listing each one individually.

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