Output string variables using putexcel and tab functions

[John Barbieri]

Hi everyone,

I have a datafile that includes a list of antibiotic courses and associated medical diagnoses (which are stored as a string variable, varname diag). I am trying to output a list of the most common associated medical diagnoses using the putexcel commands. Ideally, I would like to use some code such as this:

tab diag, sort matcell(cell) matrow(names)
putexcel A1 =matrix(names)
putexcel A2 = matrix(cell)

However, because diag is a string variable, I get an error that matrow is not allowed. I can solve this problem by first encoding the variable diag:

encode diag, generate(diag1)
tab diag1, sort matcell(cell) matrow(names)
putexcel A1 =matrix(names)
putexcel A2 = matrix(cell)

However, there are over 1000 potential diagnosis codes in the dataset, so the resulting Excel output becomes difficult to interpret without an associated lookup table to decode the encoded variable diag1 (which I am not sure how to generate). I have tried looking through prior posts for a solution to this problem but could not find one. Does anyone have any suggestions about how I can either output the string content in diag using putexcel or how to generate and export to Excel a lookup table to allow me to decode diag1 if I encode the variable diag to allow for the above code to work?

Hope that makes sense, please let me know if any questions.

Thanks!
John

[Nick Cox]

I don't know much about Excel or use it from choice and in particular I don't use putexcel. That just means here that I can't advise on it.

But I wouldn't start from where you start. You want a table of sorted frequencies, which makes perfect sense, but export to a Stata matrix from tabulate is, as you've found, often awkward or limited.

One way forward is just to contract your data to a new dataset. Then you can sort on the frequencies and use export excel Or sort within Excel, as you prefer.

Incidentally, putexcel and tabulate are commands, not functions. In Stata the two terms are not synonyms.