CODEWITHSUNDEEP
sas
N.M
N.M

Reputation: 685

finding a record with maximal value in SAS

How do I find a record of maximal value in SAS? I have a dataset of the following structure:

age name
12 joe
15 jane
3  bob
14 rick

I would like to find the value of 'name' for which 'age' is the maximal (in this example - 'jane').

I tried this:

 data max;
   set ages_data;
   if age > max_age then do;
       max_age = age;
   max_name = name;
   end;
   retain max_:;
   keep max_:;
 run;

which is based on what I found here: https://communities.sas.com/message/170554

but it didn't work for me... what am I doing wrong?

Thanks

Upvotes: 1

Views: 53

Answers (1)

Allan Bowe
Allan Bowe

Reputation: 12691

Your code is fine - but is outputting all records, I assume you expected just one? Try the following changes:

 data max;
   set ages_data end=lastobs; /* identify last record */
   if age > max_age then do;
      max_age = age;
      max_name = name;
   end;
   retain max_:;
   keep max_:;
   if lastobs then output; /* only output last record */
 run;

One drawback with this approach is that it will only output the FIRST value of name, for a given maximum age. There may be multiple values of name for that age. The following approach may be more robust for your purposes:

 proc sql;
 create table max as
    select name, age
    from ages_data
    where age= (select max(age) from ages_data);

In case it's useful - here are the datalines for testing:

data ages_data;
infile cards;
input age name $;
cards;
12 joe
15 jane
3  bob
14 rick
;run;

Upvotes: 3

Related Questions