Loop fails to find local macro

[Michele Scotto]

Hi there,

I have an issue with one local macro within a fovalues loop.

The main purpose of this code is to look for xlsx files in a directory, open them one at a time (each xlsx is a 'policy'), loop over its sheets (each sheet has data on that policy for a specific country), use both the name of the xlsx and of the sheet to find a corresponding csv, do some manipulations and save a new xlsx named with the name of the original xlsx (the policy) and with sheets with country names (appending sheets to the original xlsx as the loop goes on).

In particular, in fact, the loop seemed to work at the beginning (it displayed all the locals for all sheets within all xlsxs) but when I inserted the commands I need (see the bold comment in the code) at the end of the first iteration it fails to find the local `country' and gives me the error: "file Summary/_Mood.csv not found (Mood is the local `policy')". Any idea about what I did wrong? Please find the code below.

cd "C:/Users/miche/Opinion Data"
* Get the names of all the xlsx files in the directory (each file refers to a policy area)
local filelist : dir . files "*.xlsx"

* Loop over each file
foreach file in `filelist'{

* Remove file extension to get the name of the policy area
local p = strpos("`file'",".")
local policy = substr("`file'",1,`p'-1)

* Open the xlsx of a specific policy area
import excel using "`policy'", describe

* Within each file (policy), loop over the worksheets (each ws is a country)
forvalues s = 1/`r(N_worksheet)'{
local country "`r(worksheet_`s')'"
di "`policy'"
di "`country'"
import delimited "Summary/`country'_`policy'.csv", delimiter(",") varnames(1) clear
rename v1 _varname
rename v2 obs
rename v3 loading
rename v4 mean
rename v5 sd

* generate actual values of mean and sd to impose on mood, weightig by validity and number of observations
g wt_mean = (loading^2)*mean*obs

* LOOP RUNS SMOOTHLY IF I INCLUDE ONLY WHAT IS ABOVE THIS COMMENT. 
egen denom_mean = total(wt_mean)
g val_obs = (loading^2)*obs
egen numerator = total(val_obs)
g mood_mean = denom_mean/numerator
g wt_sd = (loading^2)*sd*obs
egen denom_sd = total(wt_sd)
g mood_sd = denom_sd/numerator

scalar new_mean = mood_mean[1]
scalar new_sd = mood_sd[1]

drop wt_* denom_* numerator

* Import mood estimates not weighetd for observations
capture noisily import excel using "`policy'.xlsx", sheet("`country'") firstrow clear
if !_rc{
g ym = ym(year, month)

qui sum mood
* Standardize original series
qui replace mood = (mood - `r(mean)')/`r(sd)'

* Impose new mean and sd in order to get mood weighted for item validity and number of observations
qui replace mood = mood*new_sd
qui replace mood = mood + new_mean
scalar drop new_mean new_sd

g yq = qofd(dofm(ym))
collapse (mean) mood, by (yq)
g country = "`country'"
order yq mood

export excel "ObsWeighted/`policy'_ObsW.xlsx", sheet("`country'") firstrow(variables) sheetmodify
}
}
}

[Nick Cox]

Locals are local to the locale, meaning each program, do file, do file editor contents or interactive session. So, you must run all the code, or find out about include -- otherwise the locals just are not visible.

[daniel klein]

My guess is that r() results get messed up somewhere in the code, presumably by the (recently changed behavior of the) drop command. Thus,

`r(worksheet_`s')'

is no longer available after drop. You either need to store the names of the worksheets before you enter the inner loop or read help _return.

As an aside: the code is very hard to read because there is no indention; it is hard to tell which of the three consecutive close braces refer to which parts of the code. I would code this as

foreach ... {
    forvalues ... {
        if ... {
    
        }
    }
}

Best
Daniel

[Michele Scotto]

Dear both,

thanks a lot for your suggestions. Let me just be a bit more precise as I think my problem might be solved differently.

The code below, with some lines commented, works. It recalls all the locals in all the loops and creates the final xlsx as I want it. The thing is that I want it also to execute the commented lines but when uncommented the loop gives me the mentioned error (local `country' is not found and, as consequence, it tries to import a non existent file).

What can explain this different behaviour? As you can see there are no drop commands now and this is the whole do file. Furthermore, for instance, if I uncomment, say, 'qui sum mood' in the innermost loop I got the same error again as if the sum (or similarly the egen command above) is the root of the problem.

cd "C:/Users/miche/Opinion Data"
* Get the names of all the xlsx files in the directory (each file refers to a policy area)
local filelist : dir . files "*.xlsx"
* Loop over each file
foreach file in `filelist'{
    * Remove file extension to get the name of the policy area
    local p = strpos("`file'",".")
    local policy = substr("`file'",1,`p'-1)
    * Open the xlsx of a specific policy area
    import excel using "`policy'", describe
    * Within each file (policy), loop over the worksheets (each ws is a country)
    forvalues s = 1/`r(N_worksheet)'{
        local country "`r(worksheet_`s')'"
        di "`policy'"
        di "`country'"
        import delimited "Summary/`country'_`policy'.csv", delimiter(",") varnames(1) clear
        rename v1 _varname
        rename v2 obs
        rename v3 loading
        rename v4 mean
        rename v5 sd

        * Generate actual values of mean and sd to impose on mood, weightig by validity and number of observations
        g wt_mean = (loading^2)*mean*obs
        *egen denom_mean = total(wt_mean)
        g val_obs = (loading^2)*obs
        *egen numerator = total(val_obs)
        *g mood_mean = denom_mean/numerator
        g wt_sd = (loading^2)*sd*obs
        *egen denom_sd = total(wt_sd)
        *g mood_sd = denom_sd/numerator

        *scalar new_mean = mood_mean[1]
        *scalar new_sd = mood_sd[1]

        * Import mood estimated but not weighetd for observations
        capture noisily import excel using "`policy'.xlsx", sheet("`country'") firstrow clear
        if !_rc{
            g ym = ym(year, month)

            * Standardize
            *qui sum mood
            *qui replace mood = (mood - `r(mean)')/`r(sd)'

            * Impose new mean and sd in order to get mood weighted for item validity and number of observations
            *qui replace mood = mood*new_sd
            *qui replace mood = mood + new_mean

            *scalar drop new_mean new_sd old_mean old_sd

            g yq = qofd(dofm(ym))
            *collapse (mean) mood, by (yq)
            g country = "`country'"
            order yq mood

            export excel "ObsWeighted/`policy'_ObsW.xlsx", sheet("`country'") firstrow(variables) sheetmodify
        }
    }
}

[daniel klein]

drop was what caught my eye when skimming through the code the first time. You have identified (at least) one source of the problem: sum (short for summarize). You are even referring to the r() results returned by summarize! Those results wipe what import excel has left there before.

As I have noted in #3 you will have to store all worksheet names when you first obtain them outside of r(). One way of doing this is

import excel using "`policy'", describe
// grab r()
local N_worksheets = r(N_worksheets)
forvalues i = 1/`N_worksheets' {
    local conutry`i' "`r(worksheet_`i')'"
}
// now loop over the worksheets again
forvalues s = 1/`N_worksheets' {
    ... `country`s'' // <- edit here: use nested quotes
}

In general, whenever you need r() results for more than the very next line after obtaining them, copy those results to local macros (or scalars or matrices where applicable). There are just too many commands that will wipe r(). Many commands do not themselves return any results in r(); yet, they still might call other commands that do so.

Best
Daniel

[Michele Scotto]

Dear Daniel,

thank you very much again for your prompt reply. Indeed you are right, those lines solve my problem! Thank you!

Best,
Michele