Specifying cell numbers in foreach loop with putexcel - adjusting for missing data

[Sonia Lewis]

Hi all,

I have 55 datasets, all with the same set of 60 variables, but some of them have missing data for some of the variables. I have a created a set of matrices that relate to each variable when it doesn't have missing data. I would like to put these matrices into an Excel sheet using code something like the code below. If h_urban has missing data, and no matrix is created, how can I make my foreach loop recognise that there are now only 3 variables in y1, and put only those names in F1, G1 and H1 and only those values in F2, G2 and H2?

local y1 = "h_region h_wealth h_urban m_edu"

findname, all(!missing(@)) local(y1_nonmissing)

foreach cell1 in F G H I  {
        local i1 = `i1' + 1
        local mat1 : word `i1' of `y1_nonmissing'
        di "`cell1'"
        di "`mat1'"
putexcel `cell1'1 ="`mat1'", bold
putexcel `cell1'2 =matrix(`mat1')
}

Thanks,

Sonia

[Carole J. Wilson]

This might get you started in the right direction:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(h_region h_wealth h_urban m_edu)
1 4 . 2
2 3 . 1
3 2 . 4
4 1 . 3
end


findname, all(!missing(@)) local(y1_nonmissing)
local num_nonmiss: list sizeof y1_nonmissing // # of words in y1_nonmissing
local range F G H I
  forvalues j=1/`num_nonmiss' {
        local mat1 : word `j' of `y1_nonmissing'
        local cell1 : word `j' of `range'
        di "`cell1'"
        di "`mat1'"
        putexcel `cell1'1 ="`mat1'", bold
        putexcel `cell1'2 =matrix(`mat1')
        }

[Sonia Lewis]

Hi Carole,

Thanks for this. I tried something similar but created matrices of the same size with missing values:

local y_all = "h_region h_wealth h_urban m_edu"

foreach file in `files' {

use "`file'", clear

findname, all(inlist(@, 0) | missing(@)) local(emptyall)
    
local y_all_nonempty : list y_all - emptyall    
  
local y_all_empty : list y_all- y_all_nonempty

/*Create the matrices separately for variables with and without missing data, then put them all in the spreadsheet using the original list of names, which corresponds to the names of the matrices.*/

local y1 = "`y_all'" 
foreach cell1 in F G H I  {
        local i1 = `i1' + 1
        local mat1 : word `i1' of `y1'
        di "`cell1'"
        di "`mat1'"
putexcel `cell1'1 ="`mat1'", bold
putexcel `cell1'2 =matrix(`mat1')
}
macro list
}

But I'm still testing this as now there are some other bugs... It seems to work fine for the first dataset, but then with the second dataset, everything runs well until the putexcel part. All the macros seem to be created correctly, but there is no y1 macro, so there is nothing to put in cell F2. Any ideas why this might be?

Sonia

[Carole J. Wilson]

The code you posted in #3 does not do what I posted in #2--it is essentially the same code as in #1. Your code does not drop the variable names with missing values. You create new macros for empty and non-empty variables, but they are not used again in the loop.

I'm afraid there are too many missing elements to understand exactly what you are doing to help you debug the code (I don't know how you generated and stored that matrices, how you are calling the files, your original putexcel statements, etc).

[Sonia Lewis]

Hi Carole,

You are right, sorry I didn't include all of the code because it was too long and complicated. I was just trying to simplify the problem. What my bit of missing code did was create two sets of matrices; one for those in `y_all' without missing data (i.e. `y_all_nonempty' - h_region h_wealth m_edu) and one for those with missing data (i.e. `y_all_empty' - h_urban), then used `y_all' to put all of the matrices with the same names as those in the original `y_all' list in the Excel sheet.

As I said, it worked fine for the first dataset, but didn't loop properly. I have re-written the code, now using your suggestion for creating the local range (which I think is much neater!), and in doing so I have also sorted out the bug with the loop. Maybe I had some brackets in the wrong place.

Thanks!

Sonia

[Antra Bhatt Hakhu]

Hi All, I have question regarding using putexcel in a Loop. I am running a short code for 60 surveys and exporting data in excel. Is there a way with putexcel to append results because currently my code will replace the results as the loop runs. Pasting my code below

clear
set more off
set maxvar 20000
local workdir "C:\Users\antra.bhatt\OneDrive - UN Women\Antradocs\SDG Monitoring Report 2018\chapter4\DHS\IRFiles"
cd "`workdir'"
local files: dir "`workdir'" files "*.dta"
putexcel set myresults.xlsx, sheet(Descriptive) modify


foreach aaa of local files {
use `aaa'
* Drop missing values for partners age
drop if v730==.
*keep currently married women only
keep if v501==1
* Create age difference variable
gen agediff= v730 - v012
* gen requisite variables by age of marriage
gen married_15=0
replace married_15=1 if v511<15
gen married_18=0
replace married_18=1 if v511<18
replace married_18=. if v012<18
gen married_adult=0
replace married_adult=1 if v511>=18
replace married_adult=. if v012<18
gen married_all=0
replace married_all=1 if v511!=.
*tabout results
tabstat agediff [fweight=v005],by(married_15) format(%5.1f) save
putexcel A2=matrix(r(Stat2))
tabstat agediff [fweight=v005],by(married_18) format(%5.1f) save
putexcel A3=matrix(r(Stat2))
tabstat agediff [fweight=v005],by(married_adult) format(%5.1f) save
putexcel A4=matrix(r(Stat2))
tabstat agediff [fweight=v005],by(married_all) format(%5.1f) save
putexcel A5=matrix(r(Stat1))
tabstat agediff if v013==2 [fweight=v005],by(married_15) format(%5.1f) save
putexcel A6=matrix(r(Stat2))
tabstat agediff if v013==2 [fweight=v005],by(married_18) format(%5.1f) save
putexcel A7=matrix(r(Stat2))
tabstat agediff if v013==2 [fweight=v005],by(married_adult) format(%5.1f) save
putexcel A8=matrix(r(Stat2))
tabstat agediff if v013==2 [fweight=v005],by(married_all) format(%5.1f) save
putexcel A9=matrix(r(Stat1))
tabstat agediff if v013==7 [fweight=v005],by(married_15) format(%5.1f)save
putexcel A10=matrix(r(Stat2))
tabstat agediff if v013==7 [fweight=v005],by(married_18) format(%5.1f)save
putexcel A11=matrix(r(Stat2))
tabstat agediff if v013==7 [fweight=v005],by(married_adult) format(%5.1f)save
putexcel A12=matrix(r(Stat2))
tabstat agediff if v013==7 [fweight=v005],by(married_all) format(%5.1f)save
putexcel A13=matrix(r(Stat1))
clear

}