Exporting output from list command into excel

[Ola Aboukhsaiwan]

Hi all,

I've written a for loop that spits back top 10 rankings of a given variable using 6 types of scores. I've used the list command with the required if condition to do this. My question is now how to export this output to excel. I've tried to use putexcel but it's not clear that it's compatible with the output arising from using the list command. Are there alternative commands that could be used to generate this list and compatible with putexcel or a different command to generate output into excel altogether?

Thanks a lot in advance!

[Joseph Coveney]

I've used the list command with the required if condition to do this. My question is now how to export this output to excel. . . . Are there alternative commands that could be used . . .

Rather than use list, how about using the same if conditions with keep? And then export excel.

If you're concerned about losing data, just use preserve beforehand, or else park the subsetted dataset into a frame and export from there.

[Maarten Buis]

list is just there to display things in the output window. It is not there for the kind of things you want to do.

Here I assume that your score variables have all different values, i.e. there are no ties. Ties (e.g. a shared first place) make rankings difficult, or at least a bit arbitrary. When ties occur, you need to decide what rules you want to use to deal with them, and adjust the code accordingly.

You can refer to a value of a variable x for the kth observation as x[k]. So if you have a variable with names called name, and your score is called score, then you can sort on score, and than get the name of the best (highest) score by getting x[`=_N'], the second best with x[`=_N-1'], etc.

So here is an example that applies this principle:

clear all
sysuse auto

putexcel set c:\temp\rankings, replace

local k = 0
foreach score of varlist price mpg headroom {
    local k = `k' + 1
    mata: st_local("col", numtobase26(strtoreal(st_local("k"))))
    putexcel `col'1 = "`score'"

    sort `score'
    forvalues i = 1/5 {
        local j = `i'+1
        putexcel `col'`j' = make[`=_N-`i'+1']
    }
}

putexcel save

The line mata: st_local("col", numtobase26(strtoreal(st_local("k")))) is just a bit of Mata magic to store the column name used in excel for the first, second, third, etc column in the local macro `col'

[Attaullah Shah]

For asdocx users:
asdocx can export the output from list command to Excel, Word, HTML, or LaTeX. With the label option, both variable labels and values labels can be exported. Here is a working example:

sysuse nlsw88
asdocx list idcode age race married industry, label save(File.xlsx) replace