creating variables to capture high and low scores using first. and last. in SAS

Question

Consider the following example where scores of players are listed

data scores;
input player$ score;
cards;
    A   22
    A   26
    A   38
    B   22
    B   58
    B   60
;
run;

Basically, I want to create two variables called "highscore" and "lowscore" that capture the highest and lowest score for each player. E.g high score for player A would be 38 and low score for player B is 22.

I tried the following code

data highlow;
set scores;
lowscore=last.score;
highscore=first.score;
run;

It creates two variables but the output is not what I had intended. Instead, it lists 0's for each observation.

how can I create these two variables using the first. and last. commands in SAS

Answer 1

You need to use a by statement for SAS to create first. and last. variables. But they do NOT take on values of the by variable, instead they are just boolean flags to indicate if you are on the first (or the last) observation of this particular value of the BY variable.

If you just want to find the minimum and maximum and your values are already sorted then it is pretty simple.

data want ;
  set scores;
  by player score ;
  if first.player then lowscore=score ;
  retain lowscore ;
  if last.player ;
  highscore=score ;
  keep player lowscore highscore ;
run;

Note that you need to RETAIN the lowscore variable so that the value set on the first observation is not cleared when the data step moves to the next observation. You can avoid the need for RETAIN by using a DO loop around the SET statement.

data want ;
  do until (last.player);
    set scores;
    by player score ;
    if first.player then lowscore=score ;
    highscore=score ;
  end;
  keep player lowscore highscore ;
run;

If the data is sorted by player, but not by player and score then you will need to add more logic to find the min and max.

data want ;
  do until (last.player);
    set scores;
    by player ;
    lowscore=min(lowscore,score);
    highscore=max(highscore,score);
  end;
  keep player lowscore highscore ;
run;

If you want to keep all of the original observations also then add another DO loop to re-read the data and output the detail rows.

data want ;
  do until (last.player);
    set scores;
    by player ;
    lowscore=min(lowscore,score);
    highscore=max(highscore,score);
  end;
  do until (last.player);
    set scores;
    by player ;
    output;
  end;
run;

Answer 2

Approach by proc sql.

Keep all records:

proc sql;
   select *, min(score) as lowscore,max(score) as highscore from scores group by player;
quit;

Keep low and high scores:

 proc sql;
       select player, min(score) as lowscore,max(score) as highscore from scores group by player;
 quit;

Answer 3

first. and last. just stores true or false that is value of 1 or 0 that is to say whether a particular value is first.variable or last.variable. you can use first. and last. logic of true and false and then assign the values. you have to do this logic to assign the value if first.player then lowscore =score; see the below link for more explanation.

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=n01a08zkzy5igbn173zjz82zsi1s.htm&docsetVersion=9.4&locale=en Below code will achieve what you want to do

data scores;
input player$ score;
cards;
A   22
A   26
A   38
B   22
B   58
B   60
;
run;

proc sort data = scores;
by player score ;
run;

data scores1;
set scores;
by player score ;
retain lowscore;
if first.player then lowscore =score;
if last.player then highscore = score;
if last.player then output;
drop score;
run;

data scores_final;
merge  scores scores1;
by player;
run;

Answer 4

You need to sort the data set by player first. Then when you set it, you should add by player. Your code was missing the"By player;" line.