Creating local macro names using a loop

[Nils Moseid]

Hi, this is my first post in the forum, I hope I will do this correctly. I am using Stata 15.1

I have som regression results and graphs that I am pasting to Excel using putexcel.
The data will be put into the spreadsheet on different columns, and I am using local macros to store the column numbers. The number of columns in the spreadsheet is around 100. I was wondering if it possible to assign names to local macros using another macro? This is what I am doing now, and it is generating a lot of code and a lot of lines:

local col = 1
excelcol `col'
local c1 = r(column)
local col = `col' + 1
excelcol `col'
local c2 = r(column)
local col = `col' + 2
excelcol `col'
local c3 = r(column)

It would shorten things up if I could do something like this:

forval i=1/100 {
local col = "ì'"
excelcol col
local c"`i'" = col /* creating macros named c1, c2....c100 */
}

But I get all sorts of messages about unrecognized namem regarding on how I put the quotation marks in the statement "local c"`i'" ="

Is this possible at all?

Thank you all for a great forum!

[Nick Cox]

excelcol is community-contributed from SSC, as you are asked to explain (FAQ Advice #12).

I think you need to back up in two respects.

1. Read the sections in {U] 18 on locals more slowly, as you seem to be guessing wildly about syntax.

2. Tell us more precisely what you want to put into a spreadsheet. You mention putexcel but don't show any code using it.

FWIW you can do things like this

forval i = 1/100 { 
      local c`i'  = `i' 
}

although whether that is really what you need I can;t say. Creating hundreds of local macros is essentially never needed.

I've never used putexcel myself, so may not say more than this.

[Nils Moseid]

Thank you for your advice, I will take a closer look at the proposed sections before making any further requests.

[Noah Spencer]

I have a related question here. I am trying to store each element of my dataset to locals with a for-loop.

Here is my data:

* Example generated by -dataex-. For more info, type help dataex
clear
input str18 dv float(att pval actual_ods synth_ods)
"ods_occur"          .3891718 .02 977 795
"male_ods_occur"     .6172195 .02 704 560
"white_ods_occur"    .5928732 .02 873 634
"female_ods_occur"    .219206 .16 273 222
"nonwhite_ods_occur" -.280511  .2 104 122
end

I am trying the following code, but have not been successful:

* Save values to locals
local dvs "ods_occur male_ods_occur female_ods_occur white_ods_occur nonwhite_ods_occur"
local stats "att pval actual_ods synth_ods"
foreach dv in `dvs' {
    foreach stat in `stats' {
        summ `stat' if dv == "`dv'"
        local `dv'_`stat' = r(mean)
        di `dv'_`stat'
    }
}

I think I have a syntax error when I'm defining the local, but I'm not sure how to fix it. Any help would be appreciated!

[Clyde Schechter]

I think I have a syntax error when I'm defining the local

No, actually the syntax error is with the -di- command. It should be -di ``dv'_`stat''- The inner macro quotes create the name of the local macro, but then that name itself must be macro-quoted in order to access its value.

[Abdullah Algarni]

I've never used putexcel myself, so may not say more than this.

Dear Nick Cox
I used to print out tables using -putexcel- command then copy them to word (For example, Table 1 in randomized controlled trials or cohort studies). Think this is more convenient than -putdocs- or new -table- command. However, I admit it requires long code (may be bcz I'm just beginner in coding).

Do you think that -putexcel- command less efficient than other community- contributed commands? If so, what's your advice with this regard?


Thanks

[Clyde Schechter]

Well, everybody is entitled to his or her own preferences on how to do things. But, despite having a somewhat steep learning curve, once you are familiar with its basics, the new -table- command is actually, in my view, a very easy and powerful way of generating a Table 1. And from there it takes just one line with -collect export- to send it to Microsoft Word or any number of other common vehicles for tables. I think it beats -putexcel-, -putdocx-, and various user-written table-1 type programs hands down.

[Nick Cox]

#6 quotes me on 16 Sept 2019 but my position hasn't changed that I've noticed. I don't recall using putexcel much if at all, or whatever community-contributed commands may be equivalent, so I don't have any opinion to give here.