Creating Multiple Excel Files for Group Categories in STATA

[danishussalam]

Hello Everyone, So I have used the sample command with by(branch) to select 8 clients from each branches. I now have a total of 80 clients from 10 branches. Is there a way I could export this file to excel in a way that I have 10 different excel files from each of the branches? each having 10 clients.

Thanks!

[Clyde Schechter]

Yes, just loop over the 10 branches to do it:

levelsof branch, local(branches)
foreach b of local branches {
    export excel using sample_`b'.xlsx if branch == `"`b'"', replace
}

Note: Above assumes that branch is a string variable and that it does not contain any characters that would be prohibited as part of a filename in your OS. If it is a numeric variable, then just remove the `" "' from around `b'. If it is a string but contains prohibited characters, then it gets more complicated as you would have to figure out how you want to transform those values into something that is legal in a filename but still has some mnemonic value for the actual branch name.

[danishussalam]

Yes, just loop over the 10 branches to do it:

levelsof branch, local(branches)
foreach b of local branches {
export excel using sample_`b'.xlsx if branch == `"`b'"', replace
}

Note: Above assumes that branch is a string variable and that it does not contain any characters that would be prohibited as part of a filename in your OS. If it is a numeric variable, then just remove the `" "' from around `b'. If it is a string but contains prohibited characters, then it gets more complicated as you would have to figure out how you want to transform those values into something that is legal in a filename but still has some mnemonic value for the actual branch name.

So I copied the code exactly how you shared it. It returns an error saying

. foreach b of local branches {
2. export excel using sample_`b'.xlsx if branch == `"`b'"', replace
3. }
invalid 'Road.xlsx'
r(198);

end of do-file

r(198);

branch is a str15 variable in my dataset.

[Clyde Schechter]

OK, I think I know what the problem is. Perhaps some values of branch, particular the one that threw this error, contain embedded blank spaces. In that case, if the value of branch was "Abbey Road", the -export excel- command would come out as -export excel using sample_Abbey Road.xlsx...-, and the Road.xlsx part is seen as extraneous material because it is set off by a blank. The fix is easy:

levelsof branch, local(branches)
foreach b of local branches {
export excel using `"sample_`b'.xlsx"' if branch == `"`b'"', replace
}

[danishussalam]

OK, I think I know what the problem is. Perhaps some values of branch, particular the one that threw this error, contain embedded blank spaces. In that case, if the value of branch was "Abbey Road", the -export excel- command would come out as -export excel using sample_Abbey Road.xlsx...-, and the Road.xlsx part is seen as extraneous material because it is set off by a blank. The fix is easy:

levelsof branch, local(branches)
foreach b of local branches {
export excel using `"sample_`b'.xlsx"' if branch == `"`b'"', replace
}

It works! Thank you so much!

The only problem is that it doesn't show the variable names. Is there a fix for that?

[danishussalam]

I got it. added firstrow(variables) in your code.

Thank you so much!!

[danishussalam]

OK, I think I know what the problem is. Perhaps some values of branch, particular the one that threw this error, contain embedded blank spaces. In that case, if the value of branch was "Abbey Road", the -export excel- command would come out as -export excel using sample_Abbey Road.xlsx...-, and the Road.xlsx part is seen as extraneous material because it is set off by a blank. The fix is easy:

levelsof branch, local(branches)
foreach b of local branches {
export excel using `"sample_`b'.xlsx"' if branch == `"`b'"', replace
}

I don't know why but somehow I am not being able to run the code. this is what it says:

. foreach b of local branches {
2. export excel using sample_`b'.xlsx if branch == `"`b'"', replace
3. }
type mismatch
r(109);

end of do-file

anyway to fix this?