topic Re: Finding the max value of a variable for a given student ID (several rows per student) in SAS Programming

Finding the max value of a variable for a given student ID (several rows per student)

ethiveos — Tue, 11 Apr 2017 18:29:14 GMT

Hi,

New SAS user here!

I'm trying to create a new variable based on the max of another variable associated with unique student IDs. Here's an example of the dataset. I'm trying to create the variable titled "MaxTerm". How would I specify that I'm looking for the max term for each student ID?

Thanks the help!

Student_ID	Term	MaxTerm
000001	1	4
000001	2	4
000001	3	4
000001	4	4
000002	1	5
000002	2	5
000002	3	5
000002	4	5
000002	5	5

Re: Finding the max value of a variable for a given student ID (several rows per student)

Kurt_Bremser — Tue, 11 Apr 2017 19:10:24 GMT

proc sql;
create table want as
select *, max(term) as max_term
from have
group by student_id
;
quit;

Re: Finding the max value of a variable for a given student ID (several rows per student)

ballardw — Tue, 11 Apr 2017 19:26:21 GMT

Is your Maxterm variable numeric or character? If character you my get unexpected results.

Example:

data junk;
   x='15';   output;
   x='5';    output;
run;

proc sql;
   select max(x)
   from junk;
quit;

will return '5' as the maximum value because when comparing characters '5' is greater than '1'. The second character only comes into consideration when the first character is the same.