Combining local macro with global path

[Chinmay Korgaonkar]

Hello Everyone.

I need help in combining local macro with global path.

This is my code for combining CSV files in Stata

cd "D:\data"
local files: dir "D:\data" files "*.csv"
foreach file in `files'{
clear
import delimited `file'
save `file'.dta, replace
}

local files: dir "D:\data" files "*.dta"

foreach file in `files'{
append using `file', force
}
save master, replace


My global path is for Microsoft OneDrive is
global mainpath `"C:/Users/Admin/OneDrive/data"'

I tried multiple ways to combine this path with the existing code.

But the code is not able to fetch the CSV files stored in the “data” folder.

Can anyone guide me regarding it?

Thanks, and regards

[Clyde Schechter]

I'm not sure what you are asking here. You show some code that might or might not work, depending on circumstances. You seem to imply it works for one path but not for another. And you don't give any indication of what error messages, if any, you got along the way, or what results you did get and how they differ from what you wanted. So I can only make some general remarks.

The code you show in #1 will not, in general, produce any results. It will only work properly if the current working directory is D:\Data. The reason for that is that when -local files: dir ...- runs, while it searches for files in "D:\Data", it does not include the path in the filenames it puts into local macro files. Then when you then go into the loop to try to read those files Stata will search the current working directory. So unless the current working directory is D:\Data, Stata will tell you it cannot find the files. Same applies to the loop that appends them. There are two solutions to this. One is to change the working directory, the other is to incorporate the path to the working directory into the code. Which of these will be more convenient depends on what else you are doing.

Then you bring up the issue of using this with Microsoft OneDrive. This raises two questions: the syntax for combining the global path with local macro files, and the issue raised above concerning the current working directory. Again, you don't say what problem you are actually encountering, so I'm not sure what the fix is. But you probably want something like this:

clear
global mainpath C:/Users/Admin/OneDrive/data
local files: dir "$mainpath" files "*.csv"

local saved_files
foreach f of local files {
    import delimited using `"$mainpath/`f'", clear
    local savename: subinstr local f ".csv" ".dta"
    save `savename', replace // SAVES IN CURRENT WORKING DIRECTORY
    local saved_files `saved_files' `savename'
}

precombine `saved_files'  // CHECK COMPATIBILITY OF FILES FOR APPENDING

At that point you will want to review the output of -precombine- (which is written by Mark Chatfield and is available from SSC) and fix any incompatibilities among the files before proceding. Once you have done that:

// CREATE A NEW LOCAL MACRO, files, CONTAINING THE NAMES OF THE
// CLEANED-UP FILES TO BE APPENDED TOGETHER.  AT THIS POINT
// ONE DRIVE IS OUT OF THE PICTURE: YOU ARE WORKING IN YOUR
// REGULAR WORKING DIRECTORY

clear
append using `files'
save master, replace

Important things to note about this approach:
1. It is seldom the case that a large number of files will be completely compatible for combining (whether by -merge- or -append-). This is true even if all of the data sets were created by a single reputable data curator. There will usually be some situations where the same variable has different storage types in two data sets, or different names, or inconsistent value labels applied. Using -force- just sweeps these problems under the rug and causes you to lose the data on these inconsistent variables. The resulting data set will either be incomplete due to the lost data, or, if there were inconsistent value labels, it will contain a variable whose meaning depends on which data set it originally came from. -precombine- detects all of these problems and lets you know about them in advance. Fix the problems in the individual data sets: this is always easier than trying to fix the mess that -append, force- creates. As with other commands that have a -force- option, using -force- hides problems but does not fix them. -force- should be used only if you know exactly what those problems are and you are 1,000% confident that the lost or invalid data that will result from applying -force- will not matter for our purposes. But frankly, if you know what the problems are and you are sure that they won't matter, it's simpler and cleaner to just -drop- the offending parts of the data and then continue to -append- without -force-. That way, you'll have a certification from Stata that there weren't some additional problems that you were "certain" didn't exist.

2. The code assumes that you want to save the single files and the combined master result file in your current working directory, not on One Drive. In general, working with files over a network connection just slows things down. I usually avoid it unless there are restrictions on removing the data from there, or if the files are too large for me to maintain and work with on my own equipment.

3. Finally, be very cautious about using global macros. I would not store that path in a global myself. I would use a local macro. Global macros are treacherous because they can clobber, or be clobbered by, macros in parts of the code that are hidden from your view. The resulting bugs can be maddening to chase down. Local macros avoid this problem because they are only visible in their own local domain and cannot be affected by "spooky action at a distance." I recommend using local macros instead of globals except in those extremely rare circumstances where the information involved needs to be shared among different programs and the circumstances are such that there is no other way to accomplish that. FWIW, I have encountered such a situation only once in 29 years of using Stata on a daily basis.

[Chinmay Korgaonkar]

Dear Sir, thank you so much for your detailed response and guidance.