Solved
New Contributor
Posts: 4

# Find the maximum number within a subset of a column

[ Edited ]

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.

Thank you for your time!

Accepted Solutions
Solution
‎09-20-2016 06:38 AM
SAS Super FREQ
Posts: 9,365

## 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

All Replies
Trusted Advisor
Posts: 1,837

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

Posted in reply to NielsKraaer

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;

Solution
‎09-20-2016 06:38 AM
SAS Super FREQ
Posts: 9,365

## 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

Valued Guide
Posts: 765

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

[ Edited ]
Posted in reply to NielsKraaer

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;

New Contributor
Posts: 4

## 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!

Valued Guide
Posts: 765

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

Posted in reply to NielsKraaer

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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