1

Here is the code I have:

local dateList "01" "02" "03" "04" "05" "06" "07" 

foreach date of local dateList {
  use `"`date'"'

  clear

  import excel "V:\Report07" + `"`date'"' + "13.xls", sheet("MySheet") firstrow

  sort PersonID Place
  bysort PersonID (Place): gen mix = Place[1] != Place[_n]
  sort PersonID
  by PersonID: egen anymix=max(mix)

  count if anymix==1

  drop mix
  drop anymix
}

I am trying to loop through multiple Excel files that differ by date, as you can see in the code where I put the variable date. For example, the name of this spreadsheet would be Report070113, representing the date July 1, 2013. The next time through the loop should import the report titled Report070213. I thought that the best way to do this would be to make an array of strings that are the various dates of the month so I can run the code month by month and get the count of each person who has visited different places. I know what is inside the loop works fine, but I am having trouble with the for loop itself. When I have:

use `"`date'"'

in the code (line 3) it gives me the following error:

file 01".dta not found

But when I don't include that line, it gives me this error:

using required

Any help would be appreciated; please let me know if my question is unclear.

Improve this question

3 Answers 3

Reset to default
1

See the difference between using single quotes and double quotes:

clear all
set more off

local dateList "01" "02" "03" "04" "05" "06" "07" 

foreach date of local dateList {
  disp `"`date'"'
  disp "`date'"
}

The problem is that the first element of the macro list is 01" and not 01. So Stata can't find the file. This is due to the way in which you declare the macro list and then call with double quotes. help quotes is a pertinent read here.

To me, it becomes clearer doing it like this:

clear all
set more off

local dateList 01 02 03 04 05 06 07

foreach date of local dateList {
  disp `"`date'"'
  disp "`date'"  
  display "V:/Report07`date'13.xls"

  * This should work for you (uncomment)
  *import excel "V:/Report07`date'13.xls", sheet("MySheet") firstrow
}

Note the declaration of the macro contains no quotes. Calls with double or single quotes now give the same results. Creating the string for the source file is straightforward. Also, the recommendation is to use / and not \. This makes the code compatible across other operating systems. Stata will make it work for MS Windows, inclusive. A reference is Stata tip 65: Beware the backstabbing backslash by Nicholas J. Cox.

As for the using required error, my guess is this has to do with import excel expecting a string and not an algebraic expression. Adding a simple + to the command will reproduce your error. For example:

.   import excel "V:/Report07" +
using required
r(100);

Stata doesn't understand the symbol, so it complains; it wants some string representing a file path. (Something different occurs if you run import excel "V:/Report07 +", instead.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Try modifying your "import excel" filepath:

local dateList "01" "02" "03" "04" "05" "06" "07" 

foreach date of local dateList {

  clear

  import excel "V:\Report07\`date'13.xls", sheet("MySheet") firstrow

  sort PersonID Place
  bysort PersonID (Place): gen mix = Place[1] != Place[_n]
  sort PersonID
  by PersonID: egen anymix=max(mix)

  count if anymix==1

  drop mix
  drop anymix

}

If it still asks for "using required", then try "import excel using...".

Improve this answer

1 Comment

Backslashes before backquotes are a bad idea in Stata as the use of backslash as an escape character clashes with its use as a separator in Windows filepaths. Use a forward slash within Windows filepaths; Stata will translate for you. Documented in manuals and at stata-journal.com/sjpdf.html?articlenum=pr0042
0

I know this is 3 years old, but I just had the same issue (getting the "using required" error) as I tried to loop over opening and modifying several Excel files. Changing the direction of the slash in my directory name fixed the issue. 

local yearlist 2014 2015

foreach year of local yearlist { 

        import....
        [do data cleaning]
        save update file
}

Original (failing) import command: 

 import excel "Z:\Data\`year'\GIS Class Data Report1_`year'.xlsx", sheet("Sheet1") firstrow clear

New (working) import command: 

 import excel "Z:/Data/`year'/GIS Class Data Report1_`year'.xlsx", sheet("Sheet1") firstrow clear

I had not been aware of the backslash issue.

Improve this answer

1 Comment

The forum software ate the backslash that you needed to show to explain the problem you solved. Now fixed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.