Replace values for each ID for a variable based on the greatest ones

[NewUsrStat]

Hi, 

suppose to have the following:

Subj   Age

a      30-40

a      30-40

a      50-60

a      50-60

b      30-40

b      30-40

b      30-40 

.................

Is there a way, for each Subj to replace for the age variable the values with the greatest ones? In other words, 30-40 for "a" should be replaced with 50-60. Thank you in advance.

[PeterClemmensen]

what possible values do you have for age?

[NewUsrStat]

I have a lot of ranges. I just have to keep the highest. Ranges are: 21-25, 26-30, 31-35, 36-40, 41-45, 46-50, 51-55, 56-60, 61-65 and 66-70.

[PeterClemmensen · Accepted solution]

Ok . Try this

data have;
input Subj $ Age $;
datalines;
a 30-40 
a 30-40 
a 50-60 
a 50-60 
b 30-40 
b 30-40 
b 30-40 
;

proc sql;
create table want as
   select Subj,
          (select max(Age) 
           from have 
           where a.Subj=Subj 
           group by Subj) as Age
   from have as a;
quit;

[PaigeMiller]

May I suggest that you use a numeric variable to represent age rather than a text string? The advantage (which may not make a difference in your data, I don't know) is that a text string such as 5-9 will seem to be higher (because that's how text is evaluated, alphabetically) than 21-25. And so if you had a 5-9 for a subject, you would possibly get the wrong answer. Similarly, if there are people in the 100-110 year age group, these will appear to be less than the 21-25 age group. Even if this doesn't apply to your data, it is still a good idea for you (and anyone else reading along) to make age a numeric variable.

This problem doesn't happen with numeric variables, since these will be evaluated numerically, either 5 or 9 is less than 25, and 100 is greater than 25. And so the code from @PeterClemmensen would also likely fail in this more general case.

I would do this:

data want;
    set have;
    age_n=input(scan(age,1,'-'),3.);
run;

and then use @PeterClemmensen 's code (or something very similar to it) to get the maximum of AGE_N.