export excel

[Masoumeh Sanagou]

Hi STATALIST,

The following program runs fine when there is at least on observation in each sheet. but I don't have any observations in some sheets. I need to save those sheets in the data check_PAH file too.

So I need to have all named sheets either with observations or without observations in the data check_PAH.

Any advice on solving this problem please?

Regards,

levelsof sheets_name, local(levels)
local vlname: value label sheets_name
foreach L of local levels {
local vl: label `vlname' `L'
export excel centre studydescription_all Unknown1 using "U:\eDose Monitoring Data\data check_PAH" if centre==5 & Unknown1==`L' ///
,sheet("`vl'") sheetreplace firstrow(variables)
}

[Clyde Schechter]

Well, I experimented a little and couldn't find any way to force -export excel- to save a blank worksheet. But you can get something almost as good by exporting a worksheet containing only an empty string value to a single cell:

levelsof sheets_name, local(levels)
local vlname: value label sheets_name
gen empty = ""
foreach L of local levels {
    local vl: label `vlname' `L'
    count if centre==5 & Unknown1==`L'
    if r(N) > 0 {
        export excel centre studydescription_all Unknown1 ///
        using "U:\eDose Monitoring Data\data check_PAH" ///
        if centre==5 & Unknown1==`L' ///
        ,sheet("`vl'") sheetreplace firstrow(variables)
    }
    else {
        export excel empty using "U:\eDose Monitoring Data\data check_PAH", ///
        sheet("`vl'") sheetreplace
    }
}

[Joseph Coveney]

Basing on Clyde's excellent answer, if you wanted to maybe save some file space, you could

export excel empty using "U:\eDose Monitoring Data\data check_PAH" in 1, ///
        sheet("`vl'") sheetreplace

You could also use -odbc- to create empty (zero-observation) worksheets.

[Masoumeh Sanagou]

Thanks for all amazing advice.

If I could have variable names (centre studydescription_all Unknown1) repeated in empty sheets is even better and save me time too.

[Joseph Coveney]

If I could have variable names (centre studydescription_all Unknown1) repeated in empty sheets is even better and save me time too.

Untested, but, assuming that your variable labels do not have spaces.

levelsof sheets_name, local(levels)
local vlname : value label sheets_name

local empty_list

foreach L of local levels {
    local vl : label `vlname' `L'
    quietly count if centre == 5 & Unknown1 == `L'
    if r(N) > 0 {
        export excel centre studydescription_all Unknown1 ///
            using "U:\eDose Monitoring Data\data check_PAH" ///
                if centre==5 & Unknown1==`L' , ///
                    sheet("`vl'") sheetreplace firstrow(variables)
    }
    else {
        local empty_list `empty_list' `vl'
    }
}

quietly keep in 1
keep centre studydescription_all Unknown1
quietly foreach var of varlist _all {
    if substr("`: type `var''", 1, 1) == "s" replace `var' = ""
    else replace `var' = .
}
foreach worksheet in `empty_list' {
        export excel using "U:\eDose Monitoring Data\data check_PAH", ///
            sheet("`worksheet'") sheetreplace
}

If they do have spaces, then use compound double quotes.

[Joseph Coveney]

Actually, you can save a few lines of code with the following tactic.

levelsof sheets_name, local(levels)
local vlname : value label sheets_name

quietly set obs `=_N + 1'

foreach L of local levels {
    local vl : label `vlname' `L'
    quietly count if centre == 5 & Unknown1 == `L'
    if r(N) > 0 {
        export excel centre studydescription_all Unknown1 ///
            using "U:\eDose Monitoring Data\data check_PAH" ///
                if centre==5 & Unknown1==`L' , ///
                    sheet("`vl'") sheetreplace firstrow(variables)
    }
    else {
        export excel centre studydescription_all Unknown1 ///
            using "U:\eDose Monitoring Data\data check_PAH" ///
                in l, ///
                    sheet("`vl'") sheetreplace firstrow(variables)
    }
}

[Masoumeh Sanagou]

Now, I have two programs and need to combine them in one.

The result of the first program is a file named frequencies with 5 spreadsheets (name of each spread sheets is `CentreName') and the results of the second program is 5 different files (data check_`CentreName').

I need to have each sheets in frequencies file in appropriate data check file.

Any advice on how I can do this?


Regards,

*frequencies
quietly sum centre
local max_centre=r(max)
local min_centre=r(min)
local centre1 : value label centre

quietly sum sheets_name
local max_sheets_name=r(max)
local min_sheets_name=r(min)
levelsof sheets_name, local(levels)
local vlname : value label sheets_name

forvalues i=`min_centre'(1)`max_centre' {
local CentreName : label `centre1' `i'
forvalues L=`min_sheets_name'(1)`max_sheets_name' {
local vl : label `vlname' `L'
version 14.0
putexcel A1=("Categories") B1=("Freq.") using "U:\UNSCEAR\eDose Monitoring Data\frequencies", sheet("`CentreName'") modify
local G = `L'+1
version 14.0
putexcel A`G'=("`vl'") using "U:\UNSCEAR\eDose Monitoring Data\frequencies", sheet("`CentreName'") modify
}
tabcount sheets_name if centre==`i', v1(1/18) v2(1/2) zero missing matrix(x)
version 14.0
putexcel b2=matrix(x) using "U:\UNSCEAR\eDose Monitoring Data\frequencies", sheet("`CentreName'") modify
}

and

*data check
quietly sum centre
local max_centre=r(max)
local min_centre=r(min)
local centre1 : value label centre
levelsof sheets_name, local(levels)
local vlname : value label sheets_name
quietly set obs `=_N + 1'
forvalues i=`min_centre'(1)`max_centre' {
local CentreName : label `centre1' `i'
foreach L of local levels {
local vl : label `vlname' `L'


quietly count if centre==`i' & Unknown1 == `L'
if r(N) > 0 {
export excel centre studydescription_all Unknown1 ///
using "U:\UNSCEAR\eDose Monitoring Data\data check_`CentreName'.xlsx" ///
if centre==`i' & Unknown1==`L' , ///
sheet("`vl'") sheetreplace firstrow(variables)
}
else {
export excel centre studydescription_all Unknown1 ///
using "U:\UNSCEAR\eDose Monitoring Data\data check_`CentreName'.xlsx" ///
in l, ///
sheet("`vl'") sheetreplace firstrow(variables)
}
}