Programming using local macros collect commands

[Colin Hubbard]

This is an offshoot of a previous question: Using collect and margins to create custom table - Statalist, but more related to programming than to the collect commands. I am attempting to automate the retrieval of certain information from the margins command with the pwcompare option. Since this is a common task with multiple data sets, I would like to make this as flexible as possible. In order to do so, I need the program to generate a number of collect get commands, the number and content of which will depend on the levels of the factor variable included in the margins statement. (I should say that I am very new to Stata programming, so it's quite possible I've gone down a dead end path.) In any case, here is an example:

sysuse auto, clear
gen pricef = .
replace pricef = 1 if price < 7000
replace pricef = 2 if price >= 7000 & price < 10000
replace pricef = 3 if price >= 10000
label define price_lbl 1 "<7000" 2 "7000-10000" 3 "10000+"
label values pricef price_lbl

reg mpg i.pricef

collect create Margins

do mymargins pricef

I have created a do file called mymargins that is supposed to generate a table of predicted margins, a table of predicted marginal effects and output them to a word document:

* Define the variable name and the integer n
local varname `1'

// Use the levelsof command to get the --levels-- of the factor variable
levelsof `varname', local(levels)
// Define local macro -- n_levels -- = the number of levels of varname
local n_levels: word count `levels' 

// Define local macro -- m1 -- consisting a string of levels of varname
forvalues i = 1/`n_levels' {
    local m1 "`m1' `i'.`varname'"
}

// Define local macro -- contrasts -- = to string of all contrasts
foreach level1 of local levels {
    foreach level2 of local levels {
        if `level1' < `level2' {
            local contrasts "`contrasts' `level2'vs`level1'.`varname'"
        }
    }
}

// Define local macro -- commands -- consisting of collect get commands to retrieve elements of table_vs for each level of contrasts
local j = 1
foreach c of local contrasts {
    local lb_vs = `"collect get lb_vs= r(table_vs)['ll', `j'], tags(colname[`c'])"'
    local ub_vs = `"collect get ub_vs= r(table_vs)['ul', `j'], tags(colname[`c'])"'
    local pvalue = `"collect get pvalue= r(table_vs)['pvalue', `j'], tags(colname[`c'])"'
    display "`commands'"
    local commands "`commands' `lb_vs' ; `ub_vs' ; `pvalue' ;"
    local j = `j' + 1
}

collect set Margins

// Margins command
collect, name(Margins) tag(model[`1']): margins `1', pwcompare(effects)

#delimit ;
`commands'
#delimit cr

collect remap result[b_vs lb_vs ub_vs]=result[_r_b _r_lb _r_ub]    
collect composite define ci = _r_lb _r_ub, delimiter(", ") trim replace    
collect label levels result ci "95% CI"

*collect style use tbl.stjson, name(Margins) override
collect label levels result _r_b "Marginal Proportions", modify name(Margins)

display "`m1'"

collect title "Margins", name(Margins)
collect layout (colname[`m1']) (result[_r_b ci])
collect preview

putdocx begin
putdocx collect 

collect label levels result _r_b "Marginal Difference(s)", modify name(Margins)
collect title "Marginal Effects"
collect layout (colname[`contrasts']) (result[_r_b ci pvalue])

putdocx paragraph 
putdocx text ("Mountain High Report: ")

putdocx describe

putdocx collect
putdocx save test, replace

The main issue seems to be the commands macro that is created within the do file. The individual commands within the commands macro run, as can be confirmed by the looking at the results of

collect levelsof colname

for example. However, nothing after the commands macro is invoked seems to run although it appears on the console. For example, if you run

collect levelsof result

it does not include ci, even though it should be created in the collect composite command. In addition, the Word document is not created.

Here is the console output from invoking the mymargins macro

. do mymargins pricef

. * Define the variable name and the integer n
. local varname `1'

. 
. // Use the levelsof command to get the --levels-- of the factor variable
. levelsof `varname', local(levels)
1 2 3

. // Define local macro -- n_levels -- = the number of levels of varname
. local n_levels: word count `levels' 

. 
. // Define local macro -- m1 -- consisting a string of levels of varname
. forvalues i = 1/`n_levels' {
  2.     local m1 "`m1' `i'.`varname'"
  3. }

. 
. // Define local macro -- contrasts -- = to string of all contrasts
. foreach level1 of local levels {
  2.     foreach level2 of local levels {
  3.         if `level1' < `level2' {
  4.             local contrasts "`contrasts' `level2'vs`level1'.`varname'"
  5.         }
  6.     }
  7. }

. 
. // Define local macro -- commands -- consisting of collect get commands to retrieve
>  elements of table_vs for each level of contrasts
. local j = 1

. foreach c of local contrasts {
  2.         local lb_vs = `"collect get lb_vs= r(table_vs)['ll', `j'], tags(colname[
> `c'])"'
  3.         local ub_vs = `"collect get ub_vs= r(table_vs)['ul', `j'], tags(colname[
> `c'])"'
  4.         local pvalue = `"collect get pvalue= r(table_vs)['pvalue', `j'], tags(co
> lname[`c'])"'
  5.         display "`commands'"
  6.         local commands "`commands' `lb_vs' ; `ub_vs' ; `pvalue' ;"
  7.         local j = `j' + 1
  8. }

 collect get lb_vs= r(table_vs)['ll', 1], tags(colname[2vs1.pricef]) ; collect get ub
> _vs= r(table_vs)['ul', 1], tags(colname[2vs1.pricef]) ; collect get pvalue= r(table
> _vs)['pvalue', 1], tags(colname[2vs1.pricef]) ;
 collect get lb_vs= r(table_vs)['ll', 1], tags(colname[2vs1.pricef]) ; collect get ub
> _vs= r(table_vs)['ul', 1], tags(colname[2vs1.pricef]) ; collect get pvalue= r(table
> _vs)['pvalue', 1], tags(colname[2vs1.pricef]) ; collect get lb_vs= r(table_vs)['ll'
> , 2], tags(colname[3vs1.pricef]) ; collect get ub_vs= r(table_vs)['ul', 2], tags(co
> lname[3vs1.pricef]) ; collect get pvalue= r(table_vs)['pvalue', 2], tags(colname[3v
> s1.pricef]) ;

. 
. collect set Margins
(current collection is Margins)

. 
. // Margins command
. collect, name(Margins) tag(model[`1']): margins `1', pwcompare(effects)

Pairwise comparisons of adjusted predictions                Number of obs = 74
Model VCE: OLS

Expression: Linear prediction, predict()

----------------------------------------------------------------------------------
                 |            Delta-method    Unadjusted           Unadjusted
                 |   Contrast   std. err.      t    P>|t|     [95% conf. interval]
-----------------+----------------------------------------------------------------
          pricef |
     7000-10000  |
             vs  |
          <7000  |  -2.149425   2.253092    -0.95   0.343    -6.641962    2.343112
10000+ vs <7000  |  -7.482759   1.798949    -4.16   0.000    -11.06976   -3.895755
         10000+  |
             vs  |
     7000-10000  |  -5.333333   2.713082    -1.97   0.053    -10.74306    .0763977
----------------------------------------------------------------------------------

. 
. #delimit ;
delimiter now ;
. `commands'
> #delimit cr
> 
> collect remap result[b_vs lb_vs ub_vs]=result[_r_b _r_lb _r_ub] 
> collect composite define ci = _r_lb _r_ub, delimiter(", ") trim replace 
> collect label levels result ci "95% CI"
> 
> *collect style use tbl.stjson, name(Margins) override
> collect label levels result _r_b "Marginal Proportions", modify name(Margins)
> 
> display "`m1'"
> 
> collect title "Margins", name(Margins)
> collect layout (colname[`m1']) (result[_r_b ci])
> collect preview
> 
> putdocx begin
> putdocx collect 
> 
> collect label levels result _r_b "Marginal Difference(s)", modify name(Margins)
> collect title "Marginal Effects"
> collect layout (colname[`contrasts']) (result[_r_b ci pvalue])
> 
> putdocx paragraph 
> putdocx text ("Mountain High Report: ")
> 
> putdocx describe
> 
> putdocx collect
> putdocx save test, replace
> 
> 

end of do-file

I am using Stata/BE 18.5. Any help would be greatly appreciated.

Colin

[Clyde Schechter]

Well, I can point up a couple of errors I find in the code. They need to be corrected, but even with these corrections, there is still something wrong with the code which I have not been able to figure out.

1. Because you are running the `commands' under -#delim ;-, the line for that has to be:

`commands' ;

The reason for that is that the -#delim- commands are preprocessor commands that do not know that the content of `commands', at execution time, will be semi-colon delimited. So once you get to #delim ;-, and nothing after that has an explicit ; at parse time, Stata thinks that everything thereafter is just one long never-ending command. That's why every line in the output you show from -#delimit cr- on is preceded by a >. That's Stata's way of telling you that the line is understood as a continuation of the preceding line.

2. Your references to r(table_vs)['ll', `j'] and the others are syntactically incorrect. There is nothing in Stata that gets wrapped in single quotes. Local macros, of course, get wrapped in ` ', and character stings get wrapped in " " or `" "'. But '...' in Stata is always wrong (except as part of the value of a string variable). So those have to be changed to r(table_vs)["ll", `j'], etc. Also, to make this work, you have to move the code that defines local macro commands down after the collect, name(Margins) tag(model[`1']): margins `1', pwcompare(effects)- command, because up where you have it, r(table_vs) has yet to be defined and will be expanded as a null string, which makes local macro command a gigantic syntax error.

3. I am not certain of this, but it is likely that the -collect- commands wipe out r(table_vs). r(table_vs), being returned in -r()-, is necessarily volatile. Nothing returned in -r()- can ever be counted on to continue to exist once any later command that saves anything in -r()- is executed. I'm frankly pretty ignorant about the workings of -collect- and its various subcommands, but I would guess that they do wipe out pre-existing contents of -r()-. So you should save r(table_vs) as a real matrix immediately after your -collect, name(Margins) tag(model[`1']): margins `1', pwcompare(effects)- command, and replace all references to -r(table_vs)- in the code creating local macro commands with the name of that matrix.

When you make those corrections, the code still fails during the execution of the line -`commands' ;-. The error message given is -invalid '1'- with error code r(198). I have tried in vain to figure out where it is going wrong. I have checked that the content of `commands' is what it is supposed to be, and there are no explicit references to M (the name I have given to the real matrix copy of r(table_vs)) and `commands' are as they should be:

. macro list _commands
_commands:       collect get lb_vs= M["ll", 1], tags(colname[2vs1.pricef]) ; collect get ub_vs= M["ul", 1], tags(colname[2vs1.pricef]) ; collect get pvalue= M["pvalue",
                1], tags(colname[2vs1.pricef]) ; collect get lb_vs= M["ll", 2], tags(colname[3vs1.pricef]) ; collect get ub_vs= M["ul", 2], tags(colname[3vs1.pricef]) ;
                collect get pvalue= M["pvalue", 2], tags(colname[3vs1.pricef]) ; collect get lb_vs= M["ll", 3], tags(colname[3vs2.pricef]) ; collect get ub_vs= M["ul",
                3], tags(colname[3vs2.pricef]) ; collect get pvalue= M["pvalue", 3], tags(colname[3vs2.pricef]) ;

. matrix list M

M[9,3]
              2vs1.       3vs1.       3vs2.
            pricef      pricef      pricef
     b  -2.1494253  -7.4827586  -5.3333333
    se   2.2530916   1.7989494   2.7130816
     t   -.9539893  -4.1595159  -1.9657844
pvalue   .34332581   .00008806   .05323503
    ll  -6.6419624  -11.069762  -10.743064
    ul   2.3431118  -3.8957554   .07639767
    df          71          71          71
  crit   1.9939434   1.9939434   1.9939434
 eform           0           0           0

I am at a loss to explain this, because the only 1's in `commands' are the second subscripts of M when `j' == 1, and I cannot see anything invalid about them. When I put -trace- on to see what is happening, I get a very long listing of the inner-workings of -collect get- and I am simply unable to follow it.

So I'm sorry I don't have a complete solution to your problem. Perhaps picking up from here, you or somebody else can figure out what is going on inside -collect get- that leads to this error message.

By the way, this is the contents of -mymargins.do- with my modifications:

* Define the variable name and the integer n
local varname `1'

// Use the levelsof command to get the --levels-- of the factor variable
levelsof `varname', local(levels)
// Define local macro -- n_levels -- = the number of levels of varname
local n_levels: word count `levels'

// Define local macro -- m1 -- consisting a string of levels of varname
forvalues i = 1/`n_levels' {
    local m1 "`m1' `i'.`varname'"
}

// Define local macro -- contrasts -- = to string of all contrasts
foreach level1 of local levels {
    foreach level2 of local levels {
        if `level1' < `level2' {
            local contrasts "`contrasts' `level2'vs`level1'.`varname'"
        }
    }
}

collect set Margins

// Margins command
collect, name(Margins) tag(model[`1']): margins `1', pwcompare(effects)
matrix M = r(table_vs)

//  NOTE NEW LOCATION AFTER THE -margins- COMMAND RUNS
// Define local macro -- commands -- consisting of collect get commands to retrieve elements of table_vs for each level of contrasts
local j = 1
foreach c of local contrasts { // NOTE USE OF '' '', NOT ' '.
    local lb_vs = `"collect get lb_vs= M["ll", `j'], tags(colname[`c'])"'
    local ub_vs = `"collect get ub_vs= M["ul", `j'], tags(colname[`c'])"'
    local pvalue = `"collect get pvalue= M["pvalue", `j'], tags(colname[`c'])"'
    local commands "`commands' `lb_vs' ; `ub_vs' ; `pvalue' ;"
    local j = `j' + 1
}
// CHECK THAT WE HAVE THE WRITE COMMANDS AND MATRIX
macro list _commands
matrix list M

#delimit ;
`commands';
#delimit cr

collect remap result[b_vs lb_vs ub_vs]=result[_r_b _r_lb _r_ub]    
collect composite define ci = _r_lb _r_ub, delimiter(", ") trim replace    
collect label levels result ci "95% CI"

*collect style use tbl.stjson, name(Margins) override
collect label levels result _r_b "Marginal Proportions", modify name(Margins)

display "`m1'"

collect title "Margins", name(Margins)
collect layout (colname[`m1']) (result[_r_b ci])
collect preview

putdocx begin
putdocx collect

collect label levels result _r_b "Marginal Difference(s)", modify name(Margins)
collect title "Marginal Effects"
collect layout (colname[`contrasts']) (result[_r_b ci pvalue])

putdocx paragraph
putdocx text ("Mountain High Report: ")

putdocx describe

putdocx collect
putdocx save test, replace

[Hemanshu Kumar]

I also can't quite figure out what is going on here, except to say that there is something weird about the way changing the delimiter seems to interact with macro substitution. For instance, consider this code:

local commands dis 5 ; dis 6;

#delimit ;
`commands' ;
#delimit cr

which results in

. local commands dis 5 ; dis 6;

.
. #delimit ;
delimiter now ;
. `commands' ;
5; invalid name
r(198);

end of do-file

r(198);

Hence for the moment, my solution is to remove the need to change delimiters. Here is my further modification of Clyde's code. I have confirmed that this works.

* Define the variable name and the integer n
local varname `1'

// Use the levelsof command to get the --levels-- of the factor variable
levelsof `varname', local(levels)
// Define local macro -- n_levels -- = the number of levels of varname
local n_levels: word count `levels'

// Define local macro -- m1 -- consisting a string of levels of varname
forvalues i = 1/`n_levels' {
    local m1 "`m1' `i'.`varname'"
}

// Define local macro -- contrasts -- = to string of all contrasts
foreach level1 of local levels {
    foreach level2 of local levels {
        if `level1' < `level2' {
            local contrasts "`contrasts' `level2'vs`level1'.`varname'"
        }
    }
}

// Define local macros consisting of collect get commands to retrieve elements of table_vs for each level of contrasts
local j = 1
foreach c of local contrasts {
    local lb_vs_`j' = `"collect get lb_vs= M["ll", `j'], tags(colname[`c'])"'
    local ub_vs_`j' = `"collect get ub_vs= M["ul", `j'], tags(colname[`c'])"'
    local pvalue_`j' = `"collect get pvalue= M["pvalue", `j'], tags(colname[`c'])"'
    local j = `j' + 1
}

local j = `j' - 1

collect set Margins

// Margins command
collect, name(Margins) tag(model[`1']): margins `1', pwcompare(effects)
matrix M = r(table_vs)

noisily matrix list M

forval i = 1/`j' {
    `lb_vs_`i''
    `ub_vs_`i''
    `pvalue_`i''
}

collect remap result[b_vs lb_vs ub_vs]=result[_r_b _r_lb _r_ub]    
collect composite define ci = _r_lb _r_ub, delimiter(", ") trim replace    
collect label levels result ci "95% CI"

*collect style use tbl.stjson, name(Margins) override
collect label levels result _r_b "Marginal Proportions", modify name(Margins)

display "`m1'"

collect title "Margins", name(Margins)
collect layout (colname[`m1']) (result[_r_b ci])
collect preview

putdocx begin
putdocx collect

collect label levels result _r_b "Marginal Difference(s)", modify name(Margins)
collect title "Marginal Effects"
collect layout (colname[`contrasts']) (result[_r_b ci pvalue])

putdocx paragraph
putdocx text ("Mountain High Report: ")

putdocx describe

putdocx collect
putdocx save test, replace

[Colin Hubbard]

Thank you so much Clyde and Hemanshu!!! I've confirmed that Hemanshu's modification of Clyde's program works on my data set as well. Your dedication to helping your colleagues is laudable and greatly appreciated. Colin

[Colin Hubbard]

So, here is an extension of the previous problem. I think this problem has more to do with the -collect get- command. I am attempting to use a variation of the above on a new data set that has multiple variables in the -margins- command. The reproducible example that invokes the modified do file is:

 sysuse auto, clear

xtile pricef = price, nq(2)

xtile mpgf = mpg, nq(2)
label define quantiles 1 "Low" 2 "High", replace
label values mpgf pricef quantiles

collect create Margins

do test_marg

collect style default
collect remap result[b_vs lb_vs ub_vs]=result[_r_b _r_lb _r_ub]
collect composite define ci = _r_lb _r_ub, delimiter(", ") trim replace
  
collect style cell result[_r_b ci], nformat(%9.0fc)
collect style cell result[ci], sformat("(%s)")
collect style cell result[pvalue], nformat(%9.3f) min(.001)

collect style row split, dups(first)

collect style header result, title(hide)

collect style column, dups(center)

collect style cell colname[2vs1.pricef 2vs1.mpgf], border(bottom)

collect label levels result _r_b "Length" ci "95% CI" pvalue "P", replace
collect label levels colname 2vs1.pricef "Difference" 2vs1.mpgf "Difference"

collect layout (colname[$levs_contr]) (model#result[_r_b ci pvalue])

The -test_marg- do file, which is a variation of the above and allows for additional variables in the -margins- command is:

version 18.0

local vars pricef mpgf

local n_var: word count `vars'

// Define local macro -- levels -- 
foreach d of local vars {
        forval j = 1/2 {
            local levels "`levels' `j'.`d'"
        }
    }

// Define local macro -- contrasts -- = to string of all contrasts
foreach d of local vars {
            local contrasts "`contrasts' 2vs1.`d'"
}

global contrasts = "`contrasts'"

// Define local macro -- levs_contr -- which includes levels and contrasts in order of vars
foreach d of local vars {
        forval j = 1/2 {
            local levs_contr "`levs_contr' `j'.`d'"
        }
        local levs_contr "`levs_contr' 2vs1.`d'"
    }
    
global levs_contr = "`levs_contr'"

// Define local macros consisting of collect get commands to retrieve elements of table_vs for each level of contrasts
local j = 1
foreach c of local contrasts {
    local lb_vs_`j' = `"collect get lb_vs= M["ll", `j'], tags(colname[`c'])"'
    local ub_vs_`j' = `"collect get ub_vs= M["ul", `j'], tags(colname[`c'])"'
    local pvalue_`j' = `"collect get pvalue= M["pvalue", `j'], tags(colname[`c'])"'
    local j = `j' + 1
}

// Display them to check
forval i = 1/`j' {
    dis `"`lb_vs_`i''"'
    dis `"`ub_vs_`i''"'
    dis `"`pvalue_`i''"'
}


// Set j to number of contrasts
local j = `j' - 1

// Regression Model
quietly: reg length i.pricef i.mpgf
    
// Margins command
quietly: collect, tag(model[(All)]): margins pricef mpgf, pwcompare(eff)

// Save r(table)
matrix M = r(table_vs)

//Execute each collect get statement
forval i = 1/`j' {
    `lb_vs_`i''
    `ub_vs_`i''
    `pvalue_`i''
}

The problem is that although the -collect get- commands seem to successfully run (based upon my review of the -results- dimension), the resulting table does not print the ci or pvalue for the contrasts:

. collect layout (colname[$levs_contr]) (model#result[_r_b ci pvalue])

Collection: Margins
      Rows: colname[ 1.pricef 2.pricef 2vs1.pricef 1.mpgf 2.mpgf 2vs1.mpgf]
   Columns: model#result[_r_b ci pvalue]
   Table 1: 6 x 2

---------------------------------------------
                          |       (All)      
                          | Length   (95% CI)
--------------------------+------------------
2 quantiles of price Low  |    183 (178, 187)
                     High |    193 (189, 198)
Difference                |     11           
--------------------------+------------------
2 quantiles of mpg   Low  |    202 (198, 207)
                     High |    173 (168, 177)
Difference                |    -30           
---------------------------------------------

I've looked at this every which way but can't figure out why this doesn't work given that the previous version worked. Thanks again for any help you can provide.

[Colin Hubbard]

If anyone is interested, I solved the problem. It was as simple as failing to add the model tag to the -collect get- commands generated by the do file. I though the model tag would be inherited from the -margins- command. The corrected part of the do file is here:

 
 // Define local macros consisting of collect get commands to retrieve elements of table_vs for each level of contrasts local j = 1 foreach c of local contrasts {     local lb_vs_`j' = `"collect get lb_vs= M["ll", `j'], tags(colname[`c'] model[(All)])"'     local ub_vs_`j' = `"collect get ub_vs= M["ul", `j'], tags(colname[`c'] model[(All)])"'     local pvalue_`j' = `"collect get pvalue= M["pvalue", `j'], tags(colname[`c'] model[(All)])"'     local j = `j' + 1 }

[Hemanshu Kumar]

Thanks for posting the solution, Colin! I'm sure it'll be helpful to people who look up this thread in the future.