How to export all the OR(95%.CI) into an Excel file from a macro?

[Denali]

Hi,

I have about 40 variables for the univariate logistic regression model so I wrote up a simple macro for it. But I don't know how to export all the OR(95% C.I.) into an Excel file. Could you please help me with it?

Below is my SAS macro:

%macro IP(x=);

proc logistic data = CP1;
class &x;
model improve = &x /rl;
run;

ods output close;
ods graphics on;

%mend IP;

%IP(x=Age_C);
%IP(x=Gender);
%IP(x=Disease_Type);
%IP(x=Disease_Status);
%IP(x=Active_Therapy);
%IP(x=Blood_Type_A);

Thank you very much in advance. 

[ballardw]

Do you want to create 40 different Excel files or have all of the output in one Excel file?

Do want only the OR or all of the other proc logistic output?

Do you get any warnings about the "ods output close;" statement?

Why do you have "ods graphics on" in the macro? You execute that 40 different times from the way your code is structured without turning the option off.

[Denali]

All the 40 ORs and 95% CIs in a single tab in an Excel file.

Could you help me to modify my code? Thank you.

[ballardw]

Perhaps

%macro IP(x=);
ods select cloddswald;
proc logistic data = CP1;
   class &x.;
   model improve = &x. /rl;
run;
%mend IP;

ods excel file=<your path and filename go here>
  options=(sheet_interval='NONE');

%IP(x=Age_C);
%IP(x=Gender);

ods excel close;

The ODS SELECT lets you pick which of the output tables you want from a procedure.

You can determine which tables are generated by running the code with

ODS TRACE ON;

<procedure code>

ODS TRACE OFF;

When use that option after the code in the Log you will see something like:

Output Added:
-------------
Name:       ModelInfo
Label:      Model Information
Template:   Stat.Logistic.ModelInfo
Path:       Logistic.ModelInfo
-------------

Output Added:
-------------
Name:       NObs
Label:      Observations Summary
Template:   Stat.Logistic.NObs
Path:       Logistic.NObs
-------------

Output Added:
-------------
Name:       ResponseProfile
Label:      Response Profile
Template:   Stat.Logistic.ResponseProfile
Path:       Logistic.ResponseProfile
-------------
<other tables deleted for example>
Output Added:
-------------
Name:       CLoddsWald
Label:      95% Clodds=Wald
Template:   Stat.Logistic.CloddsWald
Path:       Logistic.CLoddsWald
-------------

These sections each starting with Output Added: are the tables in your output. You want the value(s) that appears after Name: in the ODS SELECT statement to limit the output.

Since you want a single table then the option Sheet_interval='NONE' is used on the ODS Excel to have all of the data restricted to a single tab.

[Denali]

Hi @ballardw ,

Thanks for the code. I successfully exported ORs and 95% CIs into the Excel file, but it's not in the format that I desire. 

I have exoported all the HRs and 95%CIs from Cox Proportional Hazard Ratio models into an Excel file (see below photo), and the Hazard Ratios, Lower CI and Upper CI were exported into 3 columns. I am hoping to export ORs and 95% C.I. into 3 columns like this example.

If we can merge all 3 columns into this format OR(95% C.I.) = 0.89 (0.29-2.74), that would be even better. Thank you!

[ballardw]

---

@Denali wrote:

Hi @ballardw ,

Thanks for the code. I successfully exported ORs and 95% CIs into the Excel file, but it's not in the format that I desire. 

I have exoported all the HRs and 95%CIs from Cox Proportional Hazard Ratio models into an Excel file (see below photo), and the Hazard Ratios, Lower CI and Upper CI were exported into 3 columns. I am hoping to export ORs and 95% C.I. into 3 columns like this example.

If we can merge all 3 columns into this format OR(95% C.I.) = 0.89 (0.29-2.74), that would be even better. Thank you!

---

Add ODS output to write a separate data set containing each of the models output. Append all the data sets together, Print the output.

Something like

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

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


%IP(x=Age_C);
%IP(x=Gender);

data toprint;
   length effect $ 40;
   set odds_: ;
run;

ods excel file=<your path and filename go here>
  options=(sheet_interval='NONE');

proc print data=toprint;
run;

ods excel close;

The data step Toprint uses a list option on the set statement. Set odds_: tells SAS to use all of the datasets whose names start with ODDS_. The order the sets are use is almost certainly going to be alphabetic. The length statement preceding the Set statement should be long enough to hold the longest expected effect text as the created data set will likely  have different lengths for that variable.

[Denali]

Hi @ballardw ,

The code worked, but the variables are ordered from A to Z. Is there a way that we can order them by the original order?

Also, I was running another set of macro for all the continuous variables (previous code was for categorical variables with class statement). When I exported the ORs and 95% CIs, why all the ORs and 95% CIs from the categorical variables were all included in the Excel file even I changed the name of the file? Is there a way that I can export all the continuous variables in a seperate Excel file?

Please see my code for continuous variables below:

%macro IP1(x=);
ods select cloddswald;
ods output cloddswald=odds_&x.;

proc logistic data = CP1;
model improve = &x. /rl;
run;
%mend IP1;

%IP1(x=Age);
%IP1(x=Pre_Score);

ods excel close;

data toprint1;
length effect $ 40;
set odds_: ;
run;

ods excel file="H:\Data\CI_Continuous.xlsx"
options(sheet_interval='NONE');

proc print data=toprint1;
run;

ods excel close;

Thank you!

[ballardw · Accepted solution]

---

@Denali wrote:

Hi @ballardw ,

The code worked, but the variables are ordered from A to Z. Is there a way that we can order them by the original order?

Also, I was running another set of macro for all the continuous variables (previous code was for categorical variables with class statement). When I exported the ORs and 95% CIs, why all the ORs and 95% CIs from the categorical variables were all included in the Excel file even I changed the name of the file? Is there a way that I can export all the continuous variables in a seperate Excel file?

Please see my code for continuous variables below:

%macro IP1(x=);
ods select cloddswald;
ods output cloddswald=odds_&x.;

proc logistic data = CP1;
model improve = &x. /rl;
run;
%mend IP1;

%IP1(x=Age);
%IP1(x=Pre_Score);

ods excel close;

data toprint1;
length effect $ 40;
set odds_: ;
run;

ods excel file="H:\Data\CI_Continuous.xlsx"
options(sheet_interval='NONE');

proc print data=toprint1;
run;

ods excel close;

Thank you!

---

Manually write out the names of the data sets on the set statement in the Toprint data step in the order you want them.

Or provide variable names that sort in the order you want the output.

Or instead of using the variable name to name the odds_ set provide an order macro variable so the Odds_ sets will sort as desired

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

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


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

data toprint;
set odds_01 - odds_02;
run;

If you use numbers as shown you cannot skip any unless you make some more sublists at which point you might as well go the "name all the sets in the order desired"