Estout Matrix looping through locals in date format

[Megan Little]

Hi

I am using Stata 15.0. I have test score data on a variety of organisations for different time periods. I am trying to export a matrix into excel for each organisation that has the organisation name, mean test score and the start and end date of the time period for which they were assessed. My variables are:

program (organisation name)
period_`program' (variable per program that specifies the number of the time period for its observations)
today (date stored in date format)
score (test score)

My code looks like this:

levelsof program, local(program_name) /*This is necessary because we keep adding new programs and I want the code to run through all program names that we add*/
foreach program in `program_name'{
levelsof period_`program', local(no) /*Store no of periods for that program name, in "no"*/
foreach no in `no' {

egen min=min(today) if period_`program'==`no'
format min %tdMonth_DD,_CCYY
sum min, mean
local min: disp %tdMonth_DD,_CCYY r(mean)
drop min
egen max=max(today) if period_`program'==`no'
format max %tdMonth_DD,_CCYY
sum max, mean
local max: disp %tdMonth_DD,_CCYY r(mean)
drop max

egen x=mean(score) & period_`program'==`no'
egen mean=max(x)
local mean=mean
drop mean x

matrix t2=J(1,3,.) /*rows defined first*/
local i=0
foreach z in `min' `max' `mean' {
local i=`i'+1
matrix t2[1,`i']=`z'
}
mat rowname t2="`program'"
mat colname t2="Start date" "End date" "Average score"
*ssc install estout
estout matrix(t2) using "$out\\`program'_`no'.xls", replace
}
}

My data is as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float today str16 program float(period_grow period_lesedi period_cot_lydenburg) double score
20894 "lesotho_elds"     . . .              58.44
20900 "lesotho_elds"     . . . 28.769999999999996
21251 "ntataise_fs"      . . .              55.68
20894 "lesotho_elds"     . . . 37.989999999999995
21241 "khululeka"        . . .              33.39
21249 "smartstart"       . . .              34.16
21147 "learn_initiative" . . . 24.619999999999997
20992 "learn_initiative" . . . 25.299999999999997
21139 "learn_initiative" . . .              44.86
21143 "learn_initiative" . . .              66.07
21270 "ntataise_fs"      . . .              41.56
21265 "ntataise_fs"      . . . 31.029999999999998
21248 "sikhula_sonke"    . . .              44.51
21122 "smartstart"       . . .  45.17999999999999
21258 "cot_lydenburg"    . . 1  33.46999999999999
21109 "smartstart"       . . .              43.45
20907 "smartstart"       . . .  36.63999999999999
21257 "ntataise_fs"      . . . 28.889999999999993
21234 "smartstart"       . . .  61.30000000000001
20882 "lesotho_elds"     . . .              87.52
20900 "lesotho_elds"     . . .  39.83999999999999
21137 "learn_initiative" . . .              34.04
20879 "lesotho_elds"     . . .  83.91999999999999
20879 "grow"             2 . .  40.78999999999999
20894 "lesotho_elds"     . . . 63.510000000000005
21145 "learn_initiative" . . .              54.05
20899 "lesotho_elds"     . . . 30.499999999999996
21256 "sikhula_sonke"    . . . 32.489999999999995
21269 "lesedi"           . 3 .              22.27
21236 "khululeka"        . . .              20.68
21271 "ntataise_fs"      . . . 29.789999999999996
21143 "learn_initiative" . . .              42.91
21259 "ntataise_fs"      . . .              50.76
20891 "lesotho_elds"     . . . 37.839999999999996
21271 "ntataise_fs"      . . .  61.17999999999999
21250 "smartstart"       . . . 59.879999999999995
20885 "lesotho_elds"     . . .              35.94
21263 "lesedi"           . 3 .              36.65
21262 "lesedi"           . 3 . 28.849999999999994
21241 "khululeka"        . . .  58.19999999999999
21262 "ntataise_fs"      . . . 28.319999999999997
21262 "ntataise_fs"      . . . 40.629999999999995
20985 "learn_initiative" . . .              13.01
21249 "cot_lydenburg"    . . 1 15.290000000000001
21249 "lesedi"           . 3 . 29.749999999999996
21271 "unlim_child_kzn"  . . . 52.279999999999994
21249 "lesedi"           . 3 . 32.910000000000004
21257 "ntataise_fs"      . . .              60.17
21249 "cot_lydenburg"    . . 1              34.21
21137 "learn_initiative" . . .  37.42999999999999
21250 "ntataise_fs"      . . .              32.19
21257 "ntataise_fs"      . . .              74.83
21144 "learn_initiative" . . .              59.41
21129 "khululeka"        . . .              39.83
20900 "lesotho_elds"     . . .  44.87999999999999
21250 "sikhula_sonke"    . . .              63.09
21145 "learn_initiative" . . .              46.99
21249 "lesedi"           . 3 .              23.31
21269 "unlim_child_kzn"  . . .              19.93
20896 "lesotho_elds"     . . .              51.28
20908 "smartstart"       . . . 23.429999999999996
21250 "smartstart"       . . . 28.169999999999998
21130 "khululeka"        . . . 30.669999999999998
21251 "cot_lydenburg"    . . 1              32.16
21249 "ntataise_fs"      . . .              72.86
21265 "lesedi"           . 3 . 20.599999999999998
21270 "cot_macassar"     . . . 28.009999999999998
21271 "unlim_child_kzn"  . . .              62.91
20893 "grow"             2 . .  39.53999999999999
21249 "sikhula_sonke"    . . . 30.240000000000002
20891 "smartstart"       . . .              58.92
21228 "smartstart"       . . . 61.150000000000006
21269 "ntataise_fs"      . . .              35.38
21230 "smartstart"       . . .  55.19999999999999
21138 "learn_initiative" . . .  66.00999999999999
21262 "ntataise_fs"      . . . 45.569999999999986
20772 "khululeka"        . . .  80.36999999999999
21262 "cot_macassar"     . . .              29.32
21263 "lesedi"           . 3 .  46.51999999999999
21242 "khululeka"        . . .              33.47
21115 "smartstart"       . . .  37.78999999999999
20891 "smartstart"       . . . 30.399999999999995
21250 "ntataise_fs"      . . .  40.10999999999999
21256 "cot_macassar"     . . .              30.75
21144 "learn_initiative" . . .              65.64
21235 "khululeka"        . . . 53.459999999999994
21115 "smartstart"       . . .  41.85999999999999
20893 "smartstart"       . . . 27.419999999999998
21139 "learn_initiative" . . .              44.94
20899 "lesotho_elds"     . . .              70.14
21115 "smartstart"       . . .  50.28999999999999
20906 "grow"             2 . .              36.17
20893 "smartstart"       . . .              29.38
21104 "smartstart"       . . .  43.91999999999999
20901 "smartstart"       . . .              47.14
21269 "unlim_child_kzn"  . . .              30.02
20894 "smartstart"       . . .  36.53999999999999
21145 "learn_initiative" . . .  40.65999999999999
21271 "ntataise_fs"      . . . 40.540000000000006
21255 "ntataise_fs"      . . .                 54
end
format %td today

When I run this code I get an error that says "March not found" (ie, the local value of `min' which is March 02, 2018). Can anyone tell me how Stata can store the date value in a way that can be exported into a matrix through a loop?

Thanks
Megan

[Nick Cox]

I started reading your code and think that the first few lines can be slimmed down (10 lines reduced to 3).

There is no gain here in putting constants into variables or in formatting variables you are going to drop.

levelsof program, local(program_name)
foreach program in `program_name'{
    levelsof period_`program', local(no)
    foreach no in `no' {
        su today if period_`program'==`no', meanonly
        local min: di %tdMonth_DD,_CCYY r(min)
        local max: di %tdMonth_DD,_CCYY r(max)

Then I stopped at

    egen x=mean(score) & period_`program'==`no'

which is illegal Stata code and which I don't understand either. I could guess that

&

should be

if

On the other hand, you could be copying code and leaving out details you think aren't important but mangling it by accident.

Where is the code failing? This is an important detail.. At a wild guess you're formatting the dates prematurely and would be better advised to use their numeric values.

[Megan Little]

Hi Nick

Thank you so much for the reply. Yes I copied code but tried to simplify it and mangled it by accident, that should be "if" and thank you for the simplification tips, I often do things the long way round it seems.

The code breaks right after these lines:

foreach z in `min' `max' `mean' {
local i=`i'+1
matrix t2[1,`i']=`z'
}

The error says "March not found"

Thanks
Megan

[Megan Little]

Hi

Just checking in whether anyone could tell me when I should be formatting the dates? Is it possible to format the dates through "estout matrix"? I can see some help files on how to do it for coefficients for "estout" using "cells" but this option is disabled when using "estout matrix" I think. Any help would be greatly appreciated!

Thanks
Megan

[Megan Little]

Also, in case this is useful I have tried the following and whilst I don't get an error message anymore, the output is returned for both OPTION1 and OPTION2 in the numeric format in Excel, rather than in the date format (though I can see the matrix is showing up in the Stata window when I list it, with the correct formatting for the dates).

levelsof program, local(program_name)
foreach program in `program_name'{
            levelsof period_`program', local(no) /*Store no of periods for that program name, in "no"*/
            foreach no in `no' {

                        su today if period_`program'==`no', meanonly
                        local min=r(min)
                        local max=r(max)

                        su score if period_`program'==`no'
                        local mean=r(mean)

                        matrix t2=J(1,3,.) /*rows defined first*/
                        local i=0
                        foreach z in `min' `max' `mean' {
                                    local i=`i'+1
                                    matrix t2[1,`i']=`z'
                        }
                        mat rowname t2="`program'"
                        mat colname t2="Start date" "End date" "Average score"

                        matlist t2,  cspec(& %50s & %td & %td & %12.0f & ) rspec(|||)
                        putexcel A1=matrix(t2, names) using "$out\\`program'_`no'_OPTION1.xls", replace keepcellformat
                        *ssc install estout
                        estout matrix(t2) using "$out\\`program'_`no'_OPTION2.xls", replace
            }
}

[Nick Cox]

I don't understand that you're doing here because I never do similar things myself. But marginally I can point out that

matrix t2 = (`min', `max', `mean')

is a shorter way to do what this does

matrix t2=J(1,3,.) /*rows defined first*/
local i=0
foreach z in `min' `max' `mean' {
    local i=`i'+1
    matrix t2[1,`i']=`z'
}

[Megan Little]

Thanks Nick! In fact I have simplified the code as I was looping through different age categories on different rows in the matrix but I deleted that code as it wasn't relevant here. If anyone else has any advice it would be appreciated!

[Nick Cox]

When I've looked at this I've been working from your code and trying to see where it went wrong or otherwise how to improve on it. Looking at the thread from another end, it seems that this boils down to summarizing

start date
end date
mean score

for various subsets of the data. Why isn't that just one application of collapse?

[Megan Little]

Hi Nick

Collapse presents the results in date format and I can export this into Excel if I "export excel" into a new file, however when I try and place it into the other matrix that I have developed using "sheetmodify cell(B3)" which is the correct placing the greater matrix, I get an error saying "filename could not be loaded". I've tried using xls and xlsx, but both come with the same error even though the file does exist and isn't open at the time. If I try and create a matrix and then use putexcel I return back to the same problem as before, where the results are being exported as numbers rather than in the date format.

Nonetheless I'm just going to collapse and then export only the dates in their own matrix without the other calculations and information in my loop as at least that exports the correct format.

Thanks for your help
Megan