BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bal23
Lapis Lazuli | Level 10

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;:

 

View solution in original post

14 REPLIES 14
ballardw
Super User

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.

Bal23
Lapis Lazuli | Level 10
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;
Bal23
Lapis Lazuli | Level 10

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

Reeza
Super User
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.
Bal23
Lapis Lazuli | Level 10

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;
Reeza
Super User
Order of operations. You set it to missing here but later in the code your calculating.
Bal23
Lapis Lazuli | Level 10
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

Reeza
Super User

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

Bal23
Lapis Lazuli | Level 10
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?

Reeza
Super User

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

Bal23
Lapis Lazuli | Level 10

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
Bal23
Lapis Lazuli | Level 10

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;

Reeza
Super User

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;:

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 2289 views
  • 1 like
  • 3 in conversation