为每个ID分配年份变量的SAS代码问题咨询

Let's break down the issues with your current code and fix it to get the exact output you're expecting.

First, let's identify the problems in your existing code: #

• Unnecessary sort step: After creating Have2, you run another proc sort on it. Since you already sorted Have by ID, and Have2 generates 4 consecutive rows per ID when first.id triggers, Have2 is already ordered by ID—this sort is redundant.
• Incorrect merge step: The final merge have2 have will cause unexpected results if your original Have dataset has multiple rows per ID. It creates a Cartesian product (4 rows from Have2 multiplied by N rows from Have for each ID), leading to more rows than you want. Even if Have has one row per ID, the merge adds unnecessary variables from Have when you only need ID and Vintage.

Solution 1: If your original Have dataset has unique IDs (one row per ID) #

This code directly generates your desired output without extra steps:

/* Ensure your input dataset is sorted by ID (skip if already sorted) */
proc sort data=Have;
    by ID;
run;

/* Generate 4 rows per ID with Vintage 2017-2020 */
data Have3;
    set Have(keep=ID); /* Keep only ID to avoid extra variables */
    by ID;
    if first.ID then do;
        do Vintage = 2017 to 2020;
            output; /* Output one row for each vintage year */
        end;
    end;
run;

Solution 2: If your original Have dataset has multiple rows per ID (and you want to pair every row with each vintage) #

If you need to retain all variables from Have and attach each vintage year to every row of the ID, use this simplified code:

proc sort data=Have;
    by ID;
run;

data Have3;
    set Have;
    by ID;
    do Vintage = 2017 to 2020;
        output; /* Output the current row with each vintage year */
    end;
run;

Both solutions will produce the exact structure you showed in your expected Have3 dataset (or extended with your original variables if needed).

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