Foreach loops and local macros creating save problems

[Rolf Lund]

Hey all.

This is my third question in a short period of time and I should finally have it all figured out by now.... except from one thing. I am using foreach to loop through a list of datasets to do a lot of things but I have found one very specific complication; when I use the local to call on all .dta-files in the directory and then use that macro two save a graph as png, it gives me an error, since it's trying to save the graph as datasetname.dta.png (since the local contains datasetname.dta). Below is a condensed code that recreates the problem.

clear
 cd "C:\Users\rolfll\Dropbox\QTI\Runde 5\dta"
 local dta : dir . files "*.dta"

foreach x of local dta {
use `x'

hist var1
graph export `x'.png, replace

I thought that one way of solving it would be to somehow edit the local macro to remove the ".dta" suffix and then just write

use `x'.dta

instead of

use `x'

, but I am not quite sure how to fix this, since all the datasets have different names. I thought it was maybe possible to remove the last 4 characters of the local by doing this:

clear
 cd "C:\Users\rolfll\Dropbox\QTI\Runde 5\dta"
 local dta : dir . files "*.dta"
 foreach f of local dta{
 local dta1 = substr("`f'", 1, length("`f'")-4)
 di `dta1' 
 }

But it tells me that: "dataset1 not found". This is the name of the first dataset in the folder, so it is able to call upon it but something is still wrong.

Thanks in advance guys.

[Chris Vecchio]

You need to add quotes around `dta1' when using the display command. The use command doesn't require the quotes unless your file names have spaces in them.

local dta : dir . files "*.dta"
foreach f of local dta {
   local dta1 = substr("`f'", 1, length("`f'")-4)
   di "`dta1'"
   use `dta1', clear
}

[Rolf Lund]

Okay, this makes sense but why then, when I try to use this new list, does it not loop through the unique items in the list? If I do like this:

 local dta : dir . files "*.dta"
foreach f of local dta {    
local dta1 = substr("`f'", 1, length("`f'")-4)    
di "`dta1'"

And then this:

 foreach x of local dta1 {
use `x'.dta
tab var
}

It only creates 1 table even though I can see in my local dta1 that I have numerous datasets?

[Nick Cox]

In your loop you are cycling over different files and then (repeatedly) overwriting local dta1,

So, when you start something new that local just contains one filename element, the last one edited.

This should work better:

local dta : dir . files "*.dta"
local dta : subinstr local dta ".dta" "", all 
foreach f of local dta {    
   use `f' 
   ...
}

[Rolf Lund]

Thank you, Nick.

Does that mean, that when you specify this

".dta" ""

you tell it that the part that is called ".dta" should be blank? Why do you specify

,all

? I'm trying to get the steps so I don't have to ask another time.

[Nick Cox]

The whole point of the first line is to get a list of all the .dta files qualifying. So, you should want code to change every incidence of the extension .dta

Later

1. use is not fazed by the absence of .dta as an explicit extension

2. You can add any extension you like for other kinds of files that are output, such as .png in #1.

[grace cook]

I have a similar question that I think is related. I'm also trying to make edits on files with a loop (every file has a variable "cty_code" and I want to only keep the cty_code 1220. This is my code:

local yrly_exports : dir . files "*.dta"
di `yrly_exports'

foreach file of local yrly_exports {
    use `file'
    keep if cty_code == 1220, clear
    save `file', replace
}

When I run it I get an error 101, "options not allowed." Does anyone know why this would happen? Nick Cox I've seen a few of your posts related to this and have tried to implement changes but I can't get it to work. Thanks in advance!

[Ali Atia]

-keep- does not allow options. You should remove the clear option from the keep command and add it to the use command:

use `file', clear
keep if cty_code == 1220

[grace cook]

Ah beautiful, thanks so much, Ali!

[Pascal Tohouri]

Dear All,

I have a slightly different issue:

I have a folder directory 'C:\Users\pasca\OneDrive\1.1_Current\Working'. I want to save the contents of the folder (2 excel files) using a local macro. Then, I want a foreach loop to read each file in the folder and append the files together into one file. I used queries from elsewhere on this site (1).

The queries are:

clear

local data "C:\Users\pasca\OneDrive\1.1_Current\Working"

tempfile building
save `building', emptyok

foreach f of local data {
import excel using `"`f'"'
gen source = `"`f'"'
display `"Appending `f'"'
append using `building'
save `"`building'"', replace
}

export excel using 1_large_excel_file.xlsx, replace

These queries return an error:

file C:/Users/pasca/OneDrive/1.1_Current/Working not found
r(601);

All queries before the 'foreach' loop do not return an error. Thank you in advance for your help and wisdom.

(1) https://www.statalist.org/forums/for...rge-excel-file.

[Clyde Schechter]

As best I can tell, your definition of local macro data is the directory in which the files you are interested in are located. But when you get to the loop, what you need is a local macro containing the names of the files to be imported themselves. So it goes more like this:

clear

local data "C:\Users\pasca\OneDrive\1.1_Current\Working"
local to_import: dir `"`data'"' files "*.xlsx"

tempfile building
save `building', emptyok

foreach f of local to_import {
    import excel using `"`data'/`f'"'
    gen source = `"`f'"'
    display `"Appending `f'"'
    append using `building'
    save `"`building'"', replace
}

export excel using 1_large_excel_file.xlsx, replace

One more thing. The final -export excel- command will store the file in whatever is Stata's current working directory at the time it is run. Since you thought it necessary to define the directory in local macro data, I would guess that that won't be the current working directory. If you want it stored in "C:\Users\pasca\OneDrive\1.1_Current\Working", then change the final command to:

export excel using `"`data'/1_large_excel_file.xlsx"', replace

[Pascal Tohouri]

Dear Clyde,

Thank you for your help. These queries, however, return the error:

no; data in memory would be lost
r(4);

The two excel files in the directory share a column (containing ID numbers for each observation). Can Stata not append the spreadsheets because of this feature?

[Clyde Schechter]

The two excel files in the directory share a column (containing ID numbers for each observation). Can Stata not append the spreadsheets because of this feature?

No, that is not the source of this problem.

All Stata commands that read data into memory, such as -use-, -import delimited-, -import excel-, etc. have the property that they will not proceed if there is data currently in memory and that data was modified without the original source file being re-saved. This prevents you from inadvertently losing your changes to a data set. In this case, however, it is getting in the way. The solution is to add the -clear- option to the -import excel- command inside the loop. The -clear- option tells Stata that it is OK to wipe out the data in memory and replace it with the new data.

That said, if the data files have only a single column in common, it probably does not make sense to -append- them together. Generally when we are -append-ing files, they have all or nearly all of their variables (columns) in common. If the only common variable here is the ID, though it is legal to do so, the result of -appending- the files is going to be a very bizarre layout that will probably prove impossible to work with. It probably means you need to -merge- the data sets, though without seeing example data, I cannot say that with certainty.

[Adil Saleem]

Hello everyone,
I am not very active here and not very expert in Stata. I am working on my Thesis and using Synthetic control group method. I am using Scott Cunningham synth do file (attached here with). I think i am getting good resultts but while running placebo test and inferences. I am unable to find the saved files and stata does not find the those .dta files. Please someone help me in this

https://github.com/scunning1975/mixt...texas_synth.do

Please open this .do file line 73 - 85 stata does not show any error but did not save the files with generated gap with extension `i'. I am unable open the dta file which (as shown in line 88 - 98).

Could you please help. Clyde Schechter , You helped me 2 years ago with something in stata. If you have time, could you please help me here.

Thanks in advance.

[Clyde Schechter]

As you can find in the Forum FAQ, attachments are discouraged here. Some of us, including me, will not download or open attachments from people we do not know as it poses a security risk. The helpful way to show code for troubleshooting is to just copy/paste the relevant code here into the Forum between code delimiters. When asking for help troubleshooting code it is also usually necessary to show example data that exhibits the problem you are encountering. The way to do that is with the -dataex- command. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Also, as far as I can see, your question here has nothing to do with the topic of the thread. It is important to keep threads on topic. Apart from the person posing the initial question and the person or people who respond to it, these threads are searched and read by others who encounter the same problem or who want to learn about the topic. By diverting the subject, you make searching the Forum less effective.

So, please repost your question in a new thread. When doing that, show your code in code delimiters, and post -dataex- output with example data illustrating the problem. I also recommend you not specifically address your post to me or anybody else. It is likely that many Forum members will be able to answer your question, and it is possible that I am not able to answer your question. So you should not discourage anybody from helping you and get the earliest possible response.