## Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

Solved
Super Contributor
Posts: 345

# Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

When I calculate 95%Ci of RR, for each group,

the first one, with RR=1, there is no need to calculate 95%CI. Would you please let me know how to let SAS do this, put  a marker as "-" for the first one of each subgroup with RR=1?

Accepted Solutions
Solution
‎01-20-2016 12:00 PM
Super User
Posts: 23,776

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

Trace your IF conditions, they're not paired properly. But your code where you combined UCL and LCL to missing in one step should have worked, as long as the next

``````IF first.group then do;
*set to missing;
end;
else do;
*calculate values;
end;:``````

All Replies
Super User
Posts: 13,583

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

Please post some example data and the code you are currently using as there is not enough context to address your question.

Also, it looks like you may be asking about changing the appearance of your current output. If so post the current output and the desired output.

Super Contributor
Posts: 345

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

[ Edited ]
``````data have;
input mygroup \$ subgroup \$ b d;
cards;

;

run;

data want;
set have;
by mygroup NOTSORTED;
*tell sas to keep the denom across the rows;
retain denom;

*create the denominator value;
if first.mygroup then do;
rr=1;
denom=d/b;
end;

*calculate relative risk;
else rr=(d/b)/denom;

*calculate percent and 95%CI;
pct=d/b*100;
J=SQRT((((B-D)/D)/B)+(((B-D)/D)/B));
Lcl=EXP(log(rr)-(1.96*(J)));
Ucl=EXP(log(rr)+(1.96*(J)));
/*proc sort; by mygroup;*/

*format variables for appearance;
format  pct 8.1 rr 8.2 J 8.2 LCl 8.2 Ucl 8.2;

run;

*print results;
proc print data=want;
run;

data report;
set want;
pct=round(pct, 0.1);
rr=round(rr, 0.01);
Lcl=round(Lcl,0.01);
UcL=round(Ucl,0.01);

run;

proc export data=report

outfile = "C:\table"
dbms = XLSX replace;
sheet = "1.1";

run;``````
Super Contributor
Posts: 345

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

desired output attached

i need sas not to calculate the first one of each subgroup when their RR was set to be 1,

I want mark that as "--"

if the table attached does not show that, I am sorry excel works weird sometimes

but the table attached at least shows what I need

Super User
Posts: 23,776

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

That means you'll have to convert the variable to text because -- is text and a variable can only be one type.

How is the first record being identified to create the denom variable in your current code. If you understand that you'll figure out how to set the values to missing.

Read up on BY group processing if you need to.
Super Contributor
Posts: 345

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

Do you mean to convert a numeric variable to a characteri variable?

Or you mean there are two options, either when you define this variable is missing, or you convert it so that SAS will consider it as missing?
I use the below code, it does not change. SAS continues to calculate the values of LCL and UCL, whether or not it is the first one

``````  if first.mygroup then do;
176      rr=1;
177      denom=d/b;
178      LcL=.;
179      Ucl=.;
180  end;``````
Super User
Posts: 23,776

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

Order of operations. You set it to missing here but later in the code your calculating.
Super Contributor
Posts: 345

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

``````if first.mygroup then do;
rr=1;
denom=d/b;

end;

*calculate relative risk;
else rr=(d/b)/denom;

*calculate percent and 95%CI;
pct=d/b*100;
J=SQRT((((B-D)/D)/B)+(((B-D)/D)/B));

if first.mygroup then do;

LcL=.;
Ucl=.;
else
Lcl=EXP(log(rr)-(1.96*(J)));
Ucl=EXP(log(rr)+(1.96*(J))``````

There is something wrong with it after I change the order. Would you please help to fix it? Thanks

Super User
Posts: 23,776

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

Do's need a matching end.

And if you're doing multiple statements in an if statement they begin in a DO block. Fix your Do's/End's

Super Contributor
Posts: 345

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

``````if first.mygroup then do;

LcL=.;

end;
if first.mygroup then do;

UcL=.;

end;
else
Lcl=EXP(log(rr)-(1.96*(J)));

Ucl=EXP(log(rr)+(1.96*(J)));
/*proc sort; by mygroup;*/``````

Whatever I have modified, the LCL of the first one of the group is missing, as I want; but the UcL is still be caculated

Super User
Posts: 23,776

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

Is your log clean, ie error free? Make sure it is first.

Super Contributor
Posts: 345

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

I doubled checked, and there is no error.

`````` if first.mygroup then do;
199
200      LcL=.;
201      end;
202
203  if first.mygroup then do;
204      Ucl=.;
205      end;
206      else

Option II;
I put them together,
if first.mygroup then do;
lcl=.
ucl=.

both have same results, that is, ucl is still calculated and lcl is missing for the first one``````
Super User
Posts: 23,776

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

Post your full code and log

Super Contributor
Posts: 345

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

[ Edited ]

data want;
set have;
by mygroup NOTSORTED;
*tell sas to keep the denom across the rows;
retain denom;

*create the denominator value;
if first.mygroup then do;
rr=1;
denom=d/b;

end;

*calculate relative risk;
else rr=(d/b)/denom;

*calculate percent and 95%CI;
pct=d/b*100;
J=SQRT((((B-D)/D)/B)+(((B-D)/D)/B));

if first.mygroup then do;

LcL=.;
end;

if first.mygroup then do;
Ucl=.;
end;
else
Lcl=EXP(log(rr)-(1.96*(J)));
Ucl=EXP(log(rr)+(1.96*(J)));
/*proc sort; by mygroup;*/

*format variables for appearance;
format pct 8.1 rr 8.2 J 8.2 LCl 8.2 Ucl 8.2;

run;

*print results;
proc print data=want;
run;

proc report data=want;

column mygroup subgroup b d rr pct lcl ucl;
define mygroup/display;
define subgroup/display;
define b/display;
define f/display;
define rr/display;
define pct/display;
define Lcl/style={tagattr='format:0####.00'};
define Ucl/style={tagattr='format:0####.00'};
run;quit;

ods tagsets.excelxp close;

*************************

51 data want;
52 set have;
53 by mygroup NOTSORTED;
54 *tell sas to keep the denom across the rows;
55 retain denom;
56
57 *create the denominator value;
58 if first.mygroup then do;
59 rr=1;
60 denom=d/b;
61
62 end;
63
64 *calculate relative risk;
65 else rr=(d/b)/denom;
66
67 *calculate percent and 95%CI;
68 pct=d/b*100;
69 J=SQRT((((B-D)/D)/B)+(((B-D)/D)/B));
70
71
72 if first.mygroup then do;
73
74 LcL=.;
75 end;
76
77 if first.mygroup then do;
78 Ucl=.;
79 end;
80 else
81 Lcl=EXP(log(rr)-(1.96*(J)));
82 Ucl=EXP(log(rr)+(1.96*(J)));
83 /*proc sort; by mygroup;*/
84
85 *format variables for appearance;
86 format pct 8.1 rr 8.2 J 8.2 LCl 8.2 Ucl 8.2;
87
88 run;

NOTE: There were 24 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 24 observations and 10 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

89
90 *print results;
91 proc print data=want;
NOTE: Writing HTML Body file: sashtml.htm
92 run;

NOTE: There were 24 observations read from the data set WORK.WANT.
NOTE: PROCEDURE PRINT used (Total process time):
real time 1.46 seconds
cpu time 0.18 seconds

93
94
95
NOTE: Writing TAGSETS.EXCELXP Body file: c:\tabledesired.xml
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013). Add
97
98 proc report data=want;
99
100 column mygroup subgroup b d rr pct lcl ucl;
101 define mygroup/display;
102 define subgroup/display;
103 define b/display;
104 define f/display;
105 define rr/display;
106 define pct/display;
107 define Lcl/style={tagattr='format:0####.00'};
108 define Ucl/style={tagattr='format:0####.00'};
109 run;

WARNING: f is not in the report definition.
NOTE: There were 24 observations read from the data set WORK.WANT.
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.07 seconds
cpu time 0.07 seconds

109! quit;
110
111
112 ods tagsets.excelxp close;

Solution
‎01-20-2016 12:00 PM
Super User
Posts: 23,776

## Re: Calculate 95%CI of RR, how not to calculate the 95%Ci of the first one, with reference RR=1,

Trace your IF conditions, they're not paired properly. But your code where you combined UCL and LCL to missing in one step should have worked, as long as the next

``````IF first.group then do;
*set to missing;
end;
else do;
*calculate values;
end;:``````

🔒 This topic is solved and locked.