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.
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.
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.
This is amazing! Thank you!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.