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?
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;:
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.
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;
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
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;
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
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
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
Any advice?
Is your log clean, ie error free? Make sure it is first.
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
Post your full code and log
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;
ods tagsets.excelxp file='c:\tabledesired.xml' style=meadow;
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
96 ods tagsets.excelxp file='c:\tabledesired.xml' style=meadow;
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
options(doc='help') to the ods statement for more information.
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;
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;:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.