A Macro To Automate Importing Multiple Excel Files Containing Multiple Worksheets In Which Common Variables Have Different Length Or Types

Note: Sorry that I forgot to mention another limitation.  You may slightly change input function for your specific purpose. If you don’t want to print invalid data error message, you can add double question marks in your input function. Also, the code cannot be used in situations where variables which have date, time and dollar format have mixed data types (See the following example) in some Excel worksheets. I may modify the code when I have time.

BTW, you need to list all character variables for macro variable named “charvar” so the program can detect mixed type variables for each worksheet and convert them to numeric variables.

date	a
12/5/2009	—
—	—
—	—
—	—
—	—
—	—
—	$20
—	$5
	$6
12/8/2012	$189
11/19/2007	$19.40
8/25/1999	$1,568,941

A Macro to Build Both Bootstrap Percentile and BCA Confidence Intervals of Indirect Effect with Binary Outcomes

Mediation addresses how an independent variable causes a change in a dependent variable. When a third variable is intermediate in the relationship between two variables, it is called a mediator. In general, a mediator is a variable that accounts for all or part of the relation between a predictor and an outcome. The figure below displays the path diagram for a single mediator model.

Three regression equations are used to investigate mediation,

Model 1: Y = i1 + cX + e1                     (1)

Model 2: Y = i2 +c’X + bM + e2            (2)

Model 3: M = i3 + aX +e3                      (3)

Where c is a total effect which represents the total relationship between independent variable and dependent variable without consideration of other variables, c’ is a direct effect which accounts for the relationship between X and Y adjusting for the effect of M, a mediator, b is the parameter relating the mediator to the dependent variable adjusted for the effect of the independent variable and a represents the relationship between X and M.

The product of a and b, ab, is the mediated effect which is also known as the indirect effect. The indirect effect is also equal to the difference between c and c’, c-c’. As a result, the total effect is the sum of a direct effect and an indirect effect.

These two approaches to quantify the mediated effects are equivalent when the dependent variable is continuous and ordinary regression is used. This is not the case in logistic regression. The residual variance in logistic regression is set to equal π*π/3  so the scale of latent variable Y is not the same across models. Therefore, the estimated c-c’ and ab are not equal and can be quite different.

The logistic regression coefficients need to be standardized so the scale is equivalent across equations. Standardized coefficients can be computed by dividing each coefficient by the square root of the variance of the predicted logit from which those coefficients arose.

An INDIRECT SPSS macro for mediation with categorical outcomes developed by Preacher and Hayes is available online. However, the authors didn’t standardize coefficients when logistic regression is used.

This SAS macro that I created can investigate mediation with a binary outcome for a single mediator model. As indirect effects do not meet normal assumption for statistical analysis, bootstrap method is used to estimate the significance of indirect effects. Both bootstrap percentile intervals and bias corrected accelerated 95% confidence intervals are computed.

Reference:

Preacher, K. J., & Hayes, A. F. (2008). Asymptotic and resampling strategies for assessing and comparing indirect effects in multiple mediator models. Behavior Research Methods, 40, 879-891.

MacKinnon, D.P. (2008). Introduction to statistical mediation analysis. Mahwah, NJ: Erlbaum.

A Macro to Automate Importing Multiple CSV Files