"Transpose" data [in the excel sense] when exporting to excel

[Elizabeth Pisani]

I am using Stata 19.5.
I have a dataset of around 15 variables that I would like to export to an excel sheet. The FIRST variable is year, and it is unique; (so “year” is essentially an observation: one row per year). The other variables are all numeric, and give values for the given year. Data example below.

In exporting the data, I would like to do the equivalent of the excel function “transpose”’ i.e. instead of 6 rows with 15 colums, I want to export it as 6 colums with 15 rows. Year, [formerly my var1 equivalent] will become the first row, and thus column headers in the spreadsheet. The other 14 variables (var2-var-15) will each be a row; If there is a export excel option that allows me to do this, (which I have not yet identified!), I would export without varnames, and shart in cell B1 so that I could use column A to write in variable definitions.

(I also explored putexcel options but without joy)

* Example generated by -dataex-. For more info, type help dataex
clear
input int year double total float supply long(possession use) float study long drugs_study_reg int supply_reg long(crime_all_reg crime_all_dis civil_reg) float(drugs_all_reg pct_drugs cases_index dda)
2019         175038     26803 66378 81857    148235 157286  803 1961402 2014502 449793 158089   8.01906 83456  9170
2020         146045     23536 56271 66238    122509 130572  590 1549416 1466541 317028 131162 8.4271755 65623  8435
2021         129604     25277 51637 52690    104327 146180 1114 1522005 1562129 303335 147294  9.604436 52974  6870
2022         163697     27283 67273 69141    136414 188288 1117 2199072 2218775 353307 189405  8.562157 50813 10438
2023         179799     26814 67985 85000    152985 197799 1017 2614870 2589802 431283 198816  7.564391 52444 12180
2024 188208.2890625 23832.445     .     . 164375.84 222971  973 2774734 2773148 476236 223944  8.035761     .     .
end

I need to repeat this for several data subsets, and update as new data come in, so any help with automating it would be greatly appreciated.

[Clyde Schechter]

I know of no options to -export excel- that will do this. But you can transpose the data set within Stata and then export it to Excel. Like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input int year double total float supply long(possession use) float study long drugs_study_reg int supply_reg long(crime_all_reg crime_all_dis civil_reg) float(drugs_all_reg pct_drugs cases_index dda)
2019 175038 26803 66378 81857 148235 157286 803 1961402 2014502 449793 158089 8.01906 83456 9170
2020 146045 23536 56271 66238 122509 130572 590 1549416 1466541 317028 131162 8.4271755 65623 8435
2021 129604 25277 51637 52690 104327 146180 1114 1522005 1562129 303335 147294 9.604436 52974 6870
2022 163697 27283 67273 69141 136414 188288 1117 2199072 2218775 353307 189405 8.562157 50813 10438
2023 179799 26814 67985 85000 152985 197799 1017 2614870 2589802 431283 198816 7.564391 52444 12180
2024 188208.2890625 23832.445 . . 164375.84 222971 973 2774734 2773148 476236 223944 8.035761 . .
end

xpose, clear varname
order _varname, first
rename _varname variable

export excel using my_spreadsheet.xlsx, replace // N.B. NO -firstrow()- OPTION

[Hemanshu Kumar]

Just a quick note to mention that while xpose is up to the task of transposing numeric variables, as is the case with your data extract, it does not do well with string variables. From its help doc:

All new variables -- that is, those created by the transposition -- are made the default storage type. Thus any original variables that were strings will result in observations containing missing values.

If you (or an interested future reader) has a dataset with string variables, you may want to check out the community-contributed commands sxpose and sxpose2, both available via SSC.

[Elizabeth Pisani]

Thanks, both. That's very helpful. I had tried xpose but without the clear varname option, with confusing results. Your code works beautifully.