retain or multiple by variables?

[jamesmartinn]

Hi!

I have a situation where the data look like the following:

Student      Teacher     Grade
John               1              15
John               2              14
John               3              14
John               4              14
Sara               3              12
Sara               4              12
Matt               1              19
Jane               4              12
Jane               1              12

So here I have a dataset of Students who took a test with different teachers. My aim is to extract the highest grade achieved for each student, and the accompanying teacher. In the dataset, It's possible to have ties on the highest grades. In this case, I'd just like to make a new variable called 'flag' and give it a value of 'T'. For these students the Grade and the Teacher is irrelevant - I'm only interested in knowing that there was a tie, if the tie is the highest grade.

So for example, my output dataset (based on the above) would look like the following.

Student      Teacher     Grade    Flag
John               1              15       .
Sara               4              12       T
Matt               1              19       .
Jane               1              12       T

I've been playing around with retain statements and have looked into using multiple by variables (i.e, first.student, first.teacher) and I think retain is the way to go. I can't exactly shown what I've attempted as the code is located on the unix. Could anyone offer some tips for writing code to perform this task?

[Janus]

You could do this w/ prog sql and probably in fewer steps, but this will work:

data test;
input
Student $      Teacher     Grade;
datalines;
John               1              15
John               2              14
John               3              14
John               4              14
Sara               3              12
Sara               4              12
Matt               1              19
Jane               4              12
Jane               1              12
run;

proc sort data=test out=test2; by student descending grade; run;

data test3 test4; set test2; by student;
if first.student then do; output test3; end;
else do; output test4; end;
run;

proc sort data=test3; by student grade; run;
proc sort data=test4; by student grade; run;

data test5;
merge test3 (in=a) test4 (in=b);
by student grade;
if a;
if a and b then flag='T'; else flag=' ';
run;

[jamesmartinn]

Sorry for the delay in replying. Thank you so much, this worked great.

Cheers,