Announcement

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

  • Replacing ICD value to another value in a dataset

    Dear Stata Expert
    I would much appreicate your help on this issue.
    In a dataset where variabled DX1 - DX5
    A specific procedure was coded with ICD code Howe want to replace that code in the whole dataset with another ICD code.
    Wonder which loop function would do that.
    Meaning, to look up all variables from DX1 up to DX5 and automatically capture any ICD with the original procedure code and replace it with the new one.
    For example, i want to replace whatever ICD value coded in any of the variables DX1 up to DX5 that is equal to "41011" to be automatically converted to "41091" . Another level, if want to capture any of the values "41071" "41402" "41401" "4280" "42821" and replace it with "41091" . (these are aribitary chosen values just for demonstration)
    I wonder which loop function would do that.
    Thanks a lot



    ----------------------- copy starting from the next line -----------------------
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input str5(DX1 DX2 DX3 DX4 DX5)
    "41091" "51881" "0389"  "99592" "42833"
    "41001" "42821" "78551" "4280"  "4271"
    "41071" "78551" "2761"  "40390" "73016"
    "41011" "42823" "5809"  "5854"  "41401"
    "41041" "78551" "56881" "9980"  "2851"
    "41071" "25000" "4019"  "41401" " "    
    "41051" "78551" "42511" "2449"  "3051"
    "41071" "4019"  "41401" "2449"  "7242"
    "41041" "3051"  "41401" "V6549" " "    
    "41031" "2724"  "42789" "3051"  "V173"
    "41071" "42843" "5849"  "51852" "41401"
    "41071" "41401" "4019"  "2724"  "42611"
    "41041" "4260"  "25002" "3051"  "2724"
    "41071" "41401" "3960"  "4293"  "4272"
    "41071" "41401" "4019"  "27503" "3051"
    "41071" "2762"  "4280"  "5849"  "5853"
    "41071" "42821" "41401" "4148"  "4280"
    "41071" "41401" "4019"  "2449"  "2724"
    "41091" "2875"  "41401" "2724"  "4019"
    "41071" "40200" "5849"  "5920"  "41401"
    "41071" "5849"  "41401" "79029" "4019"
    "41041" "25000" "2724"  "41401" "4142"
    "41091" "41401" "4019"  "2720"  "4439"
    "41071" "2724"  "27800" "4019"  "49390"
    "41091" "41401" "25000" "V1582" "496"  
    "41071" "42833" "99672" "42518" "4280"
    "41071" "41402" "41401" "4280"  "42821"
    "41001" "4280"  "42820" "41401" "4148"
    "41041" "99664" "41401" "0413"  "25002"
    "41071" "29620" "41401" "34690" "2724"
    "41071" "4254"  "41401" "4019"  "2724"
    "41041" "41401" "4142"  "2724"  "42789"
    "41071" "41401" "25000" "4019"  "2724"
    "41071" "41401" "2449"  " "     " "    
    "41021" "99672" "41401" "4019"  "2724"
    "41071" "41402" "4142"  "42830" "4280"
    "41071" "41401" "4019"  "25000" "73300"
    "41041" "51881" "486"   "42841" "78551"
    "41041" "30000" "4019"  "4240"  "V1582"
    "41071" "41401" "185"   "5718"  " "    
    "41071" "41401" "4019"  "2724"  "30501"
    "41041" "51851" "99739" "2851"  "41401"
    "41031" "2449"  "41401" "3051"  "4019"
    "41071" "2761"  "41401" "4148"  "4019"
    "41071" "41042" "2724"  "43889" "4019"
    "41041" "42789" "V1582" "V1046" "V1301"
    "41011" "51881" "78551" "4275"  "4271"
    "41071" "41401" "4019"  "496"   "30400"
    "41011" "2948"  "5859"  "V1582" "2724"
    "41071" "4271"  "41401" "4019"  "7859"
    "41071" "41401" "4019"  "3051"  "27800"
    "41071" "41405" "25000" "2724"  "V1582"
    "41071" "41401" "2724"  "42769" "2720"
    "41071" "2724"  "41401" "311"   "30000"
    "41011" "412"   "41401" "4019"  "2724"
    "41041" "42613" "2720"  "41401" " "    
    "41041" "42741" "78550" "99801" "99601"
    "41071" "25002" "4254"  "41401" "3051"
    "41041" "41401" "496"   "3051"  "2724"
    "41041" "3051"  "41401" " "     " "    
    "41011" "4240"  "2724"  "41401" "60000"
    "41071" "4370"  "2662"  "2841"  "41401"
    "41071" "99812" "41401" "4019"  "2724"
    "41011" "2859"  "V667"  "3051"  "4275"
    "41031" "40391" "V5867" "V4511" "3572"
    "41071" "42821" "5849"  "5855"  "58881"
    "41041" "7100"  "41401" "4019"  "2724"
    "41041" "2722"  "3051"  "4589"  "49390"
    "41071" "41404" "41401" "V066"  "4019"
    "41021" "4142"  "7840"  "V5867" "27652"
    "41041" "25000" "4271"  "4264"  "99672"
    "41011" "41401" "3051"  "2724"  "4019"
    "41071" "4254"  "V0382" "V4502" "4019"
    "41071" "41401" "25000" "4019"  "V1581"
    "41071" "41401" "25000" "2724"  " "    
    "41071" "42823" "41401" "43310" "3051"
    "41021" "25062" "2720"  "2724"  "27800"
    "41011" "41401" "V5866" "2449"  "7109"
    "41071" "42789" "32723" "3051"  "4264"
    "41041" "2724"  "27800" "41401" "V5866"
    "41071" "2768"  "2851"  "41401" "496"  
    "41071" "42821" "36043" "4280"  "41401"
    "41041" "41401" "2724"  "4019"  "725"  
    "41011" "29620" "41401" "2724"  "4019"
    "41071" "41404" "41401" "4142"  "2724"
    "41071" "2761"  "41401" "4142"  "25002"
    "41071" "40390" "5852"  "30000" "412"  
    "41071" "25002" "4019"  "2724"  "41401"
    "41071" "2724"  "41401" "V4582" "4019"
    "41061" "27800" "41401" "4019"  "3051"
    "41011" "41401" "V1302" " "     " "    
    "41071" "2761"  "3051"  "V4364" "30001"
    "41011" "4142"  "41401" "4019"  "3051"
    "41051" "42821" "4280"  "2724"  "4011"
    "41041" "4019"  "3659"  "41401" "V4582"
    "41071" "34590" "30981" "27800" "31401"
    "41041" "42741" "4275"  "41401" "4019"
    "41041" "V4582" "412"   "4019"  "2720"
    "41071" "4168"  "4280"  "41401" "V4582"
    "41040" "V4582" "4019"  "4142"  "41402"
    end
    ------------------ copy up to and including the previous line ------------------
    Last edited by ashraf abugroun; 13 Feb 2019, 20:58.

  • #2
    You may try this code:
    foreach v of var _all { replace `v'="41091" if `v'=="41071" | `v'=="41402" | `v'=="41401" | `v'=="4280" | `v'=="42821" } ​

    Comment


    • #3
      An equivalent approach that is easier to type would be:
      Code:
      foreach v of varlist DX* {
          replace `v' = "41091" if inlist(`v', "41071", "41402", "41401", "4280", "42821")
      }
      Clearly, as the list of codes that needs to be replaced gets long, this becomes increasingly cumbersome. (In fact, -inlist()- has a limit of 10 string arguments, so you can't actually push this much farther without adding more code. Also, if the number of replacement codes gets very long, this also becomes cumbersome. I don't want to make up a complicated example, so let's just pretend that this list of codes that needs to be replaced with 41091 is too long for the above approach to be practical.

      The first step would be to create a new data file containing just one variable, called DX that contains the codes "41071", etc. that need to be replaced, one per observation. Save that as a real or temporary data file (the code below saves it as a temporary file). Then load the original data and -reshape- it to long; merge that with the file of codes to be replaced, and then do the replacement wherever there is a match. Then you can -reshape- back to wide if you like at the end. This approach scales very well.

      Code:
      // CREATE A FILE OF CODES THAT NEED REPLACEMENT
      clear
      input str5 DX
      "41071"
      "41402"
      "41401"
      "4280"
      "42821"
      end
      tempfile to_replace
      save `to_replace'
      
      // LOAD THE DATA THAT NEEDS REPLACEMENTS
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input str5(DX1 DX2 DX3 DX4 DX5)
      "41091" "51881" "0389"  "99592" "42833"
      "41001" "42821" "78551" "4280"  "4271"
      "41071" "78551" "2761"  "40390" "73016"
      "41011" "42823" "5809"  "5854"  "41401"
      "41041" "78551" "56881" "9980"  "2851"
      "41071" "25000" "4019"  "41401" " "    
      "41051" "78551" "42511" "2449"  "3051"
      "41071" "4019"  "41401" "2449"  "7242"
      "41041" "3051"  "41401" "V6549" " "    
      "41031" "2724"  "42789" "3051"  "V173"
      "41071" "42843" "5849"  "51852" "41401"
      "41071" "41401" "4019"  "2724"  "42611"
      "41041" "4260"  "25002" "3051"  "2724"
      "41071" "41401" "3960"  "4293"  "4272"
      "41071" "41401" "4019"  "27503" "3051"
      "41071" "2762"  "4280"  "5849"  "5853"
      "41071" "42821" "41401" "4148"  "4280"
      "41071" "41401" "4019"  "2449"  "2724"
      "41091" "2875"  "41401" "2724"  "4019"
      "41071" "40200" "5849"  "5920"  "41401"
      "41071" "5849"  "41401" "79029" "4019"
      "41041" "25000" "2724"  "41401" "4142"
      "41091" "41401" "4019"  "2720"  "4439"
      "41071" "2724"  "27800" "4019"  "49390"
      "41091" "41401" "25000" "V1582" "496"  
      "41071" "42833" "99672" "42518" "4280"
      "41071" "41402" "41401" "4280"  "42821"
      "41001" "4280"  "42820" "41401" "4148"
      "41041" "99664" "41401" "0413"  "25002"
      "41071" "29620" "41401" "34690" "2724"
      "41071" "4254"  "41401" "4019"  "2724"
      "41041" "41401" "4142"  "2724"  "42789"
      "41071" "41401" "25000" "4019"  "2724"
      "41071" "41401" "2449"  " "     " "    
      "41021" "99672" "41401" "4019"  "2724"
      "41071" "41402" "4142"  "42830" "4280"
      "41071" "41401" "4019"  "25000" "73300"
      "41041" "51881" "486"   "42841" "78551"
      "41041" "30000" "4019"  "4240"  "V1582"
      "41071" "41401" "185"   "5718"  " "    
      "41071" "41401" "4019"  "2724"  "30501"
      "41041" "51851" "99739" "2851"  "41401"
      "41031" "2449"  "41401" "3051"  "4019"
      "41071" "2761"  "41401" "4148"  "4019"
      "41071" "41042" "2724"  "43889" "4019"
      "41041" "42789" "V1582" "V1046" "V1301"
      "41011" "51881" "78551" "4275"  "4271"
      "41071" "41401" "4019"  "496"   "30400"
      "41011" "2948"  "5859"  "V1582" "2724"
      "41071" "4271"  "41401" "4019"  "7859"
      "41071" "41401" "4019"  "3051"  "27800"
      "41071" "41405" "25000" "2724"  "V1582"
      "41071" "41401" "2724"  "42769" "2720"
      "41071" "2724"  "41401" "311"   "30000"
      "41011" "412"   "41401" "4019"  "2724"
      "41041" "42613" "2720"  "41401" " "    
      "41041" "42741" "78550" "99801" "99601"
      "41071" "25002" "4254"  "41401" "3051"
      "41041" "41401" "496"   "3051"  "2724"
      "41041" "3051"  "41401" " "     " "    
      "41011" "4240"  "2724"  "41401" "60000"
      "41071" "4370"  "2662"  "2841"  "41401"
      "41071" "99812" "41401" "4019"  "2724"
      "41011" "2859"  "V667"  "3051"  "4275"
      "41031" "40391" "V5867" "V4511" "3572"
      "41071" "42821" "5849"  "5855"  "58881"
      "41041" "7100"  "41401" "4019"  "2724"
      "41041" "2722"  "3051"  "4589"  "49390"
      "41071" "41404" "41401" "V066"  "4019"
      "41021" "4142"  "7840"  "V5867" "27652"
      "41041" "25000" "4271"  "4264"  "99672"
      "41011" "41401" "3051"  "2724"  "4019"
      "41071" "4254"  "V0382" "V4502" "4019"
      "41071" "41401" "25000" "4019"  "V1581"
      "41071" "41401" "25000" "2724"  " "    
      "41071" "42823" "41401" "43310" "3051"
      "41021" "25062" "2720"  "2724"  "27800"
      "41011" "41401" "V5866" "2449"  "7109"
      "41071" "42789" "32723" "3051"  "4264"
      "41041" "2724"  "27800" "41401" "V5866"
      "41071" "2768"  "2851"  "41401" "496"  
      "41071" "42821" "36043" "4280"  "41401"
      "41041" "41401" "2724"  "4019"  "725"  
      "41011" "29620" "41401" "2724"  "4019"
      "41071" "41404" "41401" "4142"  "2724"
      "41071" "2761"  "41401" "4142"  "25002"
      "41071" "40390" "5852"  "30000" "412"  
      "41071" "25002" "4019"  "2724"  "41401"
      "41071" "2724"  "41401" "V4582" "4019"
      "41061" "27800" "41401" "4019"  "3051"
      "41011" "41401" "V1302" " "     " "    
      "41071" "2761"  "3051"  "V4364" "30001"
      "41011" "4142"  "41401" "4019"  "3051"
      "41051" "42821" "4280"  "2724"  "4011"
      "41041" "4019"  "3659"  "41401" "V4582"
      "41071" "34590" "30981" "27800" "31401"
      "41041" "42741" "4275"  "41401" "4019"
      "41041" "V4582" "412"   "4019"  "2720"
      "41071" "4168"  "4280"  "41401" "V4582"
      "41040" "V4582" "4019"  "4142"  "41402"
      end
      
      // GO TO LONG LAYOUT
      gen long obs_no = _n
      reshape long DX, i(obs_no)
      
      // MERGE IN THE REPLACEMENTS
      merge m:1 DX using `to_replace', keep(match master)
      // PUT IN THE NEW CODE WHERE THERE IS A MATCH
      replace DX = "41091" if _merge == 3
      // CLEAN UP AND OPTIONALLY RESTORE WIDE LAYOUT
      drop _merge
      reshape wide


      Comment


      • #4
        And if you have several batches of codes, one batch to be replaced by "41091" and another batch to be replaced by, say, "42053" , and another batch to be replaced by something else, and so on. Perhaps there are a large number of such batches. Then you build on the second approach shown in #3. You start by building a crosswalk file between the old codes and the new. This file now contains two variables DX (which refers to the old codes you want to get rid of) and newDX, which contains the corresponding code you want to replace it with. You create that file, and then run the same code as above with just one change. The -replace- command near the end becomes:
        Code:
        replace DX = newDX if _merge == 3
        Everything else is the same.

        Comment


        • #5
          Thanks a lot. That is much helpfu.

          Comment

          Working...
          X