Choosing a variable to keep based on the name of the current working directory

[Daniel Sutcliffe]

Hello,

I have a long and fairly complex Stata script which I run after first setting the cd (working directory) to a specific directory. I want the code to run in the first directory in the list, then in the second directory in the list( same Stata script- as the tables in the directories all have the same names and variable names etc) . I have created a loop so that the code will run the code using .dta files that are in the respective directories:

CD "P:\FOLDER1\FOLDER2 \ year _base"
CD "P:\FOLDER1\FOLDER2 \ year 1"
CD "P:\FOLDER1\FOLDER2 \ year 2"
CD "P:\FOLDER1\FOLDER2 \ year 3"
CD "P:\FOLDER1\FOLDER2 \ year 4"
CD "P:\FOLDER1\FOLDER2 \ year 5"


However, I would like to change the script, so that when it comes merging a table that contains the variables year_base, year_1, year_2, year_3, year_4 and year_5, it only keeps the variable that matches the directory name, so when it merges with a table it keeps "year_base" variable where the the directory is CD "P:\FOLDER1\FOLDER2 \ year _base" and variable year_2 when the current directory is CD "P:\FOLDER1\FOLDER2 \ year 2" etc. I would be happy to merge all 6 variables, if I could then drop the only one that is needed based on the current working directory name.

Is this at all possible in Stata?

My loop, if it helps is as follows:

foreach i of num 1/5 {

CD "P:\FOLDER1\FOLDER2 \ year `i'"

stata code here

}

I thought I could utilise the `i' in the loop in someway, but the loop does not work for CD "P:\FOLDER1\FOLDER2 \ year _base". I have not been able to solve that problem - so I am thinking of some other way of referencing the current working directory so that the code either picks up the right variable or only keeps the right variable.

[Clyde Schechter]

I think you want something like this:

local dirnames: dir "P:\FOLDER1\FOLDER2" dirs "year*"

foreach d of local dirnames {
    cd "P:/FOLDER1/FOLDER2/`d'"
    local vble: subinstr local d " " "_", all
    // CODE HERE USING VARIABLE `vble'
}

This is untested, and it may not be quite right, but I think it will point you in the right direction.

Added: Important--note the use of /, not \, as the path separator here. This works even on Windows--Stata will replace the path separator before it forwards the I/O commands to Windows. But you must not change this part of the code, because the sequence \`d' will not be interpreted as \ followed by `d' in Stata. So you must use the / path separator when working with local macros in Stata, regardless of which OS you are using.

[Daniel Sutcliffe]

Thank you.

The directory names are "Aug 2021 Year*" with the exception of the base year which is called "Aug 2021 base". I can do the base year outside of the loop - as I don't know how to incorporate this directory that does not follow the same conventions as the other directories.

I changed the first bit of the code to: local dirnames: dir "G:\xxx\xxx" dirs "Aug 2021 Year*"

I need to change the code local vble: subinstr local d " " "_", all .

I tried using local v: substr( subinstr local d " " "_", all,10,6) - so I can capture the "Year 1" in the local variable - - but it says that substr is not allowed.

Are you able to advise on this matter please?

[Clyde Schechter]

The directory names are "Aug 2021 Year*" with the exception of the base year which is called "Aug 2021 base". I can do the base year outside of the loop - as I don't know how to incorporate this directory that does not follow the same conventions as the other directories.

I changed the first bit of the code to: local dirnames: dir "G:\xxx\xxx" dirs "Aug 2021 Year*"

No need to handle the base year out of the loop. Just add it to dirnames with:

local dirnames `dirnames' "Aug 2021 base"

I tried using local v: substr( subinstr local d " " "_", all,10,6) - so I can capture the "Year 1" in the local variable - - but it says that substr is not allowed.

As you noted, your use of -substr()- in this context is not legal. Try this:

local vble: subinstr local d "Aug 2021 " ""
local vble: subinstr local vble " " "_", all

The first of these will strip away the Aug 2021 part and leave vble with something like "Year 1" or "base". And the second will replace the space with an underscore (_) character.

There may be one more step needed, though I think not. I think you are running Windows. Its filenames are not case sensitive, the local macro dirnames will have them in all lower case. So that is fine and nothing more needs to be done. However, were you to run this code under an operating system (e.g. MacOS) where filenames are case sensitive, you would have to convert local macro dirnames to lower case so that it would match the variable names which, I glean from #1, are lower case. If you need to do that, you can accomplish it with -local vble = lower(`"`vble'"')-.

[Daniel Sutcliffe]

Thank you very much for your advice.

However, changing the code so it reads:

dir "G:\xxx\xxx" dirs "Aug 2021 Year*"
local dirnames `dirnames' "Aug 2021 base"

before the loop, results in an error:

unable to change to G:/xxx/xxx/aug
r(170);

I note that aug is presented lower case in the error message. However, I am not sure what to do about it.

Are you able to advise on this please?

[Clyde Schechter]

It's very difficult to troubleshoot this kind of problem from afar. I don't have your directory structure to work with. So this may take a long time and a large number of back-and-forths to shake out. Under the circumstances, it might make more sense for you to do what you proposed earlier--take the Aug 2021 base directory out of the loop and do it separately.

That said, if you want to try to work on this, please show the output of:

local dirnames: dir "G:\xxx\xxx" dirs "Aug 2021 Year*"
display `"`dirnames'"'
local dirnames `dirnames' "Aug 2021 base"
display `"`dirnames'"'

Also, please show the output of:

dir "G:\xxx\xxx"

Added: It just dawns on me that the code you show in #5 starts with -dir "G:\xxx\xxx" dirs "Aug 2021 Year*"-. Did you forget the -local dirnames:- part that should start that command?

[Clyde Schechter]

Actually, I think I see what's wrong now. Stata has a tendency to strip quotes from expressions in local macros under some circumstances, and I think that is biting us here. Try this:

local dirnames `"`dirnames' "Aug 2021 base""'

The use of compound double quotes around the entire expression will block Stata from stripping the single quote at the end of Aug 2021 base. That should resolve the problem you reported in #5.

[Daniel Sutcliffe]

Thank you - that works perfectly.