Merging variables into one variable after rehaping

[Kate Dubberley]

Hi everyone, this is my first post to Statalist so I kindly request some patience as well as welcome feedback regarding my post format.

I had a long database that I have reshaped to wide.

This is my reshape code:

Accordingly, this has generated multiple age and multiple sex variables per observation (e.g. age1, age2, age3), whereas in the original long format, I only had one age and one sex variable per observation. Below you can see an example which also offers more details regarding the storage types, etc.

I would like to merge the age variables into one per observation. I would also like to do the same for the multiple sex variables.

I am also having trouble knowing exactly which term to use to address this issue (i.e. I am still learning the language of STATA). For example, having done my own research, I've learned that I am NOT looking to stack or concatenate (as this would require I reshape back to long).

I therefore have two questions:
1. Can someone please confirm I am looking to MERGE these variables into one new variable #STATALanguage?
2. Can someone please offer guidance as to how I can accomplish this.

Kind regards,
Kate

[Brina Seidel]

You probably do not need to use the merge command - that is most useful for combining different data sets.

To figure out what command you want to use, we need to know exactly how you wish to combine age1, age2, age3, etc. For example, do you want to add the values? Concatenate them in a list? etc.

A guess about what you might want to do (ignore if I'm wrong): Do all of your original rows with the same admission_id have the same age and sex, even though they have different dad_diag_occurence values? If so, then you don't want to include them in the list of values to reshape. You could try:

reshape wide diagnosis total_acute_los icd10_diag_code_desc, i(admission_id age sex) j(dad_diag_occurence)

For the future, you can use CODE tags (click the # button above where you type) instead of pasting in screenshots of your code.

[Kate Dubberley]

My goodness, Brina, you solved it! What I've learned from your post is that 1. I can prevent this via how I reshape and 2. I can list more than one key variable ('i').

And, for good measure, I've included the working code below using the CODE tag you mentioned.

 reshape wide diagnosis total_acute_los icd10_diag_code_desc, i(admission_id age sex) j(dad_diag_occurrence)

Thank you,
Kate

[William Lisowski]

Since you invite feedback on posting, let me expand on the advice in post #2 by calling to your attention to the Statalist FAQ linked to from the top of the page. Note especially sections 9-12 on how to best pose your question. It includes the advice on CODE tags as well as other ways to improve the likelihood that your question will lead to the help you need.

[Kate Dubberley]

Thank you, William!