Foreach

[jykim]

Dear Experts

This would be very basic question but I have been struggling with foreach for several days and would love to get support from you.
I tried to look up answers and advices but most of them are referring to file names with different numbers.
For example, codes were forevalues n = 2000/2015

In my case, I saved csv data set using different names as follows.
TB_coepidemics of TB and HIV_country_WHO
TB_coepidemics of TB and HIV_income groups_WHO
TB_coepidemics of TB and HIV_region_WHO
TB_drug resistant_country_WHO
TB_drug resistant_income groups_WHO
TB_drug resistant_region_WHO
TB_incidence_country_WHO
TB_incidence_income groups_WHO
TB_incidence_region_WHO
TB_new case_country_WHO
TB_new case_income groups_WHO
TB_new case_region_WHO
.....

1. My first task is to import each csv file and save them as dta
I used the below codes. If i don't use substr, the file name was saved .......csv.dta. But I would like to keep only dta.

global FOLDER `"TB/Data/STATA"'

local csvdir: dir "TB/Data/CSV" files "TB_*.csv"

foreach file in `csvdir' {

insheet using `file', firstrow, clear

local filename = "substr("`file'", -4, "")"

save `"$FOLDER/`filename'.dta"', replace

}


2. My 2nd task would be
appending 3 files in the same group.
For example,
1 TB_coepidemics of TB and HIV_all file for

TB_coepidemics of TB and HIV_country_WHO
TB_coepidemics of TB and HIV_income groups_WHO
TB_coepidemics of TB and HIV_region_WHO

1 TB_drug resistant_all file for

TB_drug resistant_country_WHO
TB_drug resistant_income groups_WHO
TB_drug resistant_region_WHO


3. Then merge these appended file using variables country year


Could you please advise me how I can do this repetitive work using foreach?

Thank you so much.

[Nick Cox]

I can't test anything here as I can't access your files, but

local filename = "substr("`file'", -4, "")"

is illegal and otherwise surely not what you want:

local filename = substr("`file'", -4, .)

would be legal, but I think you want to strip ".csv", not select it!

That would be

local filename = substr("`file'", 1, length("`file'") - 4)

[jykim]

Dear Nick
Yes. That's what I want.
I ran with your codes
local csvdir: dir "/Users/jungyeonkim/Documents/OneDrive/Documents/Harvard/Research/Decision Modeling_HepC_TB/TB/Data/CSV" files "TB_*.csv"

foreach file in `csvdir' {

insheet using `file', firstrow, clear

local filename = substr("`file'", 1, length("`file'") - 4)

save `"$FOLDER/`filename'.dta"', replace

}


but nothing happened. Is there anything else that I have to revise?

[Nick Cox]

Sorry, but I can't add to my previous answer. It would be best to run this loop in the directory with the *.csv files. I can't tell if you are doing that.

[jykim]

Thank you so much Nick.
To run this loop in the same directory, I ran the codes without global macro.
Then I got the error

invalid of

Do you know what this error means?


. local csvdir: dir "TB/Data/CSV" files "TB_*.csv"

. foreach file in `csvdir' {
2. insheet using `file', firstrow, clear
3. local filename = substr("`file'", 1, length("`file'") - 4)
4. save "`filename'.dta", replace
5. }
invalid 'of'
r(198);

end of do-file


Thank you so much.

[Clyde Schechter]

I think the problem is with -foreach file in `csvdir'-. You defined csvdir in the line before that to be the name of the directory containing the files. But for that loop to work, it would need to be a list of the files in that directory. So something like this:

local csvdir: dir "/Users/jungyeonkim/Documents/OneDrive/Documents/Harvard/Research/Decision Modeling_HepC_TB/TB/Data/CSV" files "TB_*.csv"

local csvdir_files: dir `"`csvdir'"' files "*.csv"

foreach file of local csvdir_files {
    insheet using `file', firstrow clear
    local filename = substr("`file'", 1, length("`file'") - 4)
    save `"$FOLDER/`filename'.dta"', replace
}

Note: If you are using current Stata, -insheet- has been replaced by -import delimited-. If you are not using current Stata, you should say what version of Stata you are using when you post. Also note that your -insheet- command had an extra comma between firstrow and clear, which would lead to a syntax error.

In the future please post your example code (or Stata output) within code delimiters, as I have done above, so that it aligns neatly and is easy to read. For instructions on the use of code delimiters see #12 in the FAQ.

[Nick Cox]

In addition to Clive's key points, the "of" is objected to because you are not using " " to indicate a filename including spaces.

 
 insheet using "`file'", firstrow clear

The first file is evidently

TB_coepidemics of TB and HIV_country_WHO

and Stata doesn't know what "of" means because your syntax implies that the file is "TB_coepidemics".

[jykim]

Thank you Clyde and Nick.
I changed my codes as below.

 local csvdir: dir "TB/Data/CSV" files "TB_*.csv"  
local csvdir_files: dir `"`csvdir'"' files "*.csv"  
foreach file of local csvdir_files {    
insheet using "`file'", names clear    
local filename = substr("`file'", 1, length("`file'") - 4)    
save `"$FOLDER/`filename'.dta"', replace }

But now I receive the error message as below.

local csvdir_files: dir `"`csvdir'"' files "*.csv"
invalid syntax
I also changed the firstrow option to names, as the stata says firstrow is not vaild option.
I am using Stata 12.1.

I really hope to solve this issue.

Thank you.
Best, Jungyeon

[Clyde Schechter]

You need to stop rushing and think about what you are doing here. In particular, before you just run the code we give you, read it down and understand how it works. You have made additional changes beyond those recommended, and the additional changes you made don't make sense. They suggest you don't grasp what each line of the code means. The first and second lines in #6 do different things: the first defines local csvdir as the name of the directory where your files are located. The second creates a list of the names of all .csv files contained in that.

Look at the first line of your code in #8, and compare it to the first line in #6. With the changes you have made in #8, you have defined local csvdir as the string

dir "TB/Data/CSV" files "TB_*.csv"

which is not the name of a directory. So when your second command in #8 is run, Stata finds this string in a place where it expects to find the name of a directory and complains that your syntax is invalid.