proc freq owf

[monday89]

I am having trouble calculating n and total event for multiple group

Here is the table: 

treatment_group	AgeGroup	patientID	group1	group2
treatment	adult	1	ONE	headache
treatment	adult	1	ONE	fever
treatment	adult	1	ONE	cough
treatment	child	2	ONE	cough
treatment	child	3	TWO	heart disease
treatment	child	3	TWO	smoker
treatment	adult	4	TWO	heart disease
placebo	adult	5	ONE	headache
placebo	adult	5	ONE	cough
placebo	child	6	ONE	pneumonia
placebo	child	6	TWO	heart disease
placebo	child	7	TWO	heart disease
placebo	child	7	TWO	smoker

I want to get total number per group 2 per group 1 per treatment_group for total patients for each age group. Something like this:

Total_event will be denominator for group1 based on per treatment_group and agegroup 

treatment_group	AgeGroup	group1	group2	event	total_event
placebo	adult	ONE	cough	1	2
placebo	adult	ONE	headache	1	2
placebo	child	ONE	pneumonia	1	1
placebo	child	TWO	heart disease	2	3
placebo	child	TWO	smoker	1	3
treatment	adult	ONE	cough	1	3
treatment	adult	ONE	fever	1	3
treatment	adult	ONE	headache	1	3
treatment	adult	TWO	heart disease	1	1
treatment	child	ONE	cough	1	1
treatment	child	TWO	heart disease	1	2
treatment	child	TWO	smoker	1	2

i tried but i haven't been successful for age group and treatment at the same time

proc freq data=test ;
table group1*group2/missing;
by treatment_group ;
run;

[Jagadishkatam]

Could you please try the below code

proc sort data=test;
by treatment_group AgeGroup;
run;

proc freq data=test ;
table group1*group2/missing;
by treatment_group AgeGroup;
run;

[unison]

data have;
	infile datalines dsd;
	input treatment_group :$15. agegroup $ patientid group1 $ group2 :$20.;
	datalines;
treatment,adult,1,ONE,headache
treatment,adult,1,ONE,fever
treatment,adult,1,ONE,cough
treatment,child,2,ONE,cough
treatment,child,3,TWO,heart disease
treatment,child,3,TWO,smoker
treatment,adult,4,TWO,heart disease
placebo,adult,5,ONE,headache
placebo,adult,5,ONE,cough
placebo,child,6,ONE,pneumonia
placebo,child,6,TWO,heart disease
placebo,child,7,TWO,heart disease
placebo,child,7,TWO,smoker
;
run;

proc sort data=have;
	by treatment_group agegroup;
run;

proc freq data=have noprint;
	by treatment_group agegroup;
	tables group1*group2 / out=have2(drop=percent rename=count=event);
run;

proc sql;
	create table want as
	select 
		*
		, sum(event) as total_event
	from have2
	group by 1,2,3
;quit;

[ed_sas_member]

Hi @monday89 

Another possibility, using only Proc Sql with an inner join to merge two queries:

proc sql;
	create table want as
	select a.*, b.total_event
	from 
		(select treatment_group, AgeGroup, group1, group2,
				count(group1) as event
		 from have
		 group by treatment_group, AgeGroup,group1,group2) as a
		 
		inner join
		
		(select treatment_group, AgeGroup, group1, group2,
				count(group1) as total_event
		 from have
		 group by treatment_group, AgeGroup,group1) as b
		
		on a.treatment_group=b.treatment_group and a.AgeGroup=b.AgeGroup and
		   a.group1=b.group1 and a.group2=b.group2;
run;