Calling a program inside a loop

[Adriano Mariani]

Dear All,

I have been trying to call a certain program inside a loop. (I have placed it outside the loop as placing it inside caused problems)

Code:

//program drop name
program name
drop `2'
end

clear
local myfilelist : dir "/Users/adrianomariani/Desktop/Research Assistant scheme/Forecasts excel" files "*.xlsx"

foreach filename of local myfilelist {

.....

findname instit , not local(g)
name `g'
program drop name

......

}

When I try to run this loop, it stops at the first called program: 'name', it executes the command (ie drops the variable in the second position of the list of variables provided by 'findname' and placed in local macro 'g') however nonetheless it runs it, it stops the loop with the error message: r(111)-"variable E not found", which is the variable that was successfully dropped.
I have checked there may be a problem with the ordering of the variables, but this is not the case (and even if there was it should drop anyways the second variable in the list provided by the local)

If I try to run this outside of a loop, ie if I do something like:

Code:
//program drop name
program name
drop `2'
end

findname instit , not local(g)
name `g'
program drop name

It returns no problems, drops the variable and returns no error message. Thus I believe this specific problem is when the program is called within a loop.

Any suggestions would be largely appreciated.

Thanks to all

[Sven-Kristjan Bormann]

You should probably show us the full code in the loop. Otherwise it will be difficult to spot the error. The code that you posted will only run once because you work only on one dataset for which a variable can only be dropped once.

[Adriano Mariani]

Dear Sven, thank you for your answer.

The loop here is over files so it should do this once for each file, not on the same file twice, but you are right it seems it is trying to drop it again after having dropped it the first time.

Anyways, as anticipated, it does not reach the second iteration as the loop stops before even completing the first, due to this error, thus I don't get why it would behave in this way of trying to drop it twice.

I will add the whole loop, but I don't believe it has to do with the error as everything before and after the program command runs as it should:


Code:

//program drop name
program name
drop `2'
end

//program drop name1
program name1
rename `4' instit
end

clear
local myfilelist : dir "/Users/adrianomariani/Desktop/Research Assistant scheme/Forecasts excel" files "*.xlsx"

foreach filename of local myfilelist {

//Function for cellrange
import excel using `filename', clear

drop in 1/60
gen id=_n

local filename1 = substr("`filename'",1,9)

foreach tbl in 1 4 M1 {
local len = length("`tbl'") + 6
qui summ id if substr(A,1,`len') == "Table `tbl'"
assert r(N) > 0. // Check the table name does occur
local start = r(min)
// (Assumes first occurence is the important one)
local len1 = length("Bank of America - Merrill Lynch")
qui sum id if (substr(A,1,`len1')=="Bank of America - Merrill Lynch" | (substr(G,1,16)=="City forecasters" | substr(H,1,16)=="City forecasters")) & id>`start'
assert r(N)>0.
local start1 = r(min)
qui summ id if (substr(A,1,7) == "Average" | (substr(G,1,19)=="Independent average" | substr(H,1,19)=="Independent average")) & id > `start'
assert r(N) > 0
local end = r(min) - 1
preserve
keep in `start1'/`end'
save `filename1'_`tbl', replace
restore
}


use `filename1'_1, clear

keep A B C D E
missings dropvars, force
format A %35s

foreach c of varlist _all {
qui count if inlist(substr(`c', 1, 3),"Jan", "Feb", "Mar", "Apr", "May", "Jun") | inlist(substr(`c', 1, 3), "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

if r(N) > 0 drop `c'
} //dropping the month

gen new=0
foreach c of varlist _all {
qui count if inlist(substr(`c', 1, 1), "*")
if r(N) > 0 {
replace new=1 if `c'=="*"
drop `c'
continue, break
}
} //dropping the new


//assigning names to the variables
rename A instit

foreach var of varlist _all {
local vl = "`vl' `var'"
}
local exclude "instit new"
local vl: list vl - exclude
destring `vl', replace force
rename `vl' GDPt

// Generating a time variable
gen time=substr("`filename'",3,7)
// here i'm gonna call files: F_12/2020.xlsx
replace time= subinstr(time,":","/",.)
move time instit


// Generating the city dummy
g n=_n
local len2 = length("Non-City forecasters")
qui sum n if substr(instit,1,`len2')=="Non-City forecasters"
local noncity = r(min)
gen city =0
replace city=1 if n<`noncity'
drop in `noncity'
drop n

//removing discrepancies w/ MT forecasts
replace instit="EY ITEM Club" if instit=="ITEM Club"

save `filename1'_STF, replace
erase `filename1'_1.dta

// GDPt1 ST:
use `filename1'_4, clear

keep A B C D E
missings dropvars, force
format A %35s
rename A instit

foreach c of varlist _all {
qui count if inlist(substr(`c', 1, 3),"Jan", "Feb", "Mar", "Apr", "May", "Jun") | inlist(substr(`c', 1, 3), "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

if r(N) > 0 drop `c'
} //dropping the month


+++++++++++++++++ Everything up to here works

findname instit , not local(g)
name `g'
program drop name

foreach var of varlist _all {
local vl = "`vl' `var'"
}
local exclude "instit"
local vl: list vl - exclude
destring `vl', replace force
rename `vl' GDPt1

replace instit="EY ITEM Club" if instit=="ITEM Club"

g n=_n
local len2 = length("Non-City forecasters")
qui sum n if substr(instit,1,`len2')=="Non-City forecasters"
local noncity1 = r(min)
drop in `noncity1'
drop n

gen time=substr("`filename'",3,7)
replace time= subinstr(time,":","/",.)
move time instit

save `filename1'_STF1, replace
erase `filename1'_4.dta

use `filename1'_STF, clear
merge 1:1 instit time using `filename1'_STF1
erase `filename1'_STF1.dta
erase `filename1'_STF.dta
drop _m
move GDPt1 new
save `filename1'_ST, replace


//MT:
use `filename1'_M1, clear
drop in 1 //as for MT we imported from City forecasts

//b:
keep C D E G H
missings dropvars, force

rename C GDPt2
rename D GDPt3
rename E GDPt4

//c:
foreach c of varlist _all {
qui count if inlist(substr(`c', 1, 3),"Jan", "Feb", "Mar", "Apr", "May", "Jun") | inlist(substr(`c', 1, 3), "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") | inlist(substr(`c', 1, 1), "*")

if r(N) > 0 drop `c'
} //dropping the month


+++++++++++++++ Here same problem as above


findname _all , local(g4)
name1 `g4'
program drop name1
move instit GDPt2
format instit %35s

//d:
gen time=substr("`filename'",3,7)
replace time= subinstr(time,":","/",.)
move time instit


// the non-city
g n=_n
local len3 = length("Non-City forecasters")
qui sum n if substr(instit,1,`len3')=="Non-City forecasters"
local noncity2 = r(min)
drop in `noncity2'
drop n

//destringing our numerical variables: GDPt1,...,GDPt4
foreach var of varlist _all {
local vl = "`vl' `var'"
}
local exclude "instit time"
local vl: list vl - exclude
destring `vl', replace force

//making it consistent w/ ST forecasts:
replace instit="Experian Economics" if instit=="Experian"
replace instit="EY ITEM Club" if instit=="ITEM Club"

//saving MT ready for merging
save `filename1'_MT, replace
erase `filename1'_M1.dta

// Merging ST and MT:
use `filename1'_ST, clear
merge 1:1 instit time using `filename1'_MT

//returns the problem that 2 obs: 'deutsche bank' and 'cbi', have new ST forecats but not new MT forecats and by merging by 'new' as well as time, instit.

//to get around this we could create new1 in MT forecasts
//or remove new from the identificator variables, which is what we have done


move new GDPt
move city new
drop _m

//saving the merged file and removing from folder the intermediate files for ST and MT
save `filename1'_F, replace
erase `filename1'_ST.dta
erase `filename1'_MT.dta
}

As you can see, the dataset should change throughout the iterations so there should not be this problem (if the whole loop were to run)

[daniel klein]

I find your code hard to read. This is partly because you either do not use indention or because you post code without preserving the formatting. Please use code-delimiters, as I do below, to keep the formatting.

Your code is also hard to read and understand because there are a couple blocks that will either fail or could be simplified to a great extent. For example

foreach var of varlist _all {
    local vl = "`vl' `var'"
}
local exclude "instit new"
local vl: list vl - exclude
destring `vl', replace force
rename `vl' GDPt

will only work if `vl' holds exactly one variable name; otherwise, rename will fail. If `vl' only contains one variable, then code simplifies to

findname instit new , not local(vl)
destring `vl', replace force
rename `vl' GDPt

I have used findname, as you did, and I will explain that I have downloaded it from SSC. Remember to tell us where community-contributed commands come from.

One other thing that I notice is that you will never want

program drop ...

inside a loop. The reason you never want this is that the second time through the loop, name does no longer exist and, thus, Stata will return r(199).

The error message you describe in the initial post might or might not be generated by name. Use

set trace on

to see where exactly things are going wrong.

Best
Daniel

[Adriano Mariani]

Thanks to your detailed answer, I have made the code simpler and more efficient now.

set trace on

Thanks to this command, I understood that there was nothing wrong with calling the program but the subsequent lines of code where the problem (the ones you indicated at the start). Really helpful