proc freq vs. sql

[monday89]

Following table

I want to calculate category2 per category

group	ID	category2	binary
placebo	1	ABC-123	0
placebo	3	ABC-123	0
placebo	1	DEF-134	1
placebo	3	DEF-134	1
placebo	1	OPS-129	1
placebo	3	OPS-129	0
placebo	1	TFD-157	0
placebo	3	TFD-157	1
treatment	2	ABC-123	1
treatment	4	ABC-123	1
treatment	2	DEF-134	1
treatment	4	DEF-134	0
treatment	2	OPS-129	0
treatment	4	OPS-129	1
treatment	2	TFD-157	1
treatment	4	TFD-157	1

to something like this:

group	category	binary	denominator
placebo	ABC-123	0	2
placebo	DEF-134	2	2
placebo	OPS-129	1	2
placebo	TFD-157	1	2
treatment	ABC-123	2	2
treatment	DEF-134	1	2
treatment	OPS-129	1	2
treatment	TFD-157	2	2

the denominator is treatment group. 

i tried the following but i can only get the numerator and denominator separately

proc sql;
create table yes_group as
select distinct group, category, count(binary) as binary_yes
from have
where binary= 1
group by group, category;
quit;

and i have to repeat for denominator. 

is there a bette way from proc freq? 

[Astounding]

I think this is what you are after:

proc summary data=have  nway;
   var binary;
   class group category2;
   output out=want (drop=_type_ _freq_) sum=numerator n=denominator;
run;

[ed_sas_member]

Hi @monday89 

You can also do this with Proc Sql:

proc sql;
	select group, category2,
		   sum(binary) as binary,
		   count(binary) as denominator
	from have
	group by group, category2;
quit;

Output:

Capture d’écran 2020-02-09 à 10.34.09.png