Data entry to STATA fails / error varlist required

[Maxi Koenig]

Hello.
I am currently working on my bachelor thesis where I am performing a regression analysis on divdend policy and board characteristics.
I have my data in an Excel file and already wrote the code to build my panel data set and for both a summary table and correlation matrix.
Everytime I run the code, Stata returns the error code varlist required (r100).
I would be thankful for any advice on how to fix this, as I unfortunately couldn´t figure it out myself.

local sheets "Dividend DPS DY Board_Meetings CEO_Separation Board_Size Board_Independence Board_Diversity Board_Tenure Total_Assets Net_Income Total_Equity Total_Liabilities Cash_Holdings Capital_Expenditures Market_Value RnD Total_Sales"
foreach sheet in `sheets' {
import excel "Datensatz_Kopie.xlsx", sheet("`sheet'") firstrow clear
rename ISIN year
label variable year "year"
noi ds year, not
foreach var in `r(varlist)' {
noi destring `var', replace force
noi capture assert mi(`var')
if !_rc {
drop `var'
}
}
save "tmp_Balance_Sheet_`sheet'.dta", replace
}
local sheets "Dividend DPS DY Board_Meetings CEO_Separation Board_Size Board_Independence Board_Diversity Board_Tenure Total_Assets Net_Income Total_Equity Total_Liabilities Cash_Holdings Capital_Expenditures Market_Value RnD Total_Sales"
foreach sheet in `sheets' {
use "tmp_Balance_Sheet_`sheet'.dta", clear
noi ds year, not
foreach var of varlist `r(varlist)' {
use "tmp_Balance_Sheet_`sheet'.dta", clear
keep year `var'
rename `var' `sheet'`var'
gen byte OK = !missing(`sheet'`var')
drop if OK == 0
drop OK
capture noisily reshape long `sheet', i(year) j(ISIN) string
save "tmp1_`sheet'`var'.dta", replace
}
local datafiles : dir . files "tmp1_`sheet'*.dta"
noi di `datafiles'
local firstfile: word 1 of `datafiles'
noi di "`firstfile'"
use "`firstfile'", clear
sort year ISIN
foreach datafile in `datafiles' {
if "`datafile'" ~= "`firstfile'" {
merge 1:1 year ISIN using `datafile', nogen
sort ISIN
erase "`datafile'"
}
}
erase "`firstfile'"
sort ISIN year
save "Balance_Sheet_`sheet'.dta", replace
}
use "Balance_Sheet_Total_Assets.dta", clear
sort ISIN year
foreach sheet in `sheets' {
noi merge 1:1 ISIN year using "Balance_Sheet_`sheet'.dta", nogen
erase "tmp_Balance_Sheet_`sheet'.dta"
erase "Balance_Sheet_`sheet'.dta"
label variable `sheet' "`sheet'"
sort ISIN year
}
egen long id = group(ISIN)
label variable id "id"
move id ISIN
label variable ISIN "ISIN"
save panel.dta, replace
import excel Datensatz_Kopie.xlsx, sheet("Company_List") firstrow clear
keep Name ISIN
save Company_List.dta, replace
use panel.dta
merge m:1 ISIN ISIN using Company_List.dta, nogen
move Name ISIN
drop if missing(year)
erase Company_List.dta
save panel.dta, replace




*Hier werden alle benötigten Variablen generiert

*Eine binäre Jahresvariable
tab year, gen(year_)

*Abhängige Variablen für primäres und sekundäres Modell

*Dividend Payout Ratio
gen DPR = Dividend/Net_Income
*Dividend-to-Assets Ratio
gen DAR = Dividend/Total_Assets
*Dividend-to-Sales Ratio
gen DSR = Dividend/Total_Sales

*Kontrollvariablen

*Unternehmensgröße: Natürlicher Logarithmus der Total Assets
gen Size_ln = ln(Total_Assets)
*Profitabilität: ROA
gen ROA_TA = Net_Income/Total_Assets
*Leverage
gen Lev_TA = Total_Liabilities/Total_Assets
*Cash Holdings im Verhältnis zu Total Assets
gen CH_TA = Cash_Holdings/Total_Assets
*Capital Expenditures im Verhältnis zu Total Assets
gen CAPEX_TA = Capital_Expenditures/Total_Assets
*Market-to-Book Ratio
gen MBR = Market_Value/Total_Equity
*Research and Development im Verhältnis zu Total Assets
replace RnD = 0 if missing(RnD)
gen RnD_TA = RnD/Total_Assets
*Dummy-variable für Verlust gemessen an Net Income
gen Loss_Dummy = 0 if Net_Income =< 0
replace Loss_Dummy = 1 if Net_Income > 0

save panel.dta, replace

************************************************** ******************************

*Für einen ersten Überblick über den Datensatz verwende ich den summarize Befehl
summarize DPR Board_Meetings CEO_Separation Board_Size Board_Independence Board_Diversity Board_Tenure Size_ln ROA_TA Lev_TA CH_TA CAPEX_TA MBR RnD_TA Loss_Dummy
summarize DPR Board_Meetings CEO_Separation Board_Size Board_Independence Board_Diversity Board_Tenure Size_ln ROA_TA Lev_TA CH_TA CAPEX_TA MBR RnD_TA Loss_Dummy, detail

*Die folgenden Befehle generieren eine Summary-Tabelle und eine Korrelationsmatrix für unsere Variablen.
ssc install asdoc
asdoc pwcorr DPR Board_Meetings CEO_Separation Board_Size Board_Independence Board_Diversity Board_Tenure Size_ln ROA_TA Lev_TA CH_TA CAPEX_TA MBR RnD_TA Loss_Dummy, replace star(0.05) label save(C:\Users\maxi2\Desktop\Bachelorarbeit\Stata\K orrelation.doc) dec(2) label replace
asdoc tabstat DPR Board_Meetings CEO_Separation Board_Size Board_Independence Board_Diversity Board_Tenure Size_ln ROA_TA Lev_TA CH_TA CAPEX_TA MBR RnD_TA Loss_Dummy, stat(mean median sd min max count) save(C:\Users\maxi2\Desktop\Bachelorarbeit\Stata\S ummary.doc) dec(2) label replace

[Clyde Schechter]

You've posted very lengthy code. It is difficult to read, in part because you did not wrap it in code delimiters when posting it here. Perhaps somebody can read it down and spot the problem, but if you don't get a response in the next several hours, I recommend you increase your chances of getting help by adding

set tracedepth 1
set trace on

to the very beginning of the code, and run it again.

You will get a lot of additional output. The output will now at least show you which command is triggering that error message. Then you can focus your efforts on what is going wrong there. Given the nature of the message, the most likely possibility is that you are trying to run some command that requires a variable list somewhere in its syntax, and you didn't specify one, or, it is specified through the use of some local macros and when the macros are expanded, they turn out to be empty, or contain something that isn't a possible part of a variable list. With these trace commands you will be able to see what your local macros actually expand to at run time. In most of these "varlist required" situations, this approach provides enough information to identify and fix the problem.

If you are still unable to figure out the source of the problem, then post back. When you do post back, this time post the code between code delimiters to enhance its readability. Also post the part of the output that includes the error message and commands leading up to it.

[Maxi Koenig]

Thank you for your response. I did as you told me to and I got the following:

= erase "tmp1_board_meetingsus87162m4096.dta"
- }
- }
- erase "`firstfile'"
= erase "tmp1_board_meetingsat0000720008.dta"
- sort ISIN year
- save "Balance_Sheet_`sheet'.dta", replace
= save "Balance_Sheet_Board_Meetings.dta", replace
file Balance_Sheet_Board_Meetings.dta saved
- }
- use "tmp_Balance_Sheet_`sheet'.dta", clear
= use "tmp_Balance_Sheet_CEO_Separation.dta", clear
- noi ds year, not
  ---------------------------------------------------------------- begin ds ---
  - version 9
  - local version : di "version " string(_caller()) ":"
  - if (_caller() <= 4) {
    `version' describe, simple
    exit
    }
  - else {
  - ds_util `0'
  = ds_util year, not
  - exit
  ------------------------------------------------------------------ end ds ---
- foreach var of varlist `r(varlist)' {
= foreach var of varlist  {
varlist required
  use "tmp_Balance_Sheet_`sheet'.dta", clear
  keep year `var'
  rename `var' `sheet'`var'
  gen byte OK = !missing(`sheet'`var')
  drop if OK == 0
  drop OK
  capture noisily reshape long `sheet', i(year) j(ISIN) string
  save "tmp1_`sheet'`var'.dta", replace
  }
r(100);

end of do-file

r(100);

This is the part that includes the error message.

That Varlist should´ve been "build" with this code:

local sheets "Dividend DPS DY Board_Meetings CEO_Separation Board_Size Board_Independence Board_Diversity Board_Tenure Total_Assets Net_Income Total_Equity Total_Liabilities Cash_Holdings Capital_Expenditures Market_Value RnD Total_Sales"
foreach sheet in `sheets' {
    import excel "Datensatz_Kopie.xlsx", sheet("`sheet'") firstrow clear
    rename ISIN year
    label variable year "year"
    noi ds year, not
    foreach var in `r(varlist)' {
        noi destring `var', replace force
        noi capture assert mi(`var')
        if !_rc {
            drop `var'
        }
    }
    save "tmp_Balance_Sheet_`sheet'.dta", replace

[Clyde Schechter]

So just after executing -use "tmp_Balance_Sheet_CEO_Separation.dta", clear-, you run -noi ds year, not-, and this is where things go wrong. Notice that you get no output from this command. That means, that Stata found no variables other than year in file "tmp_Balance_Sheet_CEO_Separation.dta". Consequently `r(varlist)', which is supposed to be the variable list in the subsequent -foreach var of `r(varlist)' {- command is just an empty string. Stata sees this command as -foreach var of {-, and, appropriately, notes that there is no variable list supplied.

So, the difficulty arises because file tmp_Balance_Sheet_CEO_Separation.dta has no variables in it other than year. (Maybe it doesn't have year either, but that's not important at this point.) Looking back at the larger code in #1, this file is one that your code creates early on:

foreach sheet in `sheets' {
    import excel "Datensatz_Kopie.xlsx", sheet("`sheet'") firstrow clear
    rename ISIN year
    label variable year "year"
    noi ds year, not
    foreach var in `r(varlist)' {
        noi destring `var', replace force
        noi capture assert mi(`var')
        if !_rc {
            drop `var'
        }
    }
    save "tmp_Balance_Sheet_`sheet'.dta", replace
}

CEO_Separation is one of the tokens in local macro sheets, so when we get `sheet' set to CEO_Separation, we are reading in sheet CEO_Separation from Datensatz_Kopie.xlsx, we rename ISIN year, and then we identify all variables other than year in the data set. And for each of those we destring it, and then check whether it contains exclusively missing values. If so, the variable is dropped. My conclusion* is that if you look at that CEO_Separation sheet in Datensatz_Kopie.xlsx, you will find that its variable CEO_Separation contains either all missing values to start with, or, whatever non-missing values it does contain could not be properly converted to numeric data by -destring-. (When -destring- encounters a string that cannot be made numeric, it returns a missing value.)

In short, I think your code has encountered a situation it was not prepared to deal with. Whether this is because you did not understand that this CEO_Separation variable would contain only non-numeric information, or because the Datensatz_Kopie.xlsx spreadsheet wrongly contains non-numeric information for CEO_Separation is the next question you must pursue. If the latter, that spreadsheet will have to be re-created with correct data. If the former, you will have to rethink your approach on how to handle this situation. (And bear in mind that the same thing might happen with one of the other variables mentioned in local macro sheets.)

*Added: If on investigation you find my conclusion is incorrect, look further down in the code to see if any further manipulations of that tmp_Balance_Sheet_CEO_Separation.dta file may have created the situation whereby that CEO_Separation variable was obliterated.