How to merge variables after using reshape wide command

[Pratibha Singh]

there is data set

Id	age	antibiotics
1	23	2
2	24	1
2	34	1
3	32	2
3	22	1
4	24	2
4	23	2
4	25	2
4	23	1
4	25	1

after giving reshape wide command

bysort Id :gen ab=_n
replace ab=0 if ab==.
reshape wide age antibiotics, i(id) j(ab)

the data Looks like

id	age1	age2	age3	age4	age5	antibiotics1	antibiotics2	antibiotics3	antibiotics4	antibiotics5
1	23					2
2	24	34				1	1
3	32	32				2	1
4	24	23	25	23	25	2	2	2	1	1

Now I want single age and single antibiotics column without the duplication of ID.

Please suggest what to do in Stata 14

[Jared Greathouse]

Use dataex please. I won't look at this until you've reformatted this such that anyone else here can work with the dataset you've got.


So, please give your example data.

[William Lisowski]

Perhaps this is what you want.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id age antibiotics)
1 23 2
2 24 1
2 34 1
3 32 2
3 22 1
4 24 2
4 23 2
4 25 2
4 23 1
4 25 1
end
bysort id age (antibiotics): generate ab = _n
reshape wide antibiotics, i(id age) j(ab)
list, sepby(id) abbreviate(20)

. list, sepby(id) abbreviate(20)

     +----------------------------------------+
     | id   age   antibiotics1   antibiotics2 |
     |----------------------------------------|
  1. |  1    23              2              . |
     |----------------------------------------|
  2. |  2    24              1              . |
  3. |  2    34              1              . |
     |----------------------------------------|
  4. |  3    22              1              . |
  5. |  3    32              2              . |
     |----------------------------------------|
  6. |  4    23              1              2 |
  7. |  4    24              2              . |
  8. |  4    25              1              2 |
     +----------------------------------------+

[Pratibha Singh]

There is a data which as :


id age antibiotics |
|------------------------|
1. | 1 23 1 |
|------------------------|
2. | 2 22 2 |
3. | 2 24 1 |
|------------------------|
4. | 3 23 2 |
5. | 3 34 2 |
|------------------------|
6. | 4 33 1 |
7. | 4 26 2 |
8. | 4 25 1 |
9. | 4 21 2 |



after giving reshape wide command

bysort Id :gen ab=_n
replace ab=0 if ab==.
reshape wide age antibiotics, i(id) j(ab)

the data Looks like
+--------------------------------------------------------+
| Id age1 age2 age3 age4 ab1 ab2 ab3 ab4 |
|--------------------------------------------------------
1. | 1 23 . . . 1 . . .
|--------------------------------------------------------
2. | 2 22 23 . . 2 1 . .
|--------------------------------------------------------
3. | 3 23 34 . . 2 2 . .
|-------------------------------------------------------
4. | 4 33 26 25 21 1 2 1 2
+--------------------------------------------------------+

Now I want single age and single antibiotics column without the duplication of ID.

Please suggest what to do in Stata 14

Thanks and regards

[William Lisowski]

ID 4 data has ages 21 25 26 and 33. How do you choose just one age? Explain that and show what you want the resulting observation to look like.

[Pratibha Singh]

it is follow-up data, and result what I want is to merge multiple column into one

[William Lisowski]

In Stata one variable (what you call "column") in one observation (what you might call a "row") can contain at most a single numeric value.

[Pratibha Singh]

please use use this data for helping me out

Id	hospital Visit	Antiobiotics
1	Yes	YES
2	NO	NO
2	YES	NO
3	YES	NO
4	YES	YES
4	NO	NO
4	NO	NO
4	YES	NO

Afterafter giving reshape wide command

bysort Id :gen ab=_n
replace ab=0 if ab==.
reshape wide hospital visit antibiotics, i(id) j(ab)

the data Looks changes into this form

ID	HOSPITAL VISIT1	HOSPITAL VISIT2	HOSPITAL VISIT3	HOSPITAL VISIT4	ANTIOBIOTICS1	ANTIBIOTICS 2	ANTIOBIOTICS 3	ANTIBIOTICS 4
1	YES				YES
2	NO	YES			NO	NO
3	YES				NO
4	YES	NO	NO	YES	YES	NO	NO	YES

Now I want single Hospital visit and single antibiotics column without the duplication of ID.

Please suggest what to do in Stata 14

Thanks and regards

[Pratibha Singh]

Dear Stata users,

kindly give solution to my question asap.

[Jared Greathouse]

Pratibha Singh people will usually help you if you follow the rules of how to ask a question. If you don't provide your data example using dataex along with your code, people will likely not answer it because it's really a waste of time.

If we can't reproduce your problem with code and data, and if you refuse to share your example data using dataex, then it's just not possible for us to help you.

Please, read the FAQ, especially sections 10 and 12 about how to ask questions. People on Statalist want to help, but in order for us to do so, you must help us, help you.

[William Lisowski]

Dear Stata users,

kindly give solution to my question asap.

You can start by addressing my question in post #5 above. What do you want the result to look like? What does it mean to "merge multiple columns into one"?