create a table from loops

[rosalie loewen]

Hello, I have a series of loops. Right now they are set to display, but I want them to write the data to a xls file to create a table. I have tried messing around with eststo but I can't seem to get it yet. I guess I would have to replace the display with a different command...

egen id=group(thing otherthing)
forvalues i=1989(1)2003{
display `i' distinct id if year1==`i'
}
forvalues i=1989(1)2003{
display `i' distinct id if urban==1 & year1==`i'
}
forvalues i=1989(1)2003{
display `i' distinct id if urban==3 & year1==`i'
}

[Clyde Schechter]

I think this will do it:

capture postutil clear
tempfile results
postfile handle year n_distinct ndistinct_urban_1 ndistinct_urban_3 using `results'

forvalues y = 1989/2003 {
    distinct id if year1 == `y'
    local n_distinct = r(n_distinct)
    distinct id if year1 == `y' & urban == 1
    local n_distinct_urban_1 = r(n_distinct)
    distinct id if year1 == `y' & urban == 3
    local n_distinct_urban_3 = r(n_distinct)
    post handle (`y') (`n_distinct') (`n_distinct_urban_1') (`n_distinct_urban_3')
}
postclose handle
use `results', clear

export excel using my_results_spreadsheet.xlsx

Note: As you did not provide an example data to work with, this code is untested. So there may be typos or other errors in it, but the gist of it is correct.

In the future, when asking for help with code, please show example data, and use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.



When asking for help with code, always show example data. When showing example data, always use -dataex-.