Challenge generating descriptive tables for longitudinal data

[Kassandra Martinchek]

Hello all-- I am trying to generate some semi-complex summary tables in Excel from Stata and am struggling to find commands and code that can generate the output I need.

I have longitudinal individual-level data (which I am unable to use -dataex- to share) that is formatted long (meaning each individual is observed in 11 time periods, and each individual in each time period is one row). Each individual is coded using five indicator variables (coded 0,1; variables are: prime, subprime, majority black, majority hispanic, high poverty) in addition to observing the variables of interest, which are also dichotomous.

For each variable of interest, I would like to generate output that shows the share of individuals in that time period with that particular outcome (or that share of 1's observed). I want to do this for all individuals in the data, as well as subgroups based on the indicator variables. Below, is what I am hoping to generate:

With tabulate, I can generate which individuals in a particular time period have or do not have a particular outcome of interest (below for all consumers, but using the "if" statement I can generate for subgroups as well):

tabulate time variable_of_interest

I have tried to use putexcel to pull this into a spreadsheet, but get rows of time periods and two columns for each of the two indicator values (0 and 1):

tabulate time variable_of_interest, matcell(freq) matrow(names)
putexcel set "temp_, sheet("variable_of_interest") modify
putexcel A1 = matrix(names) B1 =  matrix((freq/((r(N))/11)))

However, I struggle to append the subgroup analyses to this, or generate one column representing the 1's for the outcome of interest.

Does anyone know a good way to generate the desired output table (using either putexcel or other code)?

Thank you in advance for any insight you can provide.

[Andrew Musau]

I have longitudinal individual-level data (which I am unable to use -dataex- to share)

You are transferring the burden of creating a data example to those who may be able to help you. If your reason for not providing a data example is due to privacy concerns, then that is covered in FAQ Advice #12.