topic Re: How to calculate relative risk and confidence interval in SAS Programming

How to calculate relative risk and confidence interval

sri1 — Sun, 24 Sep 2023 14:47:44 GMT

Hi all,

I have the following data

term	event	count	drug
Anthralgia	yes	1	A
Anthralgia	no	5	A
Nausea	yes	1	A
Nausea	no	5	A
Anorexia	yes	1	A
Anorexia	no	5	A
Cough	yes	1	B
Cough	no	1	B
Malaise	yes	1	B
Malaise	no	1	B

I tried the following code to get relative risk and confidence interval

ods output relativerisks=rel;

proc freq data=test order=data;

weight count;

tables drug*event / relrisk ;

by term ;

run;

ods output close;

I am getting the following message in log and ods output is not being created

No statistics are computed for DRUG* EVENT because DRUG has fewer than 2 nonmissing levels.

May I know how to get the code work or any other proc to derive relative risk & CL for above data.

Appreciate response!

Regards

Sri

Re: How to calculate relative risk and confidence interval

PaigeMiller — Sun, 24 Sep 2023 15:03:43 GMT

From now on, please present data as working SAS data step code, like this (and not in any other format)

data test;
infile cards delimiter='|';
input term :$10.  event :$3.	count drug $;
cards;
Anthralgia|yes|1|A
Anthralgia|no|5|A
Nausea|yes|1|A
Nausea|no|5|A
Anorexia|yes|1|A
Anorexia|no|5|A
Cough|yes|1|B
Cough|no|1|B
Malaise|yes|1|B
Malaise|no|1|B
;

From now on, please present the ENTIRE log, not just one line, and place the log in a code box by clicking on the </> icon:

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 NOTE: ODS statements in the SAS Studio environment may disable some output features.
 69         
 70         data test;
 71         infile cards delimiter='|';
 72         input term :$10.  event :$3.count drug $;
 73         cards;
 
 NOTE: The data set WORK.TEST has 10 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              674.65k
       OS Memory           21412.00k
       Timestamp           09/24/2023 02:58:41 PM
       Step Count                        104  Switch Count  2
       Page Faults                       0
       Page Reclaims                     128
       Page Swaps                        0
       Voluntary Context Switches        13
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 84         ;
 85         
 86         ods output relativerisks=rel;
 87         
 88         proc freq data=test order=data;
 89                 weight count;
 90                 tables drug*event / relrisk ;
 91                by   term ;
 92                 run;
 
 NOTE: No statistics are computed for drug * event because drug has fewer than 2 nonmissing levels.
 NOTE: The above message was for the following BY group:
       term=Anthralgia
 ERROR: Data set WORK.TEST is not sorted in ascending sequence. The current BY group has term = Nausea and the next BY group has 
        term = Anorexia.
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: Output 'relativerisks' was not created.  Make sure that the output object name, label, or path is spelled correctly.  
          Also, verify that the appropriate procedure options are used to produce the requested output object.  For example, verify 
          that the NOPRINT option is not used.
 NOTE: There were 5 observations read from the data set WORK.TEST.
 NOTE: PROCEDURE FREQ used (Total process time):
       real time           0.01 seconds
       user cpu time       0.02 seconds
       system cpu time     0.01 seconds
       memory              2233.78k
       OS Memory           22444.00k
       Timestamp           09/24/2023 02:58:41 PM
       Step Count                        105  Switch Count  4
       Page Faults                       0
       Page Reclaims                     308
       Page Swaps                        0
       Voluntary Context Switches        22
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           536
       
 93         
 94         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 104

Once we see the ENTIRE log as shown above, it is clear you did not present to us the major ERROR, which is highlighted in red.

By specifying this in the code:

       by   term ;

the error is obvious now ... SAS can't do this by TERM because the data is not sorted by TERM. In addition, as soon as you limit the analysis to be done BY TERM, there is indeed only a single level DRUG in each value of TERM.

Re: How to calculate relative risk and confidence interval

sri1 — Sun, 24 Sep 2023 15:12:05 GMT

I have provided sample data , the data was sorted before deriving relative risk and I have provided the major note in the log which says

No statistics are computed for drug * event because drug has fewer than 2 nonmissing levels.

Re: How to calculate relative risk and confidence interval

PaigeMiller — Sun, 24 Sep 2023 15:20:23 GMT

@sri1 wrote:

I have provided sample data , the data was sorted before deriving relative risk and I have provided the major note in the log which says
No statistics are computed for drug * event because drug has fewer than 2 nonmissing levels.

Sample data needs to be provided in the format I have shown, not in the format you used. Even if you sorted the data (which you did not state in your original message), the explanation of that error is stated in my message.

Re: How to calculate relative risk and confidence interval

sri1 — Sun, 24 Sep 2023 15:32:08 GMT

I hope this group to provide a solution rather than commenting on how to present data .I tried to explain sorting error wasn't an issue to not get the code work as sorting was done in real time.

Re: How to calculate relative risk and confidence interval

PaigeMiller — Sun, 24 Sep 2023 15:44:07 GMT

And I explained to you why you are getting the error. I said

as soon as you limit the analysis to be done BY TERM, there is indeed only a single level DRUG in each value of TERM.

Thus, the error message from SAS is correct, and so no relative risk can be computed.

By the way, we're trying to help you, but you need to help us too. From now, please present data in the best possible format for us. Thanks.

Re: How to calculate relative risk and confidence interval

Ksharp — Mon, 25 Sep 2023 11:36:01 GMT

You have to remove "by term ;" of PROC FREQ.
Since it is only calculated RELRISK for 2x2 table,
but for term='Cough' would not form 2x2 table.

Re: How to calculate relative risk and confidence interval

sri1 — Mon, 25 Sep 2023 15:57:24 GMT

Thanks for your response. I need the output "by term". Any alternative other than proc freq?

Re: How to calculate relative risk and confidence interval

sri1 — Mon, 25 Sep 2023 15:58:44 GMT

Sure! Thanks for your response.

Re: How to calculate relative risk and confidence interval

PaigeMiller — Mon, 25 Sep 2023 16:30:30 GMT

@sri1 wrote:

Thanks for your response. I need the output "by term". Any alternative other than proc freq?

With by term; in the code, you cannot compute relative risk, that is what SAS is telling you. There is only one level of DRUG when you limit the analysis with the BY statement. In this case, relative risk is not mathematically defined, and so I doubt any PROC in SAS can do this.

Re: How to calculate relative risk and confidence interval

Ksharp — Tue, 26 Sep 2023 11:29:17 GMT

You gotta know
for 2x2 table :

a1 a2
b1 b2

for the first column
RELRISK= (a1/(a1+a2)) / (b1/(b1+b2));

for the second column
RELRISK= (a2/(a1+a2)) / (b2/(b1+b2)) ;

if you don't have b1 and b2 (a.k.a b1=0,b2=0), how could you get RELRISK?

Re: How to calculate relative risk and confidence interval

sri1 — Fri, 29 Sep 2023 19:48:49 GMT

Thanks for your response.For the above data as relrisk can't be computed , can you please let me know how can I define in a macro not to run above procedure as there is no enough data and don't get warning in log that "Output 'relativerisks' was not created" and end the macro.

Re: How to calculate relative risk and confidence interval

Ksharp — Sat, 30 Sep 2023 04:08:45 GMT

data test;
infile cards delimiter='|';
input term :$10.  event :$3.	count drug $;
cards;
Anthralgia|yes|1|A
Anthralgia|no|5|A
Nausea|yes|1|A
Nausea|no|5|A
Anorexia|yes|1|A
Anorexia|no|5|A
Cough|yes|1|B
Cough|no|1|B
Malaise|yes|1|A
Malaise|no|1|A
;


%macro relrisk(dsn=);
proc sql;
create table want as
select * from &dsn 
 group by term
  having count(distinct catx(' ',event,drug))=4 and sum(count=0)=0 ;
quit;

%let dsid=%sysfunc(open(want));
%let nlobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid.));
%if &nlobs. %then %do;
  ods output relativerisks=rel;
proc freq data=want order=data;
        weight count;
        tables drug*event / relrisk ;
       by   term ;
        run;
%end;
%mend;

%relrisk(dsn=test)