topic Re: Find the maximum number within a subset of a column in SAS Programming

Find the maximum number within a subset of a column

NielsKraaer — Fri, 16 Sep 2016 14:27:33 GMT

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!

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

Shmuel — Fri, 16 Sep 2016 18:56:35 GMT

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;

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

Cynthia_sas — Sat, 17 Sep 2016 14:27:20 GMT

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

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

MikeZdeb — Sat, 17 Sep 2016 19:24:17 GMT

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;

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

NielsKraaer — Tue, 20 Sep 2016 11:11:17 GMT

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!

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

MikeZdeb — Wed, 21 Sep 2016 23:18:39 GMT

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;