putexcel loop through columns

[Niki Mink]

Hi all,
I am trying to create a report that can be ran and exported to excel on a monthly basis but I'm stuck with creating loops every pair of columns.
All the variables in the report are going to be disaggregated by country (variable: centernum), using 2 columns by country "Column B: n/Mean" and "Column C: %/sd" as in the following example:

Monthly data review TCU
	Total		Nigeria		Kenya		India-B
	n/Mean	%/sd	n/Mean	%/sd	n/Mean	%/sd	n/Mean	%/sd
Enrolment flow
Screened women	71	100
Eligible women	50	70.42
Randomized women	49	69.01
Women followed up at day 8	27	55.10

I wrote the following code:

putexcel  B2:C2=("Total") D2:E2=("Nigeria") F2:G2=("Kenya") H2:I2=("India-B") , merge bold hcenter border(all, thin, black)

local row = 3                         
local ncol = 2                         
levelsof centernum , local(centernum)
foreach y of local  centernum {
     local col : word `ncol' of `c(ALPHA)'
putexcel  `col'3=("n/mean") `col'3=("%/sd"), bold hcenter border(all, thin, black)
local ++ncol
}

However, I don't know how to specify that the %/sd should go in the following column so at the moment it overwrites what is already there and looks like this:

Monthly data review TCU
	Total		Nigeria		Kenya		India-B
	%/sd	%/sd	%/sd
Enrolment flow
Screened women	71	100
Eligible women	50	70.42
Randomized women	49	69.01

Any help on how to sort it would be of great help!
Thanks

[Andrew Musau]

No data example, but perhaps you want:

foreach y of local centernum {
    local col: word `ncol' of "`c(ALPHA)'"
    local col2: word `=`ncol'+1' of "`c(ALPHA)'"
    putexcel `col'3=("n/mean") `col2'3=("%/sd"), bold hcenter border(all, thin, black)
    local ncol= `ncol'+2
}

[Niki Mink]

This worked great, thanks so much Andrew!

I have a subsequent question on that same table.
I am trying to complete the table disaggregating the data by center. Each center has 2 columns, one for n/Mean and the other for %/SD as in the previous post.

As an example, the table should look something like this:

	Total		Nigeria		Kenya		India-B
	n/mean	%/sd	n/mean	%/sd	n/mean	%/sd	n/mean	%/sd
Indication for enrolment
pPROM	8	16.33	0	0	7	38.9	1	3.2
Preterm Labour	10	20.41	0	0	6	33.3	4	12.9
Planned termination	31	63.27	0	0	5	27.8	26	83.9

I have created the code below and the output is not as expected, but I cannot seem to find where I am making the mistake. This is what I am getting :

	Total		Nigeria		Kenya		India-B
	n/mean	%/sd	n/mean	%/sd	n/mean	%/sd	n/mean	%/sd
Indication for enrolment
pPROM	8	16.33	7	7.00	7.00	14.29	2.04
Preterm Labour	10	20.41	6	6.00	6.00	12.24	8.16
Planned termination	31	63.27	5	5.00	5.00	10.20	53.06

Another challenge that I am facing with this particular variable (a_ind_entry) is that one of the centers has no data, so i would need to find a way to indicate that if no data, columns should be 0.
Any help would be VERY appreciated.

* Example generated by -dataex-. For more info, type help dataex
clear
input int centernum byte(hospnum scrnum) float a_ind_entry
600 1 1 3
600 2 1 3
 25 1 1 .
406 3 1 1
406 3 2 3
600 1 2 2
 25 1 2 .
600 2 2 3
406 3 3 3
600 2 3 3
600 1 3 .
600 1 4 2
406 3 4 3
600 2 4 .
406 3 5 1
600 2 5 3
600 1 5 .
406 3 6 2
600 1 6 3
600 2 6 3
end

tab a_ind_entry if scr30==2 , matcell(freq) matrow (names) //indication for trial entry: 1=pPROM 2=Preterm labour 3=Provider Initiated
matrix list freq 
matrix list names
putexcel  B19=matrix(freq), hcenter border(all, thin, black)
putexcel C19=matrix(freq/r(N)*100), hcenter border(all, thin, black) nformat(number_d2)

local ncol = 4                         
levelsof centernum , local(centernum)
foreach y of local  centernum {
     local col : word `ncol' of `c(ALPHA)'
     local col2: word `=`ncol'+1' of `c(ALPHA)'
 tab a_ind_entry centernum , col matcell(freq) matrow (names) //indication for trial entry by centre: 1=pPROM 2=Preterm labour 3=Provider Initiated
    matrix list freq 
    matrix list names
putexcel `col'19=matrix(freq), hcenter border(all, thin, black)
putexcel `col2'19=matrix(freq/r(N)*100) , hcenter border(all, thin, black) nformat(number_d2)
 local ++ncol
 }

Than you very much!

[Andrew Musau]

Sorry, I cannot follow this.

tab a_ind_entry if scr30==2 , matcell(freq) matrow (names) //indication for trial entry: 1=pPROM 2=Preterm labour 3=Provider Initiated

There is no variable called scr30 in your example dataset. Secondly, I do not see how you associate countries to columns in your Excel file.

Another challenge that I am facing with this particular variable (a_ind_entry) is that one of the centers has no data, so i would need to find a way to indicate that if no data, columns should be 0.
Any help would be VERY appreciated.

I know nothing about your data, so you need to be very specific when asking for coding suggestions.

[Niki Mink]

That was the question that I needed, thanks!

Sorry about the missing variable, it's indifferent in this case so I removed it from the post now for the sake of simplicity.

I tried this and it works:

putexcel  B2:C2=("Total") D2:E2=("Nigeria -406") F2:G2=("Kenya -600") H2:I2=("India-B -1346") , merge bold hcenter border(all, thin, black)


local ncol = 4                         
levelsof centernum , local(centernum)
foreach y of local  centernum {
     local col : word `ncol' of `c(ALPHA)'
     local col2: word `=`ncol'+1' of `c(ALPHA)'
tab a_ind_entry if centernum==`y' ,  matcell(freq) matrow (names) //indication for trial entry by centre: 1=pPROM 2=Preterm labour 3=Provider Initiated
    matrix list freq 
    matrix list names
putexcel `col'19=matrix(freq),  hcenter border(all, thin, black)
putexcel `col2'19=matrix(freq/r(N)*100)  , hcenter border(all, thin, black) nformat(number_d2)
 local ncol= `ncol'+2
 }

* Example generated by -dataex-. For more info, type help dataex
clear
input int centernum byte(hospnum scrnum) float a_ind_entry
 406 2 1 3
 406 3 1 1
 600 2 1 3
 600 1 1 3
 600 1 2 2
 600 2 2 3
 406 3 2 3
1346 4 3 3
 406 3 3 3
 600 2 3 3
 406 3 4 3
 600 1 4 2
 406 3 5 1
 600 2 5 3
1346 4 6 1
 406 3 6 2
 600 1 6 3
 600 2 6 3
 406 3 7 2
 600 2 7 3
end

Is there a better solution to linking the center to the columns so that I am not prone to errors if there is a variable for which a center has no events?
For example, if in this case centernum==600 had all missing values in a_ind_entry, then my code would export information regarding centernum==1346 into the column previously specified to centernum==600. Is there a way to avoid that?

Sorry if I am not clear, I am learning this by doing so I might not have the correct terminology to explain it.

Thanks again

[Andrew Musau]

If there is a way to map the center identifiers to the cells in the Excel file, I would just define three locals with the identifiers and the columns once in the beginning and forget about them going forward. So dropping centrrnum=600 in your example:

* Example generated by -dataex-. For more info, type help dataex
clear
input int centernum byte(hospnum scrnum) float a_ind_entry
 406 2 1 3
 406 3 1 1
 406 3 2 3
1346 4 3 3
 406 3 3 3
 406 3 4 3
 406 3 5 1
1346 4 6 1
 406 3 6 2
 406 3 7 2
end


putexcel set myfile, replace
putexcel  B2:C2=("Total") D2:E2=("Nigeria -406") F2:G2=("Kenya -600") H2:I2=("India-B -1346") , merge bold hcenter border(all, thin, black)

local cells1 D F H
local cells2 E G I
local centernum 406 600 1346 

forval i= 1/`=wordcount("`centernum'")'{
    cap noisily{
        tab a_ind_entry if centernum==`=word("`centernum'", `i')' ,  matcell(freq) matrow (names) //indication for trial entry by centre: 1=pPROM 2=Preterm labour 3=Provider Initiated
        matrix list freq 
        matrix list names
        putexcel `=word("`cells1'", `i')'19=matrix(freq),  hcenter border(all, thin, black)
        putexcel `=word("`cells2'", `i')'19=matrix(freq/r(N)*100)  , hcenter border(all, thin, black) nformat(number_d2)
        clear matrix
    }
}