Calculate exact Clopper-Pearson binomial 95% CI

[mariko5797]

I want to find the exact binomial 95% CI for each symptom (SYMP) within each cohort and dose. The end result will be something along the lines of 

Symptom	Cohort A			Cohort B			Cohort C
	n	%	95% CI	n	%	95% CI	n	%	95% CI
redness	x	x	(x.x, x.x)	x	x	(x.x, x.x)	x	x	(x.x, x.x)
tender	x	x	(x.x, x.x)	x	x	(x.x, x.x)	x	x	(x.x, x.x)
pain	x	x	(x.x, x.x)	x	x	(x.x, x.x)	x	x	(x.x, x.x)
…	x	x	(x.x, x.x)	x	x	(x.x, x.x)	x	x	(x.x, x.x)

I have gotten this far:

/*Cohort A (N=75), Cohort B(N=84), Cohort C(N=61)*/
data have;
	input cohort $ dose $ symp $ cnt pct;
	cards;
		A	01	redness	10	13.3
		A	01	tender	3	4
		A	01	pain	8	10.7
		A	02	redness	9	12
		A	02	tender	6	8
		A	02	pain	4	5.3
		A	03	redness	6	8
		A	03	pain	12	16
		B	01	tender	21	25
		B	01	pain	13	15.5
		B	02	redness	16	19
		B	02	tender	14	16.7
		B	02	pain	8	9.5
		B	03	redness 7	8.3
		B	03	tender	12	14.3
		C	01	redness	17	27.9
		C	02	redness	12	19.7
		C	02	tender	6	9.8
		C	03	redness	8	13.1
		C	03	tender	9	14.8
		C	03	pain	1	1.7
	;
run;

I attempted to use the following code:

proc freq data = have (where= (cohort = "A" dose = "01"));
 weight cnt;
 tables symp / nocum binomial;
 exact binomial;
 ods output Binomial = have_ci;
run;

My initial idea was to make a macro to run through each cohort and dose combination. However, this method does not give me a 95% CI for all different symptoms (only gives first listed).

Is there an efficient way to get to my goal? Or will I need to run through each individual combination of cohort, dose, and symptom within PROC FREQ?

The data I used to get to "have" looks like:

/*participants without a symptom (occurence = 0 or missing) were removed*/
data orig;
	input id $ cohort $ dose $ symp $ day occurence;
	cards;
		MD001	A	01	redness	1	1
		MD001	A	02	redness	2	1
		MD002	A	01	redness	1	1
		MD002	A	01	tender	1	1
		MD002	A	02	redness	2	1
		MD002	A	03	redness	3	1
		MD003	B	01	tender	1	1
		MD003	B	02	redness	2	1
		MD003	B	02	tender	2	1
		MD003	B	03	redness	3	1
		MD004	A	01	tender	1	1
		MD004	A	02	redness	2	1
		MD005	C	01	tender	1	1
		MD005	C	01	pain	1	1
		MD005	C	03	redness	3	1
	;
run;

count(distinct ID) was used to obtain counts (CNT) seen in the "have" dataset.

NOTE: There are more symptoms and cohorts than listed. There are technically less participants than I used in my example, but I didn't feel like re-writing the counts and percentages. Clopper-Pearson 95% CI is specified in the analysis plan. 

[yabwon · Accepted solution]

Do I understand correctly that you need something like this:

data have;
	input cohort $ dose $ symp $ cnt pct;
	cards;
		A	01	redness	10	13.3
		A	01	tender	3	4
		A	01	pain	8	10.7
		A	02	redness	9	12
		A	02	tender	6	8
		A	02	pain	4	5.3
		A	03	redness	6	8
		A	03	pain	12	16
		B	01	tender	21	25
		B	01	pain	13	15.5
		B	02	redness	16	19
		B	02	tender	14	16.7
		B	02	pain	8	9.5
		B	03	redness 7	8.3
		B	03	tender	12	14.3
		C	01	redness	17	27.9
		C	02	redness	12	19.7
		C	02	tender	6	9.8
		C	03	redness	8	13.1
		C	03	tender	9	14.8
		C	03	pain	1	1.7
	;
run;

proc sort data = have out=have;
  by cohort dose symp;
run;

%macro loop(list, ds=have);

%local n;
%let n = %sysfunc(countw(&list.));

%do i = 1 % to &N.;
  /* to avoid "BINOMIAL LEVEL='xxxxx' is not found for variable YYYY." errors */
  proc sql noprint;
    create table INPUTTABLE as
    select a.* 
    from
      &ds. as a
      inner join
      ( select distinct cohort, dose 
        from &ds. 
        where symp = "%scan(&list.,&i.)"
      ) as b 
      on a.cohort = b.cohort and a.dose = b.dose
    order by
      cohort, dose
    ;
  quit;
  
  ods select none;
  proc freq data = INPUTTABLE;
   by cohort dose;
   weight cnt;
   tables symp / nocum binomial(OUTLEVEL LEVEL="%scan(&list.,&i.)") noprint;
   exact binomial;
   ods output Binomial = have_ci_&i.(where=(Name1 in: ("XU", "XL")));
  run;
  ods select all;


  proc print data=have_ci_&i.;
  run;
%end;

%mend;

proc sql;
  select distinct symp 
  into :list separated by " " 
  from have(where=(cohort="A" and dose="01"))
  ;
quit; 

options mprint;
%loop(&list.)

data all_have_ci;
  set have_ci_:;
  
run;

B.

[mariko5797]

This is amazing! Thank you!