Exporting multiple excel sheets in one excel file

[Abbas Raza]

Hi,

The code below exports each data set (A_set, B_set and C_set) in separate excel file named as A_set, B_set and C_set if variable A=2 and B=175 and C=44 (the code below assumes that we have A, B and C variables in all the data sets).

foreach i in A_set B_set C_set {
use `i', clear
export excel "`i'.xlsx" if A==2|B==175|C==44, firstrow(variables) sheet("`i'") nolabel replace
}

Instead of trying to export data set in separate excel, I want to execute the command as follows:
1. Export all these data sets as separate sheets in one excel file rather than exporting in separate files.
2. As I mentioned earlier that the above code assumes the variable A,B and C are in every data set (A-C_set). Is there any way to write a code where I can execute code with this logic "If variable A is in data set A_set then export observations in sheet A_set if A=2 (and ignore if variable B and C are not part of data set A_set)"

Is there any way to write a code that could achieve both of the objectives mentioned above simultaneously?

Thanks!

[Joseph Coveney]

I assume that you want to do something analogous with datasets B and C. Here's one way.

local criteria 2 175 44
forvalues i = 1/3 {

    local letter : word `i' of `c(ALPHA)'
    use `letter'_set, clear

    local criterion : word `i' of `criteria'
    quietly keep if `letter' == `criterion'

    local replace = cond(`i' == 1, "replace", "")

    quietly export using MyWorkbook, sheet(`letter'_set) firstrow(variables) `replace'
}

[Abbas Raza]

Thanks it works! (You missed export excel in final command) :-)

[Joseph Coveney]

You're right. Sorry about that, but glad that it worked out for you anyway.