topic Re: macro error in New SAS User

macro error

charbel — Wed, 26 Sep 2018 16:11:09 GMT

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.

Re: macro error

pink_poodle — Wed, 26 Sep 2018 16:27:25 GMT

You seem to be calling it EFFECTSIZE_TABLE_IGPP everywhere except one occasion.

Re: macro error

charbel — Wed, 26 Sep 2018 16:32:17 GMT

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.

Re: macro error

Kurt_Bremser — Wed, 26 Sep 2018 16:32:36 GMT

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

Re: macro error

charbel — Wed, 26 Sep 2018 16:44:13 GMT

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

Re: macro error

Kurt_Bremser — Wed, 26 Sep 2018 16:49:11 GMT

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.

Re: macro error

charbel — Wed, 26 Sep 2018 17:04:27 GMT

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?

Re: macro error

Kurt_Bremser — Wed, 26 Sep 2018 17:29:47 GMT

Run your macro with

options mlogic;

to see how the %if conditions resolve and if the wanted sql is ever executed.

Re: macro error

charbel — Wed, 26 Sep 2018 18:19:10 GMT

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.

Re: macro error

Kurt_Bremser — Wed, 26 Sep 2018 18:59:36 GMT

Well, so you know what's preventing the creation of output. Either change values or the texts in the conditions.

Re: macro error

charbel — Wed, 26 Sep 2018 21:02:20 GMT

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?

Re: macro error

pink_poodle — Wed, 26 Sep 2018 21:15:36 GMT

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.

Re: macro error

charbel — Wed, 26 Sep 2018 21:46:50 GMT

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?

Re: macro error

ballardw — Wed, 26 Sep 2018 22:36:11 GMT

@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.

Re: macro error

charbel — Wed, 26 Sep 2018 22:48:24 GMT

Hi, sorry but I think you're responding to an earlier version of the macro which I modified. It shouldn't include this macro:

%effect_size( data=che_lim, idvar=unique_id, groupvar=tx_area, respvars=hc_cat BSI TRGI CESD, cl=0.95)

I don't have access to the macro at this time but will come back to this as soon as I can tomorrow morning.

I'm very grateful for everyone who responded so far.

Re: macro error

Kurt_Bremser — Thu, 27 Sep 2018 06:01:49 GMT

@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.

Re: macro error

charbel — Thu, 27 Sep 2018 16:56:12 GMT

thanks again everyone about input yesterday. As you pointed out I'm very new to using SAS macros.

The current macro is:

%macro effect_size( data,idvar,design,groupvar, timevar, timeval1, timeval2,respvars,cl=0.95);
%if &groupvar^= tx_area1 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 path='c:\temp' (url=none) file='survey_data_eff_size.html';



%if &groupvar^= tx_area1 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;


options symbolgen;


** Ind. Group Pre-Post design: (sd calculated from change scores) *;
** Ind. Group Pre-Post design: (sd calculated from change scores) *;

%effect_size(data=che_lim, idvar=unique_id, design=1, groupvar=tx_area1, timevar=rounds, timeval1=1, timeval2=2, respvars=hc_cat , cl=0.95)

I changed the ODS statement to:

ods html path='c:\temp' (url=none) file='survey_data_eff_size.html'; as suggested on SAS support

When I run the macro I get:

NOTE: Writing HTML Body file: survey_data_eff_size.html
SYMBOLGEN: Macro variable GROUPVAR resolves to tx_area1
SYMBOLGEN: Macro variable TIMEVAR resolves to rounds

when I use a local drive such as:

ods html file='I:\RTs_and_Projects\Integrated_Surveillance_Systems_RT\Interamerican_Development_Bank\PUBLICATIONS\DID in health expenditure\survey_data_eff_size.html';

I also get:

NOTE: Writing HTML Body file:
I:\RTs_and_Projects\Integrated_Surveillance_Systems_RT\Interamerican_Development_Bank\PUBLICATIO
NS\DID in health expenditure\survey_data_eff_size.html
SYMBOLGEN: Macro variable GROUPVAR resolves to tx_area1
SYMBOLGEN: Macro variable TIMEVAR resolves to rounds

I can see that you're all pointing to what groupvar and timevar are resolving to as the issue but I honestly can't understand or guess how to fix that.

Re: macro error

Kurt_Bremser — Thu, 27 Sep 2018 17:55:47 GMT

You supply groupvar as tx_area1, but the macro tests for the contents of groupvar to not be tx_area1. This condition is obviously false, so nothing of the code inside the %do-%end blocks is created by the macro, and nothing happens. Only the ODS statements, which are outside the conditional block, are created by the macro and executed.

Similar happens for timevar.