Hi everyone,
I am trying to adapt a macro that calculates effect size and its CL to my data set. This is the original paper: https://analytics.ncsu.edu/sesug/2012/SD-06.pdf
The edited macro for my specific dataset is the following:
%macro effect_size( data,idvar,design,groupvar, timevar, timeval1, timeval2,respvars,cl=0.95);
%if &groupvar^= tx_area and &timevar^= rounds %then %do;
proc sql noprint; select distinct(&groupvar) into :grp1-:grp2 from &data;
quit;
** to calculate effect size of 2-group pre-post design***;
** make two separate data set for group 1 and group 2****;
data data_&grp1(keep=&idvar &timevar &respvars) data_&grp2(keep=&idvar &timevar &respvars);
set &data;
if &groupvar=&grp1 then output data_&grp1;
if &groupvar=&grp2 then output data_&grp2;
run;
* Noow calculate effect size for both groups*********************;
* using either design 1(independent) or design 2 paired desig)***;
%eff_size_paired(data=data_&grp1,idvar=&idvar,design=&design, timevar=&timevar,timeval1=&timeval1,
timeval2=&timeval2, respvars=&respvars,cl=&cl,outdata=effectsize_table&grp1)
%eff_size_paired(data=data_&grp2,design=&design,idvar=&idvar, timevar=&timevar,timeval1=&timeval1,
timeval2=&timeval2, respvars=&respvars,cl=&cl,outdata=effectsize_table&grp2)
* combine two tables to get effect size***; * IGPP design is square root of sum of variance ****;
proc sql;
create table effectsize_table_IGPP as select trim(a.Response) ||"&grp1"||"-"||trim(a.Response)||"&grp2"
as Response, a.n as n_&grp1, a.mean1 as M1_&grp1 format 8.2,
a.sd1 as SD1_&grp1 format=8.2, a.mean2 as M2_&grp1 format=8.2,
a.sd2 as SD2_&grp1 format=8.2,
b.n as n_&grp2, b.mean1 as M1_&grp2 format=8.2,
b.sd1 as SD1_&grp2 format=8.2, b.mean2 as M2_&grp2 format=8.2,
b.sd2 as SD2_&grp2 format=8.2,
a.effect_size - b.effect_size as eff_size,
calculated eff_size-PROBIT(.50+&cl/2)*(sqrt(a.se_d**2+b.se_d**2)) as CI_LOW,
calculated eff_size+PROBIT(.50+&cl/2)* (sqrt(a.se_d**2+b.se_d**2)) as CI_UP
from work.effectsize_table&grp1 as a, work.effectsize_table&grp2 as b
where a.Response=b.response;
quit;
%goto printresult;
%end;
** calculate macro variable for confidence level***;
%printresult:
** Print results***;
** Print results***;
ods html file="survey_data_eff_size.xls";
%if &timevar= %then %do;
title "Table 1: Effect Size (Cohen'd) and its Confidence Interval for Independnt Group Post-Test Design(IG).";
proc sql nonumber;
select trim(measure) ||"&grp2"||"-"||trim(measure)||"&grp1" as Response,
n1 label="Group&grp1 Sample Size (n1)",mean1 label="Group&grp1 mean", sd1 label="Group&grp1 SD",
n2 label="Group&grp2 Sample Size (n2)", mean2 label="Group&grp2 Mean", sd2 label="Group&grp2 SD",
eff_size format=8.3 label="Effect Size (d)", "("||trim(left(put(ci_low,8.3)))||","||trim(left(put(ci_up,8.3)))||")" label="&conf % Conf. Interval"
from work.effectsize_table_IG;
quit;
title;
%end;
%if &groupvar^= tx_area and &timevar^= rounds %then %do;
title "Table 1: Effect Size (Cohen'd) and its Confidence Interval for Independent Group Pretest Posttest Design(IGPP)";
proc sql nonumber; select * from work.effectsize_table_IGPP;
quit;
title;
%end;
ods html close;
%mend effect_size;
the macro invoking line is:
%effect_size( data=che_lim, idvar=unique_id, groupvar=tx_area, respvars=hc_cat BSI TRGI CESD, cl=0.95)
I have been getting the following error:
ERROR: Insufficient authorization to access C:\Program
Files\SASHome\SASFoundation\9.4\effectsize_table_IGPP.xls.
ERROR: No body file. HTML output will not be created.
and
ERROR: File WORK.EFFECTSIZE_TABLE_IG.DATA does not exist.
Can someone let me know what is it that I'm doing wrong?
I am using SAS 9.4 on windows.
You seem to be calling it EFFECTSIZE_TABLE_IGPP everywhere except one occasion.
if by one occasion you mean this "ods html file="survey_data_eff_size.xls";" I tried "effectsize_table_IGPP" but still got the same error.
The original macro has it as "survey_data_eff_size.xls" only at that instance.
Use an absolute path for your HTML file:
ods html file="c:\users\username\desktop\survey_data_eff_size.html";
(insert your username)
And you create
effectsize_table_IGPP
but try to use
work.effectsize_table_IG
Just did, which seems to solve the first error. Now the macro log ends with
NOTE: Writing HTML Body file: c:\users\charbel\desktop\survey_data_eff_size.html
but still get
WARNING: Apparent symbolic reference CONF not resolved.
ERROR: File WORK.EFFECTSIZE_TABLE_IG.DATA does not exist.
P.S: all 3 instances of effectsize_table_IG are now replaced with effectsize_table_IGPP
You use &conf, but never create it.
And the fact that SAS still complains about WORK.EFFECTSIZE_TABLE_IG means that you did NOT correct that. Add the PP there.
My bad.
I had a mistake in the macro. The following section shouldn't have been there:
%if &timevar= %then %do;
title "Table 1: Effect Size (Cohen'd) and its Confidence Interval for Independnt Group Post-Test Design(IG).";
proc sql nonumber;
select trim(measure) ||"&grp2"||"-"||trim(measure)||"&grp1" as Response,
n1 label="Group&grp1 Sample Size (n1)",mean1 label="Group&grp1 mean", sd1 label="Group&grp1 SD",
n2 label="Group&grp2 Sample Size (n2)", mean2 label="Group&grp2 Mean", sd2 label="Group&grp2 SD",
eff_size format=8.3 label="Effect Size (d)", "("||trim(left(put(ci_low,8.3)))||","||trim(left(put(ci_up,8.3)))||")" label="&conf % Conf. Interval"
from work.effectsize_table_IGPP;
quit;
title;
%end;
I removed it as it belongs to another design.
now I get no errors or warnings but I do get "NOTE: Writing HTML Body file: c:\users\charbel\desktop\survey_data_eff_size.html" when I call the macro and nothing else. Ain't I supposed to get the results' table?
Run your macro with
options mlogic;
to see how the %if conditions resolve and if the wanted sql is ever executed.
did that as well, and the problem seems to be at the intervention and time specification:
MLOGIC(EFFECT_SIZE): Beginning execution.
MLOGIC(EFFECT_SIZE): Parameter DATA has value che_lim
MLOGIC(EFFECT_SIZE): Parameter IDVAR has value unique_id
MLOGIC(EFFECT_SIZE): Parameter GROUPVAR has value tx_area1
MLOGIC(EFFECT_SIZE): Parameter RESPVARS has value hc_cat BSI TRGI CESD
MLOGIC(EFFECT_SIZE): Parameter CL has value 0.95
MLOGIC(EFFECT_SIZE): Parameter DESIGN has value
MLOGIC(EFFECT_SIZE): Parameter TIMEVAR has value
MLOGIC(EFFECT_SIZE): Parameter TIMEVAL1 has value
MLOGIC(EFFECT_SIZE): Parameter TIMEVAL2 has value
MLOGIC(EFFECT_SIZE): %IF condition &groupvar^= tx_area1 and &timevar^= rounds is FALSE
NOTE: Writing HTML Body file: c:\users\charbel\desktop\survey_data_eff_size.html
MLOGIC(EFFECT_SIZE): %IF condition &groupvar^= tx_area1 and &timevar^= rounds is FALSE
MLOGIC(EFFECT_SIZE): Ending execution.
Well, so you know what's preventing the creation of output. Either change values or the texts in the conditions.
sorry Kurt but I don't think that would be the right approach. the values for these two variables (tx_area1 and rounds) can only be 1 & 2 but at the same time I know that this is where the %if statement is going wrong. Any other ideas?
options symbolgen;
will show the values of macro variables in the log. Then you may check why the condition is false.
By the way, the link to the reference paper is not working.
sorry about the link, I've attached the paper.
with your suggestion, I get:
SYMBOLGEN: Macro variable GROUPVAR resolves to tx_area1
SYMBOLGEN: Macro variable TIMEVAR resolves to rounds
NOTE: Writing HTML Body file: c:\users\charbel\desktop\survey_data_eff_size.html
SYMBOLGEN: Macro variable GROUPVAR resolves to tx_area1
SYMBOLGEN: Macro variable TIMEVAR resolves to rounds
Does that tell you anything?
@charbel wrote:
sorry Kurt but I don't think that would be the right approach. the values for these two variables (tx_area1 and rounds) can only be 1 & 2
The MLOGIC log clearly tells you otherwise, one of the macrovariables is empty, and the other contains the text tx_area1.
You may have a very typical misunderstandng (for people new to macro programming) here. The macro preprocessor cannot work with values from data step variables, or reference them. To it, everything is just plain text.
@charbel wrote:
did that as well, and the problem seems to be at the intervention and time specification:
MLOGIC(EFFECT_SIZE): Beginning execution.
MLOGIC(EFFECT_SIZE): Parameter DATA has value che_lim
MLOGIC(EFFECT_SIZE): Parameter IDVAR has value unique_id
MLOGIC(EFFECT_SIZE): Parameter GROUPVAR has value tx_area1
MLOGIC(EFFECT_SIZE): Parameter RESPVARS has value hc_cat BSI TRGI CESD
MLOGIC(EFFECT_SIZE): Parameter CL has value 0.95
MLOGIC(EFFECT_SIZE): Parameter DESIGN has value
MLOGIC(EFFECT_SIZE): Parameter TIMEVAR has value
MLOGIC(EFFECT_SIZE): Parameter TIMEVAL1 has value
MLOGIC(EFFECT_SIZE): Parameter TIMEVAL2 has value
MLOGIC(EFFECT_SIZE): %IF condition &groupvar^= tx_area1 and &timevar^= rounds is FALSE
NOTE: Writing HTML Body file: c:\users\charbel\desktop\survey_data_eff_size.html
MLOGIC(EFFECT_SIZE): %IF condition &groupvar^= tx_area1 and &timevar^= rounds is FALSE
MLOGIC(EFFECT_SIZE): Ending execution.
This line:
MLOGIC(EFFECT_SIZE): Parameter TIMEVAR has value
indicates that the macro variable TIMEVAR has no assigned value.
Since it has no assigned value when it gets to this
%IF &groupvar^= tx_area1 and &timevar^= rounds
The macro processor resolves the macro variables to
%IF tx_area1 ^= tx_area1 and ^= rounds
which has a bit of a logic problem.
Your main macro call statement:
%effect_size( data=che_lim, idvar=unique_id, groupvar=tx_area, respvars=hc_cat BSI TRGI CESD, cl=0.95)
Does not have a timevar or the timeval parameters in the macro definition. So when you pass them to the inside macro calls they don't exist.
I suspect that instead of
%if &groupvar^= tx_area and &timevar^= rounds %then %do;
That you intended
%else %if &groupvar^= tx_area and &timevar^= rounds %then %do;
since the previous %if hopefully handled the blank timevar case.
Note that this is lying to the software:
ods html file="survey_data_eff_size.xls";
And always provide a full path to the file as relative paths without a drive or mount point are problematic as to where the output ends up and in server environments often attempt to write to locations you don't have permissions for such as ERROR: Insufficient authorization to access C:\Program
Files\SASHome\SASFoundation\9.4\effectsize_table_IGPP.xls.
Many IT departments seem to be making the Program Files and subordinate folders read only to users. The program was attempting to write to the path RELATIVE to where SAS was executing.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.