topic Re: Macro for proc glimmix and export p-values and OR(95% CI) into Excel file in SAS Programming

Macro for proc glimmix and export p-values and OR(95% CI) into Excel file

Denali — Tue, 05 Jan 2021 15:46:33 GMT

Hi,

I have approxmiately 60 variables (20 continuous variables and 40 categorical variables) need to be processed by the univariate analysis of general linear mixed model. Below is an example of my code:

proc glimmix data = All plots =(all) noclprint method=quad;
class ID Ebus;
model diagnostic (event=' Yes') = Ebus
/ solution dist=binary
oddsratio ;
random intercept /subject = ID;
run;

Could anyone please help me to create a macro code for all the continuous variable and categorical variables and export all the odds ratios (95% condidence intervals) and p-values from the 60 univariate models into an Excel file?

Thank you very much!

Re: Macro for proc glimmix and export p-values and OR(95% CI) into Excel file

PaigeMiller — Tue, 05 Jan 2021 15:55:41 GMT

It's not clear whether your 60 variables are all x-variables, or some of them are y-variables, you really need to make it more clear what variables are x and what variables are y.

But your solution is probably this:

https://blogs.sas.com/content/iml/2017/02/13/run-1000-regressions.html

Re: Macro for proc glimmix and export p-values and OR(95% CI) into Excel file

Denali — Tue, 05 Jan 2021 16:04:44 GMT

The 60 variables are predictors (X). Binary outcome (Y) remains the same.

Re: Macro for proc glimmix and export p-values and OR(95% CI) into Excel file

PaigeMiller — Tue, 05 Jan 2021 16:08:24 GMT

The BY group method in the link I gave is the way to go. No macros needed.

Re: Macro for proc glimmix and export p-values and OR(95% CI) into Excel file

Denali — Tue, 05 Jan 2021 18:11:45 GMT

I am hoping to export the variable name, OR (95% CI) and p values into an Excel file as below.

I am still not sure how to do it based on the link you pasted.

Variables	OR (95% CI)	P-values
ALC_Cutoff (High vs Low)	1.17 (0.35, 3.93)
ANC_Cutoff (High vs Low)	0.00 (0.00, I)
Active_Therapy (Yes vs No)	0.32 (0.09, 1.12)
Age_C (Yes vs No)	0.84 (0.29, 2.49)
Blood_Type_A (Yes vs No)	1.43 (0.47, 4.31)
CRP_Cutoff (High vs Low)	0.29 (0.07, 1.13)
Comorbid_CV (Yes vs No)	1.08 (0.37, 3.18)
Comorbid_Immune (Yes vs No)	0.36 (0.09, 1.43)
Comorbid_Pulm (Yes vs No)	0.17 (0.04, 0.69)

Re: Macro for proc glimmix and export p-values and OR(95% CI) into Excel file

PaigeMiller — Tue, 05 Jan 2021 18:30:00 GMT

How are you obtaining the OR and 95% CI now? Show the code.

Re: Macro for proc glimmix and export p-values and OR(95% CI) into Excel file

Denali — Tue, 05 Jan 2021 18:59:33 GMT

Hi,

Below is the code that I used for exporting the OR (95%CI) from the univariate logistic regression model. I would like to change the code to Glimmix model this time and output the p-values along with the OR (95%CI), but I am not sure how to change the code below:

/*Categorical Variable*/
%macro IP(x=,order=);
ods select cloddswald;
ods output cloddswald=odds_&order.;

proc logistic data = CP_new;
class &x.;
model improve = &x. /rl;
run;
%mend IP;

%IP(x=Age_C, order=01);
%IP(x=Gender, order=02);
%IP(x=Comorbid_CV, order=03);
%IP(x=Comorbid_Pulm, order=04);

data toprint;
length effect $ 40;
set odds_01 - odds_4;
order = _n_;
comparison = "(" || strip(substr(effect, anyspace(effect))) || ")";
name = scan(effect, 1);
run;

proc contents data = cp2 out = temp;
run;

proc sort data = temp;
by name;
run;

proc sort data = toprint;
by name;
run;

data toprint;
merge toprint(in = in1) temp;

by name;

if in1;

vout = catx(" ", strip(coalescec(label, name)), comparison);

orout = strip(put(OddsRatioEst, 12.2)) || " (" ||

strip(put(LowerCL, 12.2)) || ", " ||

strip(put(UpperCL, 12.2)) || ")";

label vout = "Variable"

orout = "Odds Ratio";
run;

proc print data = toprint noobs label;
var vout orout;
run;

data print;
set toprint;
keep vout orout;
run;

proc export data=print
dbms=xlsx outfile="H:\Project\SAS Program\OR_Categorical_12-18-2020.xlsx"
replace;
run;

Currently, I am trying to modify the macro to Glimmix as below:

/*Categorical Variable*/

%macro IP(x=,order=);
ods select cloddswald;
ods output cloddswald=odds_&order.;

proc glimmix data = All plots =(all) noclprint method=quad;
class ID &x;
model robot_diagnostic (event=' Yes') = &x
/ solution dist=binary
oddsratio ;
random intercept /subject = ID;
run;

%mend IP;

%IP(x=Age_C, order=01);

Re: Macro for proc glimmix and export p-values and OR(95% CI) into Excel file

PaigeMiller — Tue, 05 Jan 2021 19:05:57 GMT

Well, honestly, I don't see the need for a macro when the link I provided shows how to do it without macros, using a BY statement. You take your (I assume) working PROC LOGISTIC or PROC GLIMMIX code and use it in place of the PROC REG in the link. If you get hung up, be specific, explain where you are having a problem and show your code for the BY statement solution.