proc freq vs. sql

monday89 — Sat, 08 Feb 2020 18:35:22 GMT

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?

Re: proc freq vs. sql

Astounding — Sat, 08 Feb 2020 20:11:36 GMT

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;

Re: proc freq vs. sql

ed_sas_member — Sun, 09 Feb 2020 09:36:04 GMT

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:

topic Re: proc freq vs. sql in SAS Programming

proc freq vs. sql

Re: proc freq vs. sql

Re: proc freq vs. sql