topic Re: macro proc genmod in SAS Programming

macro proc genmod

samnan — Thu, 14 Apr 2016 14:01:01 GMT

Hi;

I have one exposure (exp), 4 dependants (dep) and 9 independants (indp) and i want to run proc genmod. the exposure will present with each independant to model each dependant.

For example modeling the first dependant:

proc genmod data=have;

model dep1 = exp indp1 /d=nb;run;

proc genmod data=have;

model dep1 = exp indp2 /d=nb;run;

proc genmod data=have;

model dep1 = exp indp9 /d=nb;run;

then the same for the other dependants.

i belive using macro will save the time.

Any hlep would be appreciated.

Re: macro proc genmod

RW9 — Thu, 14 Apr 2016 16:05:11 GMT

Not too familiar with that procedure, however all procedures allow by group processing - which would be both easier to maintain and less resource hungry. Change your input data structure slightly - you can use transpose - normalised data is easier to work with:

You have:

... INDP1 INDP2 INDP3 ...

xyz xyz xyz

Change this to:

... INDP RES

... 1 xyz

... 2 xyz

Then you can do one gemod:

proc genmod data=have;
  by indp;
  model dep1 = exp res /d=nb;
run;

Sorry, not sure how this affects the model, can only advise on structure and syntax.

Re: macro proc genmod

FreelanceReinh — Thu, 14 Apr 2016 15:28:27 GMT

Hi @RW9,

In your MODEL statement, indp should read res to make sense.

Re: macro proc genmod

FreelanceReinh — Thu, 14 Apr 2016 15:48:24 GMT

Hi @samnan,

Personally, I prefer analysis datasets in wide format (like your HAVE dataset) for statistical modelling procedures. All variables occurring in one MODEL statement must be available anyway in the input dataset.

With your existing dataset HAVE, your "macro" can be implemented using CALL EXECUTE:

data _null_;
array dep dep1 dep2 ...; /* list your dependent variables here */
array indp indp1 indp2 ...; /* list your independent variables here */
do i=1 to dim(dep);
  do j=1 to dim(indp);
    call execute( 'proc genmod data=have; '
               || 'model ' || vname(dep[i]) || ' = exp ' || vname(indp[j]) || ' /d=nb; run;');
  end;
end;
run;

This assumes that all your independent variables are numeric (which is plausible as you didn't mention a CLASS statement). Please note that the arrays above do not contain variables from dataset HAVE, but just "dummy variables" with the same names.

Re: macro proc genmod

RW9 — Thu, 14 Apr 2016 16:05:45 GMT

Thanks, updated.

Re: macro proc genmod

samnan — Thu, 14 Apr 2016 16:26:06 GMT

Dear @RW9 thanks for your valuable help, i do not have (res) variable. but the code provided by @FreelanceReinh works good.

Re: macro proc genmod

samnan — Thu, 14 Apr 2016 16:27:49 GMT

Dear @FreelanceReinh thanks for your appreciated help,

Re: macro proc genmod

samnan — Thu, 14 Apr 2016 19:08:42 GMT

Can you take it one step further, where to let macro keep significant (indp) variables only. just to make it like (selection) option in logistic regression modeling.

Re: macro proc genmod

FreelanceReinh — Thu, 14 Apr 2016 21:03:02 GMT

So, you'd run the 4*9 PROC GENMOD steps (via CALL EXECUTE) and for each of the four dependent variables you would like to have a list of those independent variables which had p-values <0.05 in table "Analysis Of Maximum Likelihood Parameter Estimates," excluding variable EXP, which is always in the model?

Yes, this is possible. You could write the parameter estimates (incl. p-values) to datasets EST_DEP1, EST_DEP2, ... (where "DEPi" would be replaced by the name of the i-th dependent variable) and then, for example, select the names of the independent variables of interest via PROC SQL into macro variables INDPLIST_DEP1, INDPLIST_DEP2, ...

Here is draft code for this:

data _null_;
array dep dep1 dep2 ...; /* list your dependent variables here */
array indp indp1 indp2 ...; /* list your independent variables here */
do i=1 to dim(dep);
  call execute('ods output ParameterEstimates(persist=proc)=est_' || vname(dep[i]) ||';');
  do j=1 to dim(indp);
    call execute( 'proc genmod data=have;'
               || 'model ' || vname(dep[i]) || ' = exp ' || vname(indp[j]) || ' /d=nb; run;');
  end;
  call execute('ods output close;');
  call execute('proc sql noprint; select parameter into :indplist_' || vname(dep[i])
            || ' separated by " " from est_' || vname(dep[i])
            || ' where upcase(parameter) not in ("INTERCEPT", "EXP", "DISPERSION") & .<ProbChiSq<0.05; quit;');
end;
run;

%put &=indplist_dep1; /* replace dep1 by the name of the first dep. variable */
%put &=indplist_dep2; /* replace dep1 by the name of the second dep. variable */
...

You could use the variable lists &indplist_depi in MODEL statements of subsequent PROC GENMOD calls.

As I said, this is draft code. If for a particular dependent variable none of the 9 independent variables (excl. EXP) turned out to be significant, the corresponding macro variable would not be created (hence, the corresponding %PUT statement would cause a WARNING in the log).

Re: macro proc genmod

samnan — Thu, 14 Apr 2016 21:54:28 GMT

i am not sure about last part (%put &=indplist_dep1; /* replace dep1 by the name of the first dep. variable */
).

i rename my dependant variable to (dep, dep1 ... dep7) and the indepdendant variables to (indp, indp1 .... indp16) just to apply the code. when i run it this time it showed the same results of old one.

Re: macro proc genmod

FreelanceReinh — Thu, 14 Apr 2016 22:11:51 GMT

The %PUT statements are just optional to demonstrate that the variable lists have been created.

Please note that my comments "list your (in)dependent variables here" referred to the lists dep1 dep2 ... and indp1 indp2 ..., respectively. The names dep and indp are the array names and must not be replaced. So, for instance, if your independent variables were AGE, HEIGHT, WEIGHT, the second array statement would read:

array indp age height weight;

and similarly for the first array.

If the first dependent variable was XYZ and only AGE and WEIGHT were significant for that, the suggested code would create a macro variable INDPLIST_XYZ containing age weight (possibly in upper or mixed case), selected from a WORK dataset named EST_XYZ.

Re: macro proc genmod

samnan — Thu, 14 Apr 2016 22:38:24 GMT

Dear @FreelanceReinh you are doing very good coding. now i got it and i like it although the list contains all veriables and thier values either significant or insignificant.

Re: macro proc genmod

FreelanceReinh — Thu, 14 Apr 2016 22:46:13 GMT

What do you mean by "the list contains all veriables and thier values either significant or insignificant"? Are you saying that some or all of the macro variables INDPLIST_depi do not contain the correct variable lists? If anything does not work or is unclear, we can continue the discussion tomorrow (Central European Time).

Re: macro proc genmod

RW9 — Fri, 15 Apr 2016 08:23:05 GMT

RES is just a variable name I created, when the data is taken from going along the table, i.e. they are variables your data looks like this:

... INDP1 INDP2 INDP3 ...

... xyz def abc ...

...

When the data is normalised it would look like this:

... INDP RES (call these columns what you want)...

... 1 xyz...

... 2 def...

... 3 abc...

The second structure - exactly the same data, just in a different layout - is both easier to program with, uses core Base SA functionality of by group processing which is quicker and just generally better in all respsects. It is a useful thing to note that a slight restructure to your data can make your programming more efficient and easier to read and maintain.

Re: macro proc genmod

samnan — Fri, 15 Apr 2016 14:17:10 GMT

what i meant is that the (%put &=indplist_indp1) contains all independant variables (indp1 .. indp16) regardless of their P-value. I thought the list contains only significant variables (if the P-value < 0.05).

Re: macro proc genmod

samnan — Fri, 15 Apr 2016 14:27:31 GMT

Ithink that because of the follwoing:

first let explain here what i got in the list in terms of the (parameter):

Intercept

exp

indp1

Dispersion

usualy the (Intercept) or the (exp) P-value turns to be significant (p-value < 0.05), so it will be included to the list even if the (indp1) insignificant.

Re: macro proc genmod

FreelanceReinh — Fri, 15 Apr 2016 14:58:23 GMT

How can Intercept etc. and non-significant variables be included, given the WHERE clause

 where upcase(parameter) not in ("INTERCEPT", "EXP", "DISPERSION") & .<ProbChiSq<0.05;

in the PROC SQL code?

I had tested my code successfully using a subset of SASHELP.CARS as dataset HAVE. Here is a short section from the log:

NOTE: Algorithm converged.
NOTE: The negative binomial dispersion parameter was estimated by maximum likelihood.
NOTE: PROCEDURE GENMOD used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


4   + ods output close;
NOTE: The data set WORK.EST_INVOICE has 8 observations and 10 variables.
5   + proc sql noprint;
5   +                   select parameter into :indplist_invoice separated by " " from est_invoice where upcase(parameter) not in
("INTERCEPT", "HORSEPOWER", "DISPERSION") & .<ProbChiSq<0.05;
5   +
 quit;
NOTE: PROCEDURE SQL used (Total process time):

A comparison of the above log excerpt with a corresponding section of your SAS log (one such section per dependent variable) will be a promising first step towards finding the issue at your end.

Of course, the names "INVOICE" and "HORSEPOWER" are data-specific. The dataset WORK.EST_yourvariablename will show what the PROC SQL step should have written into the pertinent macro variable INDPLIST_yourvariablename.

Re: macro proc genmod

samnan — Fri, 15 Apr 2016 18:49:13 GMT

Dear @FreelanceReinh i have tried many times but could not got it; Please find my sample below:

data have;
infile cards dlm=" ";
input res trans commn commr busn totacc facc fsacc exp;
cards;
0.5106 0.00592 0.00204 0.04674 0.01549 1561 8 45 14.6401
0.54689 0.00368 0.00104 0.01984 0.00878 1293 6 50 14.5009
0.55426 0.00755 0.00368 0.01085 0.0197 1283 6 27 13.7799
0.78301 0.00229 0.00246 0.03242 0.02246 1399 3 25 13.9239
0.52035 0.00555 0.00813 0.00985 0.0148 835 3 24 13.8535
0.52406 0.00045 0.00179 0.01541 0.00757 784 5 15 14.4113
0.54079 0.01462 0.05013 0.01628 0.02279 936 4 13 14.0026
0.0878 0.00312 0.00011 0.00942 0.0025 1034 5 16 14.1336
0.04896 0.00157 0.04674 0.11843 0.0066 1179 8 25 13.9206
0.41158 0.00399 0.00734 0.25723 0.02834 1893 2 31 13.7067
0.62409 0.00807 0.00253 0.03514 0.00633 1699 7 49 13.821
0.34779 0.0289 0.05937 0.04541 0.02666 1900 9 39 14.9494
0.57421 0.00915 0.00181 0.03558 0.00675 1402 3 42 13.9342
0.68064 0.00395 0.0007 0.08047 0.02416 376 0 5 12.6189
0.38445 0.01718 0.00134 0.05237 0.0705 1623 11 42 14.7249
0.71162 0.00409 0.00075 0.02756 0.0264 1433 2 14 14.0926
0.51897 0.00997 0.00074 0.04778 0.03209 5088 11 71 14.894
0.63653 0.00411 0.00374 0.0487 0.0202 2044 7 51 14.6865
0.31564 0.00522 0.00018 0.01399 0.04705 1005 3 20 13.9002
0.35732 0.00522 0.00208 0.04063 0.02346 5741 34 157 15.3313
0.75071 0.01419 0.00551 0.02774 0.01832 980 4 25 13.9193
0.30134 0 0.00008 0.00945 0.00451 671 7 27 14.004
0.24283 0.00123 0.01685 0.00348 0.00336 564 2 10 14.074
0.46881 0.00231 0.00829 0.01293 0.03569 700 1 5 13.7692
0.03949 0.01613 0 0.00055 0.00081 407 1 6 13.2234
0.04287 0.00561 0.04142 0.01772 0.01949 4436 34 99 15.0589
0.32452 0.01828 0.00561 0.08055 0.01413 9017 40 162 15.1009
0.01545 0.00422 0.00205 0.00982 0.03275 2866 39 114 15.7243
0.05259 0.00144 0.08136 0.00561 0.01251 227 1 5 13.9364
0.0216 0.00012 0.00009 0.02055 0.012 2757 11 61 14.2239
0.01964 0.00341 0.00691 0.00493 0.00128 41 0 0 14.2912
0.02977 0.00424 0.01542 0.00407 0.00895 1440 10 35 14.6687
0.49944 0.01248 0.01842 0.01607 0.00833 332 1 4 11.7664
0.57083 0.0105 0.00156 0.12008 0.0259 759 3 13 13.136
0.00176 0.0002 0.01181 0.00386 0.00292 1784 43 126 15.3832
0.01671 0.00037 0.00009 0.00096 0.00116 234 1 6 11.4916
0.02278 0.00003 0.00231 0.0088 0.00024 3272 48 142 14.6535
0.07297 0 0.01172 0 0 62 0 1 9.2823
0.44657 0.06945 0.02416 0.05744 0.12574 1536 3 22 14.0376
0.00054 0 0 0 0.00008 655 14 39 12.7758
0.69536 0.01753 0.00429 0.03601 0.01435 1934 5 37 14.3894
0.49895 0.00396 0 0.26858 0.01808 354 4 14 12.1618
0.02764 0.00178 0.04852 0.01417 0.02677 5776 30 134 15.0845
0.00177 0.0002 0.00037 0.00006 0.00003 126 2 2 11.8863
0.45239 0.01005 0.00076 0.00014 0.01328 1246 4 31 14.4243
0.13396 0.00224 0.01808 0.0079 0.00452 5140 45 240 15.2573
0 0.0708 0 0 0 1272 9 45 13.0868
0.55166 0.01672 0.00734 0.04716 0.00758 2015 12 61 14.5673
0.49025 0.01888 0.01895 0.04154 0.07337 1777 5 18 14.0023
0.10641 0.00109 0.00371 0.03058 0.00735 3680 17 111 15.4036
;run;

data _null_;
array dep totacc facc fsacc;
array indp res trans commn commr busn;
do i=1 to dim(dep);
call execute('ods output ParameterEstimates(persist=proc)=est_' || vname(dep[i]) ||';');
do j=1 to dim(indp);
call execute( 'proc genmod data=have;'
|| 'model ' || vname(dep[i]) || ' = exp ' || vname(indp[j]) || ' /d=nb; run;');
end;
call execute('ods output close;');
call execute('proc sql noprint; select parameter into :indplist_' || vname(dep[i])
|| ' separated by " " from est_' || vname(dep[i])
|| ' where upcase(parameter) not in ("INTERCEPT", "EXP", "DISPERSION") & .<ProbChiSq<0.05; quit;');
end;
run;

%put &=indplist_totacc ;
%put &=indplist_facc ;
%put &=indplist_fsacc;

Re: macro proc genmod

FreelanceReinh — Fri, 15 Apr 2016 19:16:07 GMT

Hi @samnan,

Thank you for posting this realistic example. Good news: I've run your code and found that, as far as I see, all results are correct and as expected. The only WARNING message is the one I announced already in this earlier post: There are no significant predictors for TOTACC among the five candidate variables, hence macro variable INDPLIST_TOTACC is not created and the %PUT statement is not applicable. (We could create it as an empty macro variable if needed.)

The other two macro variables contain the lists of (at the 0.05 level) significant independent variables:

INDPLIST_FACC=res busn
INDPLIST_FSACC=busn

These results are in accordance with the datasets EST_TOTACC, EST_FACC and EST_FSACC, which were produced correctly with the expected 20 observations each (=4 obs. for each of the 5 indep. variables).

Did you get anything different or what is it that you "could not [get]" although you "have tried many times"?

Re: macro proc genmod

samnan — Fri, 15 Apr 2016 19:16:18 GMT

Dear @RW9 thanks for your explanation, i will try it