csv import loop error

[Patrick Mauder]

Hi there,

I am trying to import 75 csv files each containing daily company data for 18 months, when I run the script I get an error message saying "using required". Any help would be appreciated, here is the script:


. global MY_PATH "C:\Users\pmauder\Work Folders\Documents"

. local files : dir "$MY_PATH\csv" files "*.csv"

. cd "$MY_PATH\csv"
C:\Users\patrick\Work Folders\Documents\csv

. foreach file in `files' {
2. import delimited `file', clear
3. gen security_id = upper(substr("`file'",1,3))
4. gen date_new = date(date, "YMD")
5. replace date_new = date(date,"DMY") if date_new==.
6. replace date_new = date(date,"MDY") if date_new==.
7. format date_new %td
8. drop date
9. rename date_new date
10. recast float px_open
11. recast float px_high
12. recast float px_low
13. recast float px_last
14. rename px_open open
15. rename px_high high
16. rename px_low low
17. rename px_last close
18. keep security_id date open high low close
19. drop if date < date("20160601","YMD")
20. save temp, replace
21. use "$MY_PATH\csv\security_returns_file", clear
22. append using temp
23. save, replace
24. }
using required
r(100);

Thanks,

Patrick

Stata/MP 14.2

[William Lisowski]

The following

. import delimited, clear
using required
r(100);

suggests to me that the local macro file in the import delimited command is not being set correctly, so that the command being executed after macro substitution lacks a filename.

The first step to debugging this is to use trace (see help trace) to see what your commands look like after macro substitution is done.

[Chris Larkin]

I would try separating the import + save loop from the append loop.

So, something like the below should work. NB: i have not tried this in Stata so it may be a bit buggy

global MY_PATH "C:\Users\pmauder\Work Folders\Documents"

local files : dir "$MY_PATH\csv" files "*.csv"
cd "$MY_PATH\csv"

foreach file in `files' {
import delimited `file', clear
gen security_id = upper(substr("`file'",1,3))
gen date_new = date(date, "YMD")
replace date_new = date(date,"DMY") if date_new==.
replace date_new = date(date,"MDY") if date_new==.
format date_new %td
drop date
rename date_new date
recast float px_open
recast float px_high
recast float px_low
recast float px_last
rename px_open open //not sure if you care, but --rename-- accepts varlist, not just varname
rename px_high high
rename px_low low
rename px_last close
keep security_id date open high low close
drop if date < date("20160601","YMD")
save "`file'".dta, replace
}

use "$MY_PATH\csv\DatasetN", clear //use the last dataset listed when you call the local files. Try displaying them within the loop

local files : dir "$MY_PATH\csv" files "*.dta"
foreach file in `files' {
if "`file'"!="DatasetN"{
append using `file'
save, replace
}
}

I'm sure there are better ways to do this too - good luck!

[Marcos Almeida]

Hello Patrick "GC",

Please act on the advice given here (http://www.statalist.org/forums/foru...mand-delimited). Particularly, message #8.

Thanks.

[Patrick Mauder]

Thank you everyone, your help is really appreciated!!!

[William Lisowski]

Patrick -

From the Statalist FAQ linked to from the top of the page,

16. Should I close threads I start?

Closing a thread you started is important, especially by reporting what solved your problem. You can then thank those who tried to help.

It would be helpful to us, and to later readers who find this thread by searching Statalist for help with a similar problem they are having, to know what the solution was.

[Patrick Mauder]

Hi So I tried to run the scripts and i still get the same error message. I have done the csv data merge manually now and am trying to run eventstudy2. I have saved the security_returns_file, marketfile and eventfile into the working directory, yet somehow it seems not to find a variable which is in the marketfile (called marketref).

Sorry to keep asking questions which are most likely due to me not understanding stata enough.

Patrick Mauder

Here is what I get:


. eventstudy2 security_id date using security_returns_file, returns(return) mode
> l(FM) marketfile(marketfile) mar(market) idmar(marketref) factor1(smb) factor2
> (hml) risk(rf)
Generating dateline ...
...succeeded
Preparation of event list ...
...succeeded
Preparation of security return data...
...succeeded
Preparation of market and/or factor return data...
...succeeded
variable marketref not found
You specified the keep(varlist) option. The above error was the result of
parsing the list of variables you supplied against the using data.
r(111);

[Joseph Coveney]

What's eventstudy2? Try

use marketfile, clear
describe marketref
// or maybe
isid marketref

[Patrick Mauder]

eventstudy2 is written to calculate abnormal returns using various methods, I have the raw shareprice in one file and risk free rate, event windows in seperate files and that formula is basically extracting the data it needs to do the calculation, it was recommended by a friend
somehow the sript works on her machine but just not on mine

[William Lisowski]

Do you realize that readers who know something about eventstudy2 will not necessarily be looking at a topic titled "csv loop error"? When you have a new question you should start a new topic with an appropriate subject.

Do review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

Also, please take the time to click "Contact Us" in the lower right corner below and request your registration name be changed to include the surname you shared in #7 above.

And, with all that said, perhaps the reason you were not able to get the script in #1 to run successfully is that while you had the entire script in your do-file editor window, you selected the first three commands and ran them separately, then selected the loop and ran it. That will not work. If you look carefully at your results window, you'll see that each group of selected lines are copied into a temporary do-file and run, so even though all the commands are in the same window in the do-file editor, they are run as separate do-files. And local variables vanish at the conclusion of the do-file within which they are created, so `files' will not exist when the loop is run, which leads to the error you received.

[Patrick Mauder]

Thanks William, I will work on my forum questions and get my name changed.

[Patrick Mauder]

Hi there,

I managed to get the loop to work. I learnt my lesson as the key reason was that we had not saved the do file after making changes and only notice that when going through it step by step.

Thanks for your help and I would appreciate if you could tell me how to close a topic?

Kind Regards,

Patrick

[Nick Cox]

Closing a topic here means reporting on solutions, so you just did that. But no-one owns a thread, not even the person who started it, so it's also true that all threads remain open indefinitely in case someone wants to add more.