reshape long of many variable at once in the same dataset

[Howaida Fahmy]

Hi,
I am performing a prevalence (descriptive stat) of some disease among the exposed to some drugs. the data was found in two data sets which I merged 1:1 after using max . Now I want to reshape long the variable both merged (camax to fcmax) to be the outcome and which was in the master file (drug1-drug7) to be the exposure. I tried to reshape it after merging but it was not working. Finally, I want to calculate how many are taking more than 3 drugs.
Could some one help me please?
Thanks
Here is the dataex
CODE]
* Example generated by -dataex-. For more info, type help dataex
clear
input double serial_N float(gender age_5g) int Edate float(drug1 drug2 drug3 drug4 drug5 drug6 drug7 camax supmax venmax atmax oamax fcmax) byte _merge
1 1 4 17279 . . . . . . . . . . . . . 1
2 1 . 17388 . . . . . . . . . . . . . 1
5 1 3 17314 . . . . . . . . . . . . . 3
7 2 4 17305 0 0 1 0 0 0 0 . . . . . . 3
8 2 4 17324 0 0 1 0 0 0 0 . . . . . . 1
9 1 5 17500 0 0 1 0 0 0 0 . . . . . . 1
10 2 5 17315 . . . . . . . . . . . . . 1
16 1 . 17520 . . . . . . . . . . . . . 1
17 1 . 17197 . . . . . . . . . . . . . 1
18 1 5 17171 0 0 0 1 0 0 0 . . . . . . 3
19 2 4 17345 . . . . . . . . . . . . . 3
21 2 4 17184 . . . . . . . . . . . . . 1
29 2 4 17212 . . . . . . . . . . . . . 3
30 2 5 17220 0 0 1 0 0 0 0 . . . . . . 1
32 1 . 17244 . . . . . . . . . . . . . 3
34 2 1 17325 . . . . . . . . . . . . . 1
36 2 . 17171 . . . . . . . . . . . . . 3
42 2 . 17224 . . . . . . . . . . . . . 1
43 1 . 17270 . . . . . . . . . . . . . 3
45 1 4 17177 . . . . . . . . . . . . . 1
53 1 1 17178 0 0 0 0 0 1 0 . . . . . . 3
57 1 5 17177 . . . . . . . . . . . . . 3
60 2 4 17189 0 0 1 0 0 0 0 . . . . . . 3
61 2 . 17436 . . . . . . . . . . . . . 1
63 2 2 17452 . . . . . . . . . . . . . 3
64 2 2 17195 . . . . . . . . . . . . . 1
65 2 3 17225 . . . . . . . . . . . . . 1
69 1 . 17269 . . . . . . . . . . . . . 3
71 1 4 17304 . . . . . . . . . . . . . 1
73 1 5 17363 . . . . . . . . . . . . . 1
75 2 . 17341 . . . . . . . . . . . . . 3
82 1 . 17290 . . . . . . . . . . . . . 1
84 2 . 17219 . . . . . . . . . . . . . 1
86 1 2 17252 . . . . . . . . . . . . . 3
89 1 4 17185 . . . . . . . . . . . . . 3
91 2 4 17170 0 0 1 0 0 0 1 . . . . . . 1
98 1 5 17220 . . . . . . . . . . . . . 3
99 1 1 17236 . . . . . . . . . . . . . 1
103 2 3 17204 . . . . . . . . . . . . . 3
105 1 3 17255 . . . . . . . . . . . . . 1
107 2 3 17181 0 0 1 0 0 0 0 . . . . . . 3
110 2 2 17268 . . . . . . . . . . . . . 1
111 2 . 17443 . . . . . . . . . . . . . 1
113 2 4 17217 . . . . . . . . . . . . . 3
114 2 . 17372 . . . . . . . . . . . . . 1
116 2 5 17171 . . . . . . . . . . . . . 3
117 2 . 17387 . . . . . . . . . . . . . 1
122 1 3 17171 . . . . . . . . . . . . . 1
123 2 5 17359 . . . . . . . . . . . . . 3
125 1 5 17378 0 0 0 0 0 0 1 . . . . . . 1
126 2 . 17181 . . . . . . . . . . . . . 1
128 1 2 17219 . . . . . . . . . . . . . 3
129 1 . 17183 . . . . . . . . . . . . . 3
131 2 5 17211 . . . . . . . . . . . . . 1
133 1 4 17295 . . . . . . . . . . . . . 1
139 2 5 17343 . . . . . . . . . . . . . 3
141 2 3 17398 . . . . . . . . . . . . . 3
144 2 . 17471 . . . . . . . . . . . . . 1
146 1 3 17274 . . . . . . . . . . . . . 1
147 2 4 17253 . . . . . . . . . . . . . 3
150 2 4 17196 . . . . . . . . . . . . . 3
152 2 4 17196 . . . . . . . . . . . . . 3
153 2 . 17283 . . . . . . . . . . . . . 3
154 2 . 17504 . . . . . . . . . . . . . 3
158 2 3 17169 . . . . . . . . . . . . . 3
161 2 . 17248 . . . . . . . . . . . . . 1
162 2 . 17328 . . . . . . . . . . . . . 3
163 2 4 17178 . . . . . . . . . . . . . 3
165 2 4 17279 . . . . . . . . . . . . . 1
166 2 4 17290 . . . . . . . . . . . . . 1
168 2 . 17268 . . . . . . . . . . . . . 3
169 2 . 17321 . . . . . . . . . . . . . 1
173 2 4 17511 . . . . . . . . . . . . . 1
175 2 3 17486 . . . . . . . . . . . . . 1
177 2 . 17480 . . . . . . . . . . . . . 3
179 2 . 17181 . . . . . . . . . . . . . 1
180 1 . 17170 . . . . . . . . . . . . . 1
183 2 2 17217 . . . . . . . . . . . . . 3
184 1 . 17251 . . . . . . . . . . . . . 1
185 2 . 17457 . . . . . . . . . . . . . 1
186 2 . 17217 . . . . . . . . . . . . . 1
187 2 5 17183 . . . . . . . . . . . . . 1
188 1 2 17248 . . . . . . . . . . . . . 1
189 2 1 17307 . . . . . . . . . . . . . 3
192 1 . 17298 . . . . . . . . . . . . . 1
193 2 3 17390 . . . . . . . . . . . . . 3
195 1 . 17248 . . . . . . . . . . . . . 1
198 1 . 17212 . . . . . . . . . . . . . 1
199 1 2 17282 . . . . . . . . . . . . . 1
201 1 3 17245 . . . . . . . . . . . . . 1
205 1 . 17315 . . . . . . . . . . . . . 3
206 2 . 17271 . . . . . . . . . . . . . 3
210 2 4 17170 . . . . . . . . . . . . . 3
213 1 . 17335 . . . . . . . . . . . . . 3
214 2 3 17190 . . . . . . . . . . . . . 3
215 2 3 17469 . . . . . . . . . . . . . 1
216 2 3 17378 . . . . . . . . . . . . . 1
217 1 . 17469 . . . . . . . . . . . . . 3
220 2 5 17346 . . . . . . . . . . . . . 3
224 2 . 17210 . . . . . . . . . . . . . 3
end
format %tdYYNNDD Edate
label values _merge _merge
label def _merge 1 "Master only (1)", modify
label def _merge 3 "Matched (3)", modify
[/CODE]

[Howaida Fahmy]

Hi,
Please Clyde Schechter do you have any suggestion for my post #1?
Thanks.

[Clyde Schechter]

reshape long drug, i(serial_N) j(drug_num)
rename drug exposed

Please, in the future, do not say that your code "is not working." There are many ways in which some code may fail to produce the desired results. It is anybody's guess which of those occurred. The situation is even worse when you do not show the actual code you tried. When you seek help with code, always show the code you have tried, exactly as you used it, and also always show any output (including, especially, error messages) that Stata gave you in response. To assure that everything is given exactly as it happened, you should do this by copy/pasting from your log file or the Results window directly into the Forum editor. Then surround that by code delimiters so it will be easily readable.

If you tried some code and got no error messages, but the resulting data set was not what you were looking for, then also post -dataex- output showing the results you got, and then, unless it is blatantly obvious to anybody, explain how it differed from what you were looking for.

You, and anyone else following the thread, will learn more if you show the code that "didn't work." You learn a little when somebody writes code that works for you. But you learn more if somebody can explain to you why the code you tried first failed.

[Howaida Fahmy]

Thanks for your reply! I will follow that in the future message. My problem is that, I want to reshape long the exposure (drug1-drug7) and the outcome (camax-fcmax) as I mentioned in the first post, however when I tried to reshape the drug long, it affected the number of the outcome, and I don't want that. So I wonder if there is a way to reshape both of the out come and the exposure separately without affecting each other. And also want to know how many individual are using more than three drugs. I used the code you posted, it works well with the drug but affected the number of the outcome.
thanks for help.

[Clyde Schechter]

My problem is that, I want to reshape long the exposure (drug1-drug7) and the outcome (camax-fcmax) as I mentioned in the first post,

I'm not sure I understand what you're trying to do. There are 7 drugs and 6 outcomes. So do you want to expand each of the original observations to 7*6 = 42 observations, one for each combination of drug and outcome? If so, that's just a question of doing two consecutive -reshape long-s.

As for how many people are taking more than three drugs and the number experiencing each outcome, that is best done in the original data.

//    HOW MANY PEOPLE WERE EXPOSED TO MORE THAN 3 DRUGS
egen num_drugs_exposed = rowtotal(drug*)
count if num_drugs_exposed > 3
foreach v of varlist camax-fcmax {
    display `"`v'"'
    count if `v' == 1
}

reshape long drug, i(serial_N) j(drug_num)
rename drug exposure

rename (camax-fcmax) experienced=
reshape long experienced, i(serial_N drug_num) j(outcome) string

Notes: In your example data, nobody was exposed to more than 3 drugs. Also, in your example data, all of the *max variables are always missing value, so the end result of -reshape- on those variables is rather uninteresting.

[Howaida Fahmy]

Hi again and thanks for you response!
these are the original files(first one is the drug file) and second one is the outcome
* Example generated by -dataex-. For more info, type help dataex
clear
input double serial_N str1 gender int Edate float(age_5g drug1 drug2 drug3 drug4 drug5 drug6 drug7)
1 "1" 17279 4 . . . . . . .
2 "1" 17388 . . . . . . . .
5 "1" 17314 3 . . . . . . .
7 "2" 17305 4 0 0 1 0 0 0 0
7 "2" 17381 4 . . . . . . .
7 "2" 17409 4 0 0 1 0 0 0 0
7 "2" 17493 4 0 0 1 0 0 0 0
8 "2" 17324 4 0 0 1 0 0 0 0
9 "1" 17500 5 0 0 1 0 0 0 0
10 "2" 17315 5 . . . . . . .
10 "2" 17384 5 . . . . . . .
10 "2" 17458 5 . . . . . . .
10 "2" 17498 5 . . . . . . .
16 "1" 17520 . . . . . . . .
17 "1" 17197 . . . . . . . .
17 "1" 17210 . . . . . . . .
17 "1" 17329 . . . . . . . .
17 "1" 17457 . . . . . . . .
17 "1" 17520 . . . . . . . .
18 "1" 17171 5 0 0 0 1 0 0 0
18 "1" 17230 5 0 0 0 1 0 0 0
18 "1" 17241 5 . . . . . . .
18 "1" 17267 5 0 0 0 1 0 0 0
18 "1" 17272 5 0 0 0 1 0 0 0
18 "1" 17272 5 . . . . . . .
18 "1" 17284 5 . . . . . . .
18 "1" 17300 5 . . . . . . .
18 "1" 17322 5 . . . . . . .
18 "1" 17328 5 . . . . . . .
18 "1" 17357 5 . . . . . . .
18 "1" 17357 5 0 0 0 1 0 0 0
18 "1" 17444 5 0 0 0 1 0 0 0
19 "2" 17345 4 . . . . . . .
19 "2" 17459 4 . . . . . . .
21 "2" 17184 4 . . . . . . .
21 "2" 17244 4 . . . . . . .
21 "2" 17321 4 . . . . . . .
21 "2" 17351 4 . . . . . . .
21 "2" 17433 4 . . . . . . .
21 "2" 17490 4 . . . . . . .
21 "2" 17518 4 . . . . . . .
29 "2" 17212 4 . . . . . . .
29 "2" 17337 4 0 0 0 0 0 0 1
29 "2" 17378 4 0 0 0 0 0 0 1
29 "2" 17414 4 . . . . . . .
29 "2" 17421 4 0 0 0 0 0 0 1
29 "2" 17421 4 . . . . . . .
29 "2" 17506 4 . . . . . . .
30 "2" 17220 5 0 0 1 0 0 0 0
30 "2" 17249 5 0 0 1 0 0 0 0
30 "2" 17352 5 0 0 1 0 0 0 0
30 "2" 17352 5 . . . . . . .
30 "2" 17361 5 0 0 0 0 0 0 1
30 "2" 17393 5 0 0 1 0 0 0 0
30 "2" 17499 5 . . . . . . .
32 "1" 17244 . . . . . . . .
34 "2" 17325 1 . . . . . . .
36 "2" 17171 . . . . . . . .
36 "2" 17230 . . . . . . . .
36 "2" 17505 . . . . . . . .
42 "2" 17224 . . . . . . . .
43 "1" 17270 . . . . . . . .
45 "1" 17177 4 . . . . . . .
45 "1" 17183 4 . . . . . . .
45 "1" 17192 4 . . . . . . .
45 "1" 17206 4 . . . . . . .
45 "1" 17219 4 . . . . . . .
45 "1" 17233 4 . . . . . . .
45 "1" 17248 4 . . . . . . .
45 "1" 17253 4 . . . . . . .
45 "1" 17259 4 . . . . . . .
45 "1" 17276 4 . . . . . . .
45 "1" 17290 4 . . . . . . .
45 "1" 17304 4 . . . . . . .
45 "1" 17318 4 . . . . . . .
45 "1" 17331 4 . . . . . . .
45 "1" 17342 4 . . . . . . .
45 "1" 17345 4 . . . . . . .
45 "1" 17354 4 . . . . . . .
45 "1" 17359 4 . . . . . . .
45 "1" 17360 4 . . . . . . .
45 "1" 17373 4 . . . . . . .
45 "1" 17388 4 . . . . . . .
45 "1" 17401 4 . . . . . . .
45 "1" 17402 4 . . . . . . .
45 "1" 17415 4 . . . . . . .
45 "1" 17429 4 . . . . . . .
45 "1" 17443 4 . . . . . . .
45 "1" 17458 4 . . . . . . .
45 "1" 17472 4 . . . . . . .
45 "1" 17485 4 . . . . . . .
45 "1" 17499 4 . . . . . . .
45 "1" 17511 4 . . . . . . .
45 "1" 17521 4 . . . . . . .
53 "1" 17178 1 0 0 0 0 0 1 0
53 "1" 17225 1 1 0 0 0 0 0 0
53 "1" 17231 1 1 0 0 0 0 0 0
53 "1" 17266 1 0 0 0 0 0 1 0
53 "1" 17315 1 1 0 0 0 0 0 0
53 "1" 17372 1 1 0 0 0 0 0 0
****
second file
* Example generated by -dataex-. For more info, type help dataex
clear
input long serial_N str1 gender float(age_5g ca sup ven at ot fc)
7 "2" 4 . . . . . .
29 "2" 4 . . . . . .
36 "2" 4 . . . . . .
75 "2" 5 . . . . . .
75 "2" 5 . . . . . .
158 "2" 3 . . . . . .
168 "2" 5 . . . . . .
177 "2" 5 . . . . . .
228 "2" 5 . . . . . .
242 "2" 5 . . . . . .
267 "2" 1 . . . . . .
267 "2" 1 . . . . . .
267 "2" 1 . . . . . .
267 "2" 1 . . . . . .
267 "2" 1 . . . . . .
267 "2" 1 . . . . . .
282 "1" 1 . . . . . .
282 "1" 1 . . . . . .
295 "2" 5 . . . . . .
342 "2" 3 . . . . . .
350 "2" 5 . . . . . .
351 "2" 4 . . . . . .
381 "2" 4 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
392 "1" 3 . . . . . .
407 "2" 5 . . . . . .
410 "1" 4 . . . . . .
440 "2" 5 . . . . . .
504 "2" 2 . . . . . .
509 "2" 4 . . . . . .
519 "2" 3 . . . . . .
612 "2" 5 . . . . . .
628 "1" 3 . . . . . .
695 "2" 5 . . . . . .
704 "2" 4 . . . . . .
728 "1" 1 . . . . . .
738 "1" 1 . . . . . .
796 "1" 4 . . . . . .
800 "2" 5 . . . . . .
818 "1" 3 . . . . . .
835 "1" 3 . . . . . .
875 "2" 4 . . . . . .
938 "1" 5 . . . . . .
949 "2" 5 . . . . . .
973 "2" 4 . . . . . .
975 "2" 5 . . . . . .
995 "2" 5 . . . . . .
1012 "2" 4 . . . . . .
1012 "2" 4 . . . . . .
1012 "2" 4 . . . . . .
1025 "1" 4 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1028 "1" 5 . . . . . .
1037 "1" 5 . . . . . .
1047 "2" 2 . . . . . .
1056 "1" 5 . . . . . .
1076 "1" 4 . . . . . .
1078 "2" 4 . . . . . .
1081 "2" 5 . . . . . .
1158 "2" 3 . . . . . .
1158 "2" 3 . . . . . .
1191 "1" 3 . . . . . .
1230 "2" 4 . . . . . .
1237 "2" 2 . . . . . .
1264 "2" 5 . . . . . .
1342 "1" 3 . . . . . .
1354 "2" 1 . . . . . .
1354 "2" 1 . . . . . .
So I want to merge both files, so what I did firstly, I used max to be able to merge one to one. then after that I thought in reshaping them long for further analysis
By the way, as you said I want to do two Consecutive reshaping . I have done the one you have posted earlier after merging one to one, but I found that the number of drugs have been changed a lot from the original file. hope I was able to clarify what I need.
thanks

[Howaida Fahmy]

Hi again.
I forgot to mention that in the first post, I kept the first exposure of the indvidual for each drug then I used max function then merged 1:1
Thanks