Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • reshape long of many variable at once in the same dataset

    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]

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

    Comment


    • #3
      Code:
      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.

      Comment


      • #4
        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.

        Comment


        • #5
          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.
          Code:
          //    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.

          Comment


          • #6
            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

            Comment


            • #7
              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

              Comment

              Working...
              X