topic macro - with automatic table creation (including P values) - please help =) in SAS Programming

macro - with automatic table creation (including P values) - please help =)

GabbyJ — Wed, 10 Jun 2020 21:16:06 GMT

Hi all,

Could you kindly help me?

Below is a macro with multiple inputs for predictors/outcomes that automatically make output tables.

However I cannot get it to display coefficients, P values, and CI's - could you please help me modify it to display these?

data xxx;
  input v1-v5 ind1 ind2;
  cards;
1 0 1 1 0 34 23
0 0 1 0 1 22 32
1 1 1 0 0 12 10
0 1 0 1 1 56 90
0 1 0 1 1 26 80
1 1 0 0 0 46 45
0 0 0 1 1 57 53
1 1 0 0 0 22 77
0 1 0 1 1 44 45
1 1 0 0 0 41 72
;
run;

%macro mylogita(indata, all_deps, indvars =, myout =_out );
  %let k=1;
  %let dep = %scan(&all_deps, &k);
  %do %while(&dep NE);
    title "The dependent variable is &dep";
    title2 "The independent variables are &indvars";
    proc logistic data=&indata des outest=est&k;
      model &dep = &indvars;
    run;
    %let k = %eval(&k + 1);
    %let dep = %scan(&all_deps, &k);
  %end;

  data &myout;
    set 
    %do i = 1 %to &k - 1;
      est&i
    %end; 
    ;
  run;
%mend;

*run the program;
%mylogita(xxx, v1 v2 v3, indvars = ind1 ind2, myout = myparms)

title;
proc print data = myparms;
  var _name_ intercept ind1 ind2;
run;

This is the output but without P values and CI's -- could you kindly help me figure out how to add those?

Obs _NAME_ Intercept ind1 ind2 _TYPE_123

v1	2.4570	-0.04282	-0.01709	PARMS
v2	0.3278	-0.09480	0.09078	PARMS
v3	33.3421	-0.50434	-0.40122	PARMS

Thank you 😃

Gabby

Re: macro - with automatic table creation (including P values) - please help =)

JeffMeyers — Wed, 10 Jun 2020 21:22:08 GMT

I would run ODS TRACE ON; prior to an example LOGISTIC procedure and note the parts of the output that you want to capture. Each segment will have a corresponding table name shown by ODS TRACE in the log. For example, the parameter estimates table that has the estimates and chi-square p-values is captured I believe in the ParameterEstimates table, so you could output it with the following before your LOGISTIC procedure:

ODS OUTPUT ParameterEstimates=_parm;

You can specify multiple tables in the same ODS OUTPUT statement.

After that it's just figuring out how to merge the tables you want together to get your final output table.

Re: macro - with automatic table creation (including P values) - please help =)

GabbyJ — Wed, 10 Jun 2020 22:07:46 GMT

Dear Jeff,

Thank you so much for your kind and quick reply. I am a complete newbie to SAS. I wrote the statement below, however, still no P values or CI's. Could you help me understand the next steps?

Thank you for your time,

Gabby

ods trace on;
ods output ParameterEstimates = test1;
proc logistic data = xxx descending;
  model v1 = ind1 ind2;
run;
ods trace off;

proc print data = test1;
run;

this is the output:

Obs Variable DF Estimate StdErr WaldChiSq ProbChiSq _ESTTYPE_123

Intercept	1	2.4570	2.3179	1.1237	0.2891	MLE
ind1	1	-0.0428	0.0524	0.6671	0.4141	MLE
ind2	1	-0.0171	0.0300	0.3243	0.5691	MLE

Re: macro - with automatic table creation (including P values) - please help =)

JeffMeyers — Wed, 10 Jun 2020 22:17:57 GMT

The p-value in that table you printed is the ProbChiSquare variable. It is the p-value from the parameter estimates table. Which p-value are you trying to print out? When you say Ci's are you looking for Odds ratios and CIs or a CI of the estimate? If you need a CI of the estimate then you would use the StdERR variable with the Estimate variable to calculate (e.g. estimate-1.96*stderr and estimate+1.96*stderr).

If you want Odds ratios with CI's then you can add ODDSRATIO statements to your proc logistic and then ODS OUTPUT those with ODDSRATIOSWALD:


ods output ParameterEstimates = test1 oddsratioswald=test2;
proc logistic data = xxx descending;
  model v1 = ind1 ind2;
  oddsratio ind1;
  oddsratio ind2;
run;

proc print data = test1;
run;

proc print data = test2;
run;

Note that if your IND1 and IND2 variables are meant to be categorical you should have them in a CLASS statement with the reference level you want. Then I would add / diff=ref to the ODDSRATIO statement for that variable.

Re: macro - with automatic table creation (including P values) - please help =)

GabbyJ — Wed, 10 Jun 2020 22:39:58 GMT

Jeff, you are amazing 😃 Thank you! This works now. However, I would still like to put these P values and CI's in a table through the macro.

The goal is to run a bunch of analysis and not have to manually create tables with (1) OR (2) P value (3) 95% CIs for the O.

Could you kindly guide me on how to edit the code below? I put in the ODS output statement like you suggested, but don't know how to run it through the loop. Right now it only outputs the last variables.

Thank you so much 😃

Gabby

Sample data:

data xxx;
  input v1-v5 ind1 ind2;
  cards;
1 0 1 1 0 34 23
0 0 1 0 1 22 32
1 1 1 0 0 12 10
0 1 0 1 1 56 90
0 1 0 1 1 26 80
1 1 0 0 0 46 45
0 0 0 1 1 57 53
1 1 0 0 0 22 77
0 1 0 1 1 44 45
1 1 0 0 0 41 72
;
run;

Code with Macro:

%macro mylogita(indata, all_deps, indvars =,);
  %let k=1;
  %let dep = %scan(&all_deps, &k);
  %do %while(&dep NE);
    title "The dependent variable is &dep";
    title2 "The independent variables are &indvars";
    ods output ParameterEstimates = test1 oddsratioswald=test2;
    proc logistic data=&indata des;
      model &dep = &indvars;
    run;
    %let k = %eval(&k + 1);
    %let dep = %scan(&all_deps, &k);
  %end;
  data &myout;
    set 
    %do i = 1 %to &k - 1;
      est&i
    %end; 
    ;
  run;
%mend;
*run the program;
%mylogita(xxx, v1 v2 v3, indvars = ind1 ind2);

title "These are the outputs";
proc print data = test1;
run;

proc print data = test2;
run;

Re: macro - with automatic table creation (including P values) - please help =)

smantha — Wed, 10 Jun 2020 23:37:43 GMT

%macro mylogita(indata, all_deps, indvars =,);
  %let k=1;
  %let dep = %scan(&all_deps, &k);
  %do %while(&dep NE);
    title "The dependent variable is &dep";
    title2 "The independent variables are &indvars";
    ods output ParameterEstimates = prob&k. oddsratioswald=odds&k.;;
    proc logistic data=&indata des;
      model &dep = &indvars;
    run;
    %let k = %eval(&k + 1);
    %let dep = %scan(&all_deps, &k);
  %end;

%mend;
  data &myout._odds;
    set 
    %do i = 1 %to &k - 1;
      odds&i (in=in&i.)
    %end; 
    ;
 %do i = 1 %to &k - 1;
      if in&i. then run=&i.;
    %end; 
  run;

  data &myout._prob;
    set 
    %do i = 1 %to &k - 1;
      prob&i (in=in&i.)
    %end; 
    ;
 %do i = 1 %to &k - 1;
      if in&i. then run=&i.;
    %end; 
  run;
*run the program;
%mylogita(xxx, v1 v2 v3, indvars = ind1 ind2);

title "These are the outputs";
proc print data = &myout._odds;
run;

proc print data =&myout2_prob;
run;

Re: macro - with automatic table creation (including P values) - please help =)

JeffMeyers — Thu, 11 Jun 2020 00:27:04 GMT

Here is how I would do it:

%macro mylogita(indata, all_deps, myout,indvars =,);
  %do k= 1 %to %sysfunc(countw(&all_deps,%str( )));
    %let dep = %scan(&all_deps, &k);
    title "The dependent variable is &dep";
    title2 "The independent variables are &indvars";
    ods output ParameterEstimates = test1 oddsratioswald=test2;
    proc logistic data=&indata des;
      model &dep = &indvars;
      %do i = 1 %to %sysfunc(countw(&indvars,%str( )));
        oddsratio  "%scan(&indvars,&i,%str( ))"  %scan(&indvars,&i,%str( ));
      %end;
    run;
    proc sql;
      create table est&k as
        select &k as model,"&dep" as dependent, a.effect as indvar,a.oddsratioest,a.lowercl,a.uppercl,b.probchisq
        from test2 a left join test1 b on upcase(a.effect)=upcase(b.variable);
      drop table test1,test2;
    quit;
  %end;
  data &myout;
    set 
    %do i = 1 %to %sysfunc(countw(&all_deps,%str( )));
      est&i
    %end; 
    ;
  run;
  proc datasets nolist nodetails;
    delete %do i = 1 %to %sysfunc(countw(&all_deps,%str( )));
      est&i
    %end; ;
   quit;
%mend;
Data Neuralgia;
input Treatment $ Sex $ Age Duration Pain $ @@;
datalines;
P F 68 1 No B M 74 16 No P F 67 30 No
P M 66 26 Yes B F 67 28 No B F 77 16 No
A F 71 12 No B F 72 50 No B F 76 9 Yes
A M 71 17 Yes A F 63 27 No A F 69 18 Yes
B F 66 12 No A M 62 42 No P F 64 1 Yes
A F 64 17 No P M 74 4 No A F 72 25 No
P M 70 1 Yes B M 66 19 No B M 59 29 No
A F 64 30 No A M 70 28 No A M 69 1 No
B F 78 1 No P M 83 1 Yes B F 69 42 No
B M 75 30 Yes P M 77 29 Yes P F 79 20 Yes
A M 70 12 No A F 69 12 No B F 65 14 No
B M 70 1 No B M 67 23 No A M 76 25 Yes
P M 78 12 Yes B M 77 1 Yes B F 69 24 No
P M 66 4 Yes P F 65 29 No P M 60 26 Yes
A M 78 15 Yes B M 75 21 Yes A F 67 11 No
P F 72 27 No P F 70 13 Yes A M 75 6 Yes
B F 65 7 No P F 68 27 Yes P M 68 11 Yes
P M 67 17 Yes B M 70 22 No A M 65 15 No
P F 67 1 Yes A M 67 10 No P F 72 11 Yes
A F 74 1 No B M 80 21 Yes A F 69 3 No
;
run;
%mylogita(neuralgia,pain,estimates,indvars=age duration)

This gives me the following table:

model	dependent	indvar	OddsRatioEst	LowerCL	UpperCL	ProbChiSq
1	pain	Age	1.167	1.036	1.316	0.0113
1	pain	Duration	0.981	0.934	1.031	0.4492

Re: macro - with automatic table creation (including P values) - please help =)

JeffMeyers — Thu, 11 Jun 2020 00:29:07 GMT

I put my solution in another reply, but wanted to mention that it only works if your independent variables are numeric and either continuous or binary. You'll need to change it if you have categorical data. I do have a nice macro you can try if you're trying to do a lot of modeling here:
https://communities.sas.com/t5/SAS-Communities-Library/MVMODELS-a-Macro-for-Survival-and-Logistic-Analysis/ta-p/635217