Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Trying to find the most common ICD-9 code among 30 different variable columns

    Hello all,

    I am stuck on the following problem.

    I have a group of 70 patients (variable = id) whom each have 30 corresponding ICD-9 codes (variable = dx1, dx2, dx3...dx30). I am trying to identify the top 10 ICD-9 codes among this group of patients, across all of the dx columns. I have tried going through and independently assessing each diagnosis group, and then summing the common ICD-9 codes in Excel. This seems to be quite labor intensive though, and I am sure there must be a better way to do this in STATA.

    I have included a sample set of the data below, but truncated the dx code columns to just 3 instead of 30.

    Any help you could provide would be much appreciated.

    Kind regards,

    Joe

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input float id str5(dx1 dx2 dx3)
 1 "5712"  "56729" "78959"
 2 "5902"  "0389"  "99811"
 3 "01400" "0389"  "04104"
 4 "20500" "51881" "78061"
 5 "0389"  "5849"  "2875"
 6 "1830"  "56089" "19889"
 7 "01486" "5679"  "78959"
 8 "56081" "56731" "01500"
 9 "01485" "56723" "78959"
10 "01665" "01405" "78959"
11 "56089" "262"   "56789"
12 "01404" "486"   "01194"
13 "42823" "486"   "261" 
14 "78903" "01500" "73088"
15 "570"   "5849"  "07044"
16 "01404" "78959" "5119"
17 "01325" "0389"  "56722"
18 "01485" "0549"  "V6441"
19 "53783" "5849"  "01193"
20 "042"   "01894" "262" 
21 "01890" "5720"  "2639"
22 "01890" "2851"  "78959"
23 "99668" "4589"  "4148"
24 "78904" "01400" "7994"
25 "5601"  "5723"  "01190"
26 "01190" "56722" "01480"
27 "5571"  "56729" "51884"
28 "2651"  "01400" "51189"
29 "01406" "78959" "9982"
30 "0389"  "56983" "01890"
31 "56983" "0389"  "56729"
32 "01480" "78959" "9982"
33 "56081" "5679"  "01890"
34 "01403" "99591" "0389"
35 "9920"  "0389"  "5849"
36 "57512" "01190" "78959"
37 "56889" "56723" "5849"
38 "56983" "01896" "56789"
39 "23691" "78552" "99668"
40 "5750"  "5990"  "99859"
41 "01402" "5119"  "78959"
42 "042"   "5679"  "51881"
43 "0389"  "56722" "78552"
44 "01485" "01205" "2639"
45 "042"   "56789" "01190"
46 "7100"  "5845"  "51851"
47 "01406" "78959" "2631"
48 "01403" "5180"  "5119"
49 "01400" "V427"  "78959"
50 "78959" "56729" "01400"
51 "01484" "3484"  "78060"
52 "01892" "56789" "5849"
53 "43491" "5849"  "7580"
54 "5550"  "56089" "7862"
55 "01400" "4019"  "2449"
56 "042"   "0312"  "683" 
57 "01895" "34400" "2761"
58 "0389"  "042"   "4019"
59 "1550"  "042"   "486" 
60 "01480" "07044" "51881"
61 "5718"  "01400" "5845"
62 "01145" "99592" "78552"
63 "5609"  "51851" "55220"
64 "73313" "56731" "28419"
65 "56211" "56722" "042" 
66 "56889" "01400" "78959"
67 "5722"  "01404" "99592"
68 "01404" "51881" "0389"
69 "25041" "5856"  "99656"
70 "01890" "56781" "57512"
end
    Tags: None
  • #2
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input float id str5(dx1 dx2 dx3)
 1 "5712"  "56729" "78959"
 2 "5902"  "0389"  "99811"
 3 "01400" "0389"  "04104"
 4 "20500" "51881" "78061"
 5 "0389"  "5849"  "2875"
 6 "1830"  "56089" "19889"
 7 "01486" "5679"  "78959"
 8 "56081" "56731" "01500"
 9 "01485" "56723" "78959"
10 "01665" "01405" "78959"
11 "56089" "262"   "56789"
12 "01404" "486"   "01194"
13 "42823" "486"   "261" 
14 "78903" "01500" "73088"
15 "570"   "5849"  "07044"
16 "01404" "78959" "5119"
17 "01325" "0389"  "56722"
18 "01485" "0549"  "V6441"
19 "53783" "5849"  "01193"
20 "042"   "01894" "262" 
21 "01890" "5720"  "2639"
22 "01890" "2851"  "78959"
23 "99668" "4589"  "4148"
24 "78904" "01400" "7994"
25 "5601"  "5723"  "01190"
26 "01190" "56722" "01480"
27 "5571"  "56729" "51884"
28 "2651"  "01400" "51189"
29 "01406" "78959" "9982"
30 "0389"  "56983" "01890"
31 "56983" "0389"  "56729"
32 "01480" "78959" "9982"
33 "56081" "5679"  "01890"
34 "01403" "99591" "0389"
35 "9920"  "0389"  "5849"
36 "57512" "01190" "78959"
37 "56889" "56723" "5849"
38 "56983" "01896" "56789"
39 "23691" "78552" "99668"
40 "5750"  "5990"  "99859"
41 "01402" "5119"  "78959"
42 "042"   "5679"  "51881"
43 "0389"  "56722" "78552"
44 "01485" "01205" "2639"
45 "042"   "56789" "01190"
46 "7100"  "5845"  "51851"
47 "01406" "78959" "2631"
48 "01403" "5180"  "5119"
49 "01400" "V427"  "78959"
50 "78959" "56729" "01400"
51 "01484" "3484"  "78060"
52 "01892" "56789" "5849"
53 "43491" "5849"  "7580"
54 "5550"  "56089" "7862"
55 "01400" "4019"  "2449"
56 "042"   "0312"  "683" 
57 "01895" "34400" "2761"
58 "0389"  "042"   "4019"
59 "1550"  "042"   "486" 
60 "01480" "07044" "51881"
61 "5718"  "01400" "5845"
62 "01145" "99592" "78552"
63 "5609"  "51851" "55220"
64 "73313" "56731" "28419"
65 "56211" "56722" "042" 
66 "56889" "01400" "78959"
67 "5722"  "01404" "99592"
68 "01404" "51881" "0389"
69 "25041" "5856"  "99656"
70 "01890" "56781" "57512"
end

preserve
reshape long dx, i(id) j(_j)
contract dx
gsort -_freq
list in 1/10, clean
restore
    As is so often the case, the key is to put the data into long layout. Then it becomes exquisitely simple.

    Note: If there is a tie for 10th place, this tie will be broken arbitrarily, at random and irreproducibly. If you have a rule that you want to apply for this instead, the code can be modified.
    • 1 like

    Comment

    • #3
      Thank you very much!!! That is exactly what I needed. Out of curiosity, what is happening when the data gets put into long layout? Why does that make it so easy?
      • 1 like

      Comment

      • #4
        When you put the data into long layout, you now have three observations per id, one for each dx, and a new variable, _j, that tells you which of the three dx variables that observation arose from. Crucially, all of the dx codes are now in a single variable. -contract- then reduces the data set to one observation per dx code, accompanied by its count in the variable _freq. Now we just sort on _freq and read out the winners.

        Comment

        • #5
          With tabm from tab_chi (SSC) you can do this. This really isn't a different answer from that of Clyde Schechter, as tabm depends on a temporary reshaping of the data.

          Code:
          . tabm dx?, rowsort transpose

           |             variable
    values |       dx1        dx2        dx3 |     Total
-----------+---------------------------------+----------
     78959 |         1          4          9 |        14
      0389 |         4          5          2 |        11
     01400 |         3          4          1 |         8
       042 |         4          2          1 |         7
      5849 |         0          4          3 |         7
     01890 |         3          0          2 |         5
     01190 |         1          1          2 |         4
     01404 |         3          1          0 |         4
     51881 |         0          2          2 |         4
     56722 |         0          3          1 |         4
     56729 |         0          3          1 |         4
     56789 |         0          2          2 |         4
     01480 |         2          0          1 |         3
     01485 |         3          0          0 |         3
       486 |         0          2          1 |         3
      5119 |         0          1          2 |         3
     56089 |         1          2          0 |         3
      5679 |         0          3          0 |         3
     56983 |         2          1          0 |         3
     78552 |         0          1          2 |         3
     01403 |         2          0          0 |         2
     01406 |         2          0          0 |         2
     01500 |         0          1          1 |         2
     07044 |         0          1          1 |         2
       262 |         0          1          1 |         2
      2639 |         0          0          2 |         2
      4019 |         0          1          1 |         2
     51851 |         0          1          1 |         2
     56081 |         2          0          0 |         2
     56723 |         0          2          0 |         2
     56731 |         0          2          0 |         2
     56889 |         2          0          0 |         2
     57512 |         1          0          1 |         2
      5845 |         0          1          1 |         2
     99592 |         0          1          1 |         2
     99668 |         1          0          1 |         2
      9982 |         0          0          2 |         2
     01145 |         1          0          0 |         1
     01193 |         0          0          1 |         1
     01194 |         0          0          1 |         1
     01205 |         0          1          0 |         1
     01325 |         1          0          0 |         1
     01402 |         1          0          0 |         1
     01405 |         0          1          0 |         1
     01484 |         1          0          0 |         1
     01486 |         1          0          0 |         1
     01665 |         1          0          0 |         1
     01892 |         1          0          0 |         1
     01894 |         0          1          0 |         1
     01895 |         1          0          0 |         1
     01896 |         0          1          0 |         1
      0312 |         0          1          0 |         1
     04104 |         0          0          1 |         1
      0549 |         0          1          0 |         1
      1550 |         1          0          0 |         1
      1830 |         1          0          0 |         1
     19889 |         0          0          1 |         1
     20500 |         1          0          0 |         1
     23691 |         1          0          0 |         1
      2449 |         0          0          1 |         1
     25041 |         1          0          0 |         1
       261 |         0          0          1 |         1
      2631 |         0          0          1 |         1
      2651 |         1          0          0 |         1
      2761 |         0          0          1 |         1
     28419 |         0          0          1 |         1
      2851 |         0          1          0 |         1
      2875 |         0          0          1 |         1
     34400 |         0          1          0 |         1
      3484 |         0          1          0 |         1
      4148 |         0          0          1 |         1
     42823 |         1          0          0 |         1
     43491 |         1          0          0 |         1
      4589 |         0          1          0 |         1
     51189 |         0          0          1 |         1
      5180 |         0          1          0 |         1
     51884 |         0          0          1 |         1
     53783 |         1          0          0 |         1
     55220 |         0          0          1 |         1
      5550 |         1          0          0 |         1
      5571 |         1          0          0 |         1
      5601 |         1          0          0 |         1
      5609 |         1          0          0 |         1
     56211 |         1          0          0 |         1
     56781 |         0          1          0 |         1
       570 |         1          0          0 |         1
      5712 |         1          0          0 |         1
      5718 |         1          0          0 |         1
      5720 |         0          1          0 |         1
      5722 |         1          0          0 |         1
      5723 |         0          1          0 |         1
      5750 |         1          0          0 |         1
      5856 |         0          1          0 |         1
      5902 |         1          0          0 |         1
      5990 |         0          1          0 |         1
       683 |         0          0          1 |         1
      7100 |         1          0          0 |         1
     73088 |         0          0          1 |         1
     73313 |         1          0          0 |         1
      7580 |         0          0          1 |         1
     78060 |         0          0          1 |         1
     78061 |         0          0          1 |         1
      7862 |         0          0          1 |         1
     78903 |         1          0          0 |         1
     78904 |         1          0          0 |         1
      7994 |         0          0          1 |         1
      9920 |         1          0          0 |         1
     99591 |         0          1          0 |         1
     99656 |         0          0          1 |         1
     99811 |         0          0          1 |         1
     99859 |         0          0          1 |         1
      V427 |         0          1          0 |         1
     V6441 |         0          0          1 |         1
-----------+---------------------------------+----------
     Total |        70         70         70 |       210
          • 1 like

          Comment

          Previous Next
          Working...
          X