Fluorite | Level 6

## Find the maximum number within a subset of a column

Hi!

I have the following dataset:

ID      spellnr      duration

101        1                1

101        1                2

101        1                3

101        2                1

101        2                2

101        3                1

101        3                2

101        3                3

102        1                1

102        1                2

102        2                1

I want the following dataset:

ID      spellnr      duration      maxspellnr

101        1                1                   3

101        1                2                   3

101        1                3                   3

101        2                1                   3

101        2                2                   3

101        3                1                   3

101        3                2                   3

101        3                3                   3

102        1                1                   2

102        1                2                   2

102        2                1                   2

That is, I want to know what the maximum number of spells an individual is subject to.

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Super FREQ

## Re: Find the maximum number within a subset of a column

Hi:

As an alternative, here's an example that does not use SQL, but instead counts on the fact that if you sorted by ID and descending SPELLNR, then the max of SPELLNR would be on the first row for ID. Then all you need is a RETAIN to retain the max value for all the rows for the same ID.

cynthia

5 REPLIES 5
Garnet | Level 18

## Re: Find the maximum number within a subset of a column

You need 2 steps:

1) compute the max spellnr per ID:

proc sql;

create table tmp as select ID, max(spellnr) as maxspellnr

from  have group by ID;

quit;

2) join the max value to the original data:

proc sql;

create table want as

select a.* , b.maxspellnr

from have as a

left join tmp as b

on a.ID = b.ID;

quit;

SAS Super FREQ

## Re: Find the maximum number within a subset of a column

Hi:

As an alternative, here's an example that does not use SQL, but instead counts on the fact that if you sorted by ID and descending SPELLNR, then the max of SPELLNR would be on the first row for ID. Then all you need is a RETAIN to retain the max value for all the rows for the same ID.

cynthia

Rhodochrosite | Level 12

## Re: Find the maximum number within a subset of a column

Hi. You can use PROC SQL and get your new data set in one step ....

proc sql;
create table new as
select *, max(spellnr) as maxspellnr from x
group id;
quit;

Fluorite | Level 6

## Re: Find the maximum number within a subset of a column

I went with Cynthias solution, but I am annoyed at how hard it is to find the maximum number in a column. Thank you for your posts!

Rhodochrosite | Level 12

## Re: Find the maximum number within a subset of a column

Hi, is this much code really that annoying ...

proc sql;
create table new as
select *, max(spellnr) as maxspellnr from x
group id;
quit;

Finding the maximum number requires little SAS code (PROC MEANS or SUMMARY will do that). It's the remerging with the original observations that requires the extra programing. PROC SQL finds the maximum value within groups and also does that remerging with the least amnount of SAS code.

Here are a couple other ideas ...

* read the data set twice ... add maximum value in the second pass through the data;

* almost as short as the PROC SQL solution shown above;

data xx;

do until (last.id);
set x (in=pass1) x;
by id;
if pass1 then maxspellnr = max(of maxspellnr, spellnr);
else output;
end;

run;

or ...

* use PROC SUMMARY to find the maximum value;

proc summary data=x nway;
var spellnr;
class id;
output max=maxspellnr out=maxsp(index=(id) keep=id maxspellnr);
run;

* add the maximum value to the data set;

data xx;
set x;
set maxsp key=id/unique;
run;

Discussion stats
• 5 replies
• 4374 views
• 2 likes
• 4 in conversation