converting long to wide - creating a j variable

[Tom Lawson]

Hello everyone. Thanks for looking at my post. I hope you can help a PhD candidate...

I have a data set with 170 subjects, each of whom have daily repeated measures for up to 7 days, in addition to many one-time measures like demographics, baseline characteristics, and outcomes. My data is in long form and each subject has a different number of rows. In long shape it is 1800 rows and there are about 100 columns. I need to convert the whole thing to wide format to work on a mixed effects logistic regression model. I didn't have a good "j" variable for the "reshape wide" command. I tried creating one which I called j1 with [by record_id: generate j1 = _n] which appears to give me a unique variable within each subject, but when I tried [reshape wide age race ethnicity ... [ 100 variables], i(record_id) j(j1)]. I got the command to run and my data is reshaped to (really)wide, but those measures which should only occur once (e.g., age, BMI, mortality) now are duplicated 19 times (i.e., age1, age2, ..., age19). when I tried to reshape with only the daily repeating measures, the command won't run and I get "variable variablename not constant within record_id." I could go through air drop almost 2,000 useless variables, but is there a more eloquent way?

I'm using Stata/IC 14.2 running in a Microsoft remote desktop on a mac. I hope this post was both precise enough and concise enough.

Thank You,
Tom Lawson

[Daniel Schaefer]

It seems like you will want to use the -xtmelogit- command to estimate your model, which I believe (like all xt commands) expects the data in long format. I understand the wide format is not considered particularly useful in Stata, generally speaking.

[Clyde Schechter]

First, let me be clear that I strongly agree with Daniel Schaefer in #2 that, as best one can infer from #1, reshaping this data to wide layout would be a serious mistake. -melogit- (the current name for the mixed effects logistic regression command) definitely expects the data to be in long, not wide, layout.

That said, as it isn't entirely clear what else you plan to do and there is a small chance that you will later actually have a reason to go to wide layout, the way to do that would be in the -reshape- command itself not to list the variables that should only occur once in the command's variable list. List only those variables that you want to have widened, remembering that the variables that you don't include must be constant within record_id.

[William Lisowski]

I hope this post was both precise enough and concise enough.

To understand the expected balance between precision and concision, please take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It is particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data - or invented data with similar characteristics, if your actual data is restricted - as described in section 12 of the FAQ.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

With that said, I too agree with Clyde and Daniel. The experienced users here generally agree that, with few exceptions, Stata makes it much more straightforward to accomplish complex analyses using a long layout of your data rather than a wide layout of the same data, even if it's not obvious to you at first glance how to accomplish what you need. You should try to achieve what you need with the data organized as it currently is, and seek the help of Statalist in doing so.