topic Re: PRoc freq - sum by group in SAS Programming

PRoc freq - sum by group

monday89 — Wed, 15 Apr 2020 14:51:05 GMT

Hi i have the following table:

group	reasons	total_events
1	a	5
1	b	1
1	c	3
1	d	2
2	a	4
2	b	3
2	c	11
2	d	4

What I want is the frequency total by group as denominator; something like this:

group	reasons	total_events	total_denominator
1	a	5	11
1	b	1	11
1	c	3	11
1	d	2	11
2	a	4	22
2	b	3	22
2	c	11	22
2	d	4	22

I tried the following but i get 100% for all.

ods output onewayfreqs=test_owf;/*one way frequency*/
proc freq data=o.have;
table reasons;
by group;
run;
ods listing;

Re: PRoc freq - sum by group

novinosrin — Wed, 15 Apr 2020 15:21:28 GMT

Hi @monday89 Switch to Proc SQL and take advantage of the autoremerge

proc sql;
create table want as
select *,sum(total_events) as total_denominator
from have
group by group;
quit;

/*Or*/

data want;
 do _n_=1 by 1 until(last.group);
  set have;
  by group;
  total_denominator=sum(total_denominator,total_events);
 end;
 do _n_=1 to _n_;
  set have;
  output;
 end;
run;

Re: PRoc freq - sum by group

PaigeMiller — Wed, 15 Apr 2020 15:01:46 GMT

proc freq data=o.have;
table reasons;
by group;
weight total_events;
run;

This gets you close. It does not produce the column named TOTAL_DENOMINATOR, but that's easy to add via a data step.

Re: PRoc freq - sum by group

Kurt_Bremser — Wed, 15 Apr 2020 15:42:23 GMT

@novinosrin's SQL solution is what came to my mind first. Recommended.