如何在SAS中使用first.和last.语句正确生成玩家高低分变量？

Got it, let's figure out why your highscore/lowscore variables are all showing 0 and fix it using SAS's first. and last. group processing. The main issue here is almost certainly that you didn't properly sort your data by player (so first./last. can't identify player groups) or you're not retaining the running high/low values across observations for each player.

Here's a step-by-step solution:

1. First, Sort Your Data by Player #

first. and last. only work correctly when your data is sorted by the grouping variable (in this case, your player identifier). Use PROC SORT to organize your data:

proc sort data=your_raw_score_data;
    by player_id; * Replace with your actual player column name (e.g., player_name);
run;

2. Use a DATA Step with by, retain, and first./last. #

We'll use retain to keep track of the high and low scores across rows in the same player group, first.player_id to initialize the values for each new player, and update the scores as we iterate through each observation.

Option 1: Keep All Observations (Each Row Shows Player's Current High/Low) #

This adds the highscore/lowscore to every row for the player, so you can see how their scores evolved:

data player_scores_with_running_stats;
    set your_sorted_score_data;
    by player_id;
    
    * Retain the high/low values so they don't reset to missing after each row;
    retain highscore lowscore;
    
    * Initialize high/low to the first score of the player;
    if first.player_id then do;
        highscore = score;
        lowscore = score;
    end;
    * For subsequent scores, update high/low if current score is higher/lower;
    else do;
        if score > highscore then highscore = score;
        if score < lowscore then lowscore = score;
    end;
run;

Option 2: One Row Per Player (Only Final High/Low) #

If you just want a summary with each player's highest and lowest score, add an if last.player_id then output; statement to only write the final row of each player group:

data player_score_summary;
    set your_sorted_score_data;
    by player_id;
    
    retain highscore lowscore;
    
    if first.player_id then do;
        highscore = score;
        lowscore = score;
    end;
    else do;
        if score > highscore then highscore = score;
        if score < lowscore then lowscore = score;
    end;
    
    * Only output when we reach the last observation of the player group;
    if last.player_id then output;
    
    * Optional: Drop the individual score column if you don't need it;
    * drop score;
run;

Key Explanations #

• by player_id: Tells SAS to group observations by player, which automatically creates the first.player_id and last.player_id flags (1 for the first/last row of the group, 0 otherwise).
• retain: By default, SAS resets variables to missing after each iteration of the DATA step. retain keeps the highscore/lowscore values from the previous row, so we can track the running high/low for each player.
• Initialization with first.player_id: For the first score of a player, their highest and lowest score is that first value—so we set both variables to the current score here.
• Updating scores: For every subsequent score, we compare it to the retained high/low and update if needed.

Example Output #

If your raw data looks like this:

Option 1 will produce:

Option 2 will produce:

Why Your Original Code Showed 0s #

Most likely, you either:

• Forgot to sort your data by player, so first./last. didn't group observations correctly, or
• Didn't use retain, so your highscore/lowscore variables were reset to 0 (or missing) every row, leading to the final value being 0.

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