Export "missings report" to Excel by year

[Anna Emilie]

Hi,

I'm fairly new to Stata and I'm having some issues exporting command output to excel.
As far as I understand, there is a difference in how output from commands and functions are stored and this also means that the way to export to Excel using putexcel differs.
So far I'm only able to generate the lists:

sort year
by year: missings report
return list

which gives me

scalars:
r(N) = 1883274
macros: r(varlist)) : "variable1_variable2_"


How can I use putexcel to get this list into Excel?

Thanks!

[Andrew Musau]

missings is from the Stata Journal (FAQ Advice #12). Maybe better if you compile what is to be exported and then use export excel. This is not tested and may contain errors and typos.

levelsof year, local(years)
frame create results
frame results{
    set obs `=wordcount("`years'")'
    g N= .
    g varlist=""
}
local i 1
foreach y of local years{
    missings report if year==`y'
    frame results: replace N= `r(N)' in `i'
    frame results: replace varlist= "`r(varlist)'" in `i'
    local ++i
}
frame results: export excel using myfile, keepcellfmt replace
frame drop results