Find the maximum number within a subset of a column

[NielsKraaer]

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!

[Shmuel]

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;

[Cynthia_sas · Accepted solution]

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

[MikeZdeb]

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;

[NielsKraaer]

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!

[MikeZdeb]

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;