max sum per group

[monday89]

Hi I have the following table:

group	event	Frequency	Percent	CumulativeFrequency
a	fever	2	20.00	2
a	cold	3	30.00	5
a	cough	1	10.00	6
a	flu	4	40.00	10
b	headache	2	20.00	2
b	fatigue	2	20.00	4
b	fever	1	10.00	5
b	cold	1	10.00	6

What I would like to do is that: take max cumulativefrequency of the "group" and create a static column. Something like this

group	event	Frequency	Percent	CumulativeFrequency	Total
a	fever	2	20.00	2	10
a	cold	3	30.00	5	10
a	cough	1	10.00	6	10
a	flu	4	40.00	10	10
b	headache	2	20.00	2	6
b	fatigue	2	20.00	4	6
b	fever	1	10.00	5	6
b	cold	1	10.00	6	6

i tried to proc sql;

create table test c as

select group, max(CumulativeFrequency) as total

from have;

group by group;

quit;

it works but i have to rejoin test and have. is there a way without creating additional dataset? 

[novinosrin]

Try chaging your SELECT  clause to 

select *, max(CumulativeFrequency) as total

That will auto-remerge as long as the select clause has a variabe read from input table that is not part of a by group

[unison]

Change:

proc sql;

create table test c as

select group, max(CumulativeFrequency) as total

from have;

group by group;

quit;

To:

proc sql;
create table test as
select *, max(CumulativeFrequency) as total
from have
group by group;
quit;