BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mariko5797
Pyrite | Level 9

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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.

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

2 REPLIES 2
yabwon
Onyx | Level 15

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.

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



mariko5797
Pyrite | Level 9

This is amazing! Thank you!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 11839 views
  • 0 likes
  • 2 in conversation