Looping command over files

[Pranav Garg]

Hey,

I am new to this community. This is my first ever post. I am dealing with monthly level data between 2007 and 2017 titled: sort_SCR_jan07, sort_SCR_feb07 ... sort_SCR_mar17

All files have the same variable names. I want to drop a few variables from every file, and to run that command in a loop over the entire list of 123 files. All files are in .dta format.

Kindly suggest how I should construct the loop.

Thank you in advance,
Pranav

[daniel klein]

If you have all the files (and no other files starting with sort_SCR) in one directory, you can

cd `"directory_where_files_are_stored"'
local allfiles : dir . files "sort_SCR_*.dta" , respectcase
foreach file of local allfiles {
    use `"`file'"' , clear
    drop ...
    gettoken filename : file , parse(".")
    save `"`filename'_reduced.dta"'
}

Best
Daniel

[Pranav Garg]

Dear Daniel,

Thank you for your response! I managed to work with a longer command:

local year 07 08 09 ... 17
local month jan feb ... dec

foreach y of local year{
foreach m of local month{
use sort_SCR_`m'`y'.dta, clear
drop [my list of variables]
save, replace [#I had created a backup of my files so replace was okay, but I like the addition in your command]
}
}


I would like to try your command too, and I shall do it again just to get familiarity.


Thanks,
Pranav

[daniel klein]

For fine-tuning of your approach see the output generated by

local mons = strlower("`c(Mons)'")
forvalues year = 2007/2017 {
    foreach mon of local mons {
        local year2 = substr("`year'", 3, .)
        display "`mon'`year2'"
    }
}

Best
Daniel

[Pranav Garg]

A quick follow-up question, Daniel. Now that all files are formatted (same variable names, same number of columns), how can I append all the files in the folder to create one big dataframe?
Is there a loop to do so?

Sorry and thank you in advance,
Pranav

[daniel klein]

Actually, append should work without a loop (although I have not tried this)

local allfiles : dir . files "sort_SCR_*.dta" , respectcase
gettoken firstfile allfiles : allfiles
use `"`firstfile'"' , clear
append using `allfiles'

Best
Daniel

[Pranav Garg]

I have run the code and it is currently working. I shall inform you if the output was desirable.
I would also like to know what the syntax of your command means. What does gettoken do and why are you writing `"`firstfile'"'?

I understand if you are unable to explain them to me. But if possible, a brief word would be appreciated.

Pranav

[Maarten Buis]

If you don't know what a command does, then type in Stata help cmd_name where cmd_name is the name of the command you want to know more about. So in your case you would type help gettoken .

[daniel klein]

I start out with a complete list of files. Stata requires me to start with one file in memory to which others can be appended. I choose to begin with the first file in the list, which is why I have to remove its name from the list (as I do not want to append the file to itself). This can be done by gettoken, a low-level parsing command. It could be done in other ways.

I think it would be save not use compound double quotes with use, as filenames should not contain double quotes. So

use "`firstfile'"

would have sufficed. In your specific case the filenames do not even contain spaces, so not even the double quotes are needed. However, I tend to write code that is as general as possible.

The more interesting part is

append using `allfiles'

where I do not use double quotes. This is because the local created by the extended function dir contains a list of filenames that are already enclosed in double quotes.

Edit: Crossed with Maarten's more general and, thus, more valuable hint to Stata's help command. To learn more about this, type

help help

Best
Daniel

[Pranav Garg]

Thank you both.