Identify the most common observations among many variables

[Hadi Kahalzadeh]

Hi everyone,

I am working with a dataset and want to Identify the three most common ICD-10 codes (observation) among diagnosis codes (20 DX codes DX1-DX20). In fact, I am wondering how many times patients are diagnosis for example for "M054", "M055", "M057", "M058", "M060", "M062", "M063", "M068", "M069", "M120" between DX1 to DX20.

Also, I am assigned to have two subgroups: RA covers "M054", "M055", "M057", "M058", "M060", "M062", "M063", "M068", "M069", "M120" and
Other RA includes "M050", "M051", "M052", "M053", "M056", "M061"

I used this code:

foreach v of varlist dx1-dx20 {
    gen S`v' = "RA"  if inlist(substr(`v', 1, 4), "M054", "M055", "M057", "M058", "M060", "M062", "M063", "M068", "M069", "M120")
    replace S`v'= "Other_RA" if inlist(substr(`v', 1, 4), "M050", "M051", "M052", "M053", "M056", "M061")
    encode S`v', gen(DS`v')
    replace DS`v'=0 if DS`v'==.
    }

This command helps me to define "RA" and"Other RA" through DSdx1-DSdx20. But my problem is I don't know how to calculate the most frequent ICD codes in "RA" and "Other RA"

DMdx1    DMdx2    DMdx3    DMdx4    DMdx5    DMdx6   
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
0    M069    0    0    0    0    0    0    0    0    0
M0510    0    0    0    0    0    0    0    0    0    0
M0510    0    0    0    0    0    0    0    0    0    0
M0510    0    0    0    0    0    0    0    0    0    0
M0510    0    0    0    0    0    0    0    0    0    0
M0510    0    0    0    0    0    0    0    0    0    0
M06041    0    0    0    0    0    0    0    0    0    0
M06051    0    0    0    0    0    0    0    0    0    0
M06071    0    0    0    M06072    0    0    0    0    0    0
M06071    0    M064    0    0    0    0    0    0    0    0
M06072    0    0    0    0    0    0    0    0    0    0
M0609    0    0    0    0    0    0    0    0    0    0
M0609    0    0    0    0    0    0    0    0    0    0
M0609    0    0    0    0    0    0    0    0    0    0
M0609    0    0    0    0    0    0    0    0    0    0
M06322    0    0    0    0    0    0    0    0    0    0
M06322    M06341    0    0    0    0    0    0    0    0    0
M06331    0    0    0    0    0    0    0    0    0    0
M06341    0    0    0    0    0    0    0    0    0    0
M06342    0    0    0    0    0    0    0    0    0    0
M06342    0    0    0    0    0    0    0    0    0    0
M064    0    0    0    0    0    0    0    0    0    0
M064    0    0    0    0    0    0    0    0    0    0

[Clyde Schechter]

First, you did not run that code. It is riddled with errors. Probably you ran something more like this:

foreach v of varlist dmdx1-dmdx10 {
    gen S`v' = "RA"  if inlist(substr(`v', 1, 4), "M054", "M055", "M057", "M058", "M060", "M062", "M063") ///
        | inlist(substr(`v', 1, 4), "M068", "M069", "M120")
    replace S`v'= "Other_RA" if inlist(substr(`v', 1, 4), "M050", "M051", "M052", "M053", "M056", "M061")
    encode S`v', gen(DS`v')
    replace DS`v'=0 if DS`v'==.
 }

You can find the "three most frequent" in each RA/OtherRA category with the following code: (Obviously "0" is by far the most frequent code--but I assume you intend not to count that.)

//  IDENTIFY THE THREE MOST COMMON CODES AMONG THE VARIABLES
gen long obs_no = _n
keep obs_no dmdx* S*
reshape long dmdx Sdmdx, i(obs_no)
drop if dmdx == "0"
drop obs_no _j
by Sdmdx dmdx, sort: gen freq = _N
by Sdmdx dmdx: keep if _n == 1
by Sdmdx (freq), sort: keep if _N-_n < 3
list, noobs sepby(Sdmdx)

I used scarequotes around three most frequent because if your data set is large, there is a good probability that there are two or more codes that tie for third most frequent. The code above breaks that tie randomly and irreproducibly.

I also note your data organization is rather strange. I see precisely one non-"0" variable value in each observation. So it seems unclear to me why you would waste 20 variables to hold information that is really just a single variable. Anyway, perhaps that is not the case in your full data set.

As I remarked, I assume that you don't intend to count "0." And I'm guessing that "0" is in fact a code for missing value. If so, for nearly all purposes, you will make your life easier if you actually replace those with Stata's missing value string ("").

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Hadi Kahalzadeh]

Clyde, Many thanks for your kind support.

But when I try the code

 
 gen long obs_no = _n keep obs_no dmdx* S* reshape long dmdx Sdmdx, i(obs_no) drop if dmdx == "0" drop obs_no _j by Sdmdx dmdx, sort: gen freq = _N by Sdmdx dmdx: keep if _n == 1 by Sdmdx (freq), sort: keep if _N-_n < 3 list, noobs sepby(Sdmdx)

I lose all observations. Let's use a simple way. I have 20 DX (dx1 to dx20).

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7(dx1 dx2 dx3 dx4 dx5 dx6 dx7 dx8 dx9 dx10 dx11 dx12 dx13 dx14 dx15 dx16 dx17 dx18 dx19 dx20)
"L600"    "J45909" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "K5730"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I4901"  "Z95810"  " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"M2021"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I2510"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "K621"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K2270"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"R1310"   "K319"   "G4733"   "I10"    "I4891" "J4520" " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "J449"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I4891"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "R000"   "H40059"  "E785"   "F419"  " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2511"   "E785"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "K5730"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "D125"   "I10"     "E119"   " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "I4510"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2511"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "D124"   "D125"    "D128"   "J449"  " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "E119"   "I10"     " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K625"    "K5730"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"J351"    " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "K5730"  "G4733"   "E119"   "I10"   " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "H2189"  "Z961"    " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2511"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H6591"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "D122"   "D125"    "K5730"  " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H25042"  "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H25041"  "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "K635"   "K639"    " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z48815"  "K621"   "K648"    "I10"    " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I2510"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"R1013"   "K2970"  "K449"    "I10"    "E119"  " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H25041"  "E119"   "I10"     "E785"   " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "K635"   "G4733"   "J45909" " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I2510"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "G4733"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H25011"  "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2511"   "Z961"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "H2181"  "T446X5S" " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K219"    "G4730"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "G4733"  "I10"     "E119"   " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "E119"   "I10"     " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"S83232A" " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z8711"   "K449"   "K219"    "F17200" " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"M5417"   "Z91048" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "Z91040" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K5900"   "K635"   "K621"    "K2270"  "K222"  "K5730" "K648" "I10"    "I2510" "K9171" "Y838" " " " " " " " " " " " " " " " " " "
"M1288"   "I2510"  "Z885"    "Z882"   "Z888"  "Z886"  "Z881" "Z91041" " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H04301"  "H04201" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "J45909" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"M5416"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "D123"   "K5730"   "K645"   "I10"   " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"J320"    " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"R197"    "Z8379"  "K5730"   " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"R1310"   "K219"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "D124"   "K5730"   "I10"    "I2510" " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K529"    " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "D124"   "E119"    "I10"    "I2510" " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "I8500"  "K766"    "K3189"  " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K429"    "D176"   "E119"    "I10"    "I4891" " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"C50911"  " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"D242"    "N6082"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "K6289"  "K644"    "E119"   "I341"  " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"D509"    "K2210"  "K449"    " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
end

I want to work only on specific ICD-10 codes. So I generated new 20 variable SDX that only contains such specific ICD-10 codes.

foreach v of varlist dx1-dx20 {
    gen S`v' = `v' if inlist(substr(`v', 1, 4), "M054", "M055", "M057", "M058", "M060", "M062", "M063") | inlist(substr(`v', 1, 4), "M068", "M069", "M120") | inlist(substr(`v', 1, 4),"M050", "M051", "M052", "M053", "M056", "M061")
    encode S`v', gen(DS`v')
    replace DS`v'=0 if DS`v'==.
 }

So I have these variables:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(DSdx1 DSdx2 DSdx3 DSdx4 DSdx5 DSdx6 DSdx7 DSdx8 DSdx9 DSdx10 DSdx11 DSdx12 DSdx13 DSdx14 DSdx15 DSdx16 DSdx17 DSdx18 DSdx19 DSdx20)
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 M0510 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 M05141 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 M069 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 M069 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 M069 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 M0579 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 M069 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 M06072 0 0 0 0 0 0 0 0 0 0 0
M069 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 M0690 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 M069
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 M0690 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0M069 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 M0690 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
end
label values DSdx1 DSdx1
label values DSdx2 DSdx2
label values DSdx3 DSdx3
label values DSdx4 DSdx4
label values DSdx5 DSdx5
label values DSdx6 DSdx6
label values DSdx7 DSdx7
label values DSdx8 DSdx8
label values DSdx9 DSdx9
label values DSdx10 DSdx10
label values DSdx11 DSdx11
label values DSdx12 DSdx12
label values DSdx13 DSdx13
label values DSdx14 DSdx14
label values DSdx15 DSdx15
label values DSdx16 DSdx16
label values DSdx17 DSdx17
label values DSdx18 DSdx18
label values DSdx19 DSdx19
label values DSdx20 DSdx20

Now I want to know what the most common ICD-10 codes are here. I mean between new variables (DSdx1 to DSdx20). When I used the following code, it drops all observations.

My main question is I want to show -for example- the code M069 is reported 100 times as the most common diagnosis or code M0579 is reported 80 times as second. But when we drop "0" the whole row is dropped.

[Clyde Schechter]

I ran that code with your data example, and it does not lose all the data. What is true is that the Sdx* variables are all missing values because the data does not have any dx* codes among the values you list. So your DS variable is always missing value, and you only get the top three codes in the category where DS is missing. So the problem is with your data (or with the list of codes you want to focus on, not the code.

That said, the code can be made much simpler. The wide layout you start with is just a nuisance: like nearly everything in Stata, this task is easier in long layout, and the sooner we go there the better. The following code is simpler and does the job (or would if given suitable data)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7(dx1 dx2 dx3 dx4 dx5 dx6 dx7 dx8 dx9 dx10 dx11 dx12 dx13 dx14 dx15 dx16 dx17 dx18 dx19 dx20)
"L600"    "J45909" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "K5730"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I4901"  "Z95810"  " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"M2021"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I2510"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "K621"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K2270"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"R1310"   "K319"   "G4733"   "I10"    "I4891" "J4520" " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "J449"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I4891"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "R000"   "H40059"  "E785"   "F419"  " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2511"   "E785"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "K5730"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "D125"   "I10"     "E119"   " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "I4510"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2511"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "D124"   "D125"    "D128"   "J449"  " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "E119"   "I10"     " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K625"    "K5730"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"J351"    " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "K5730"  "G4733"   "E119"   "I10"   " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "H2189"  "Z961"    " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2511"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H6591"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "D122"   "D125"    "K5730"  " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H25042"  "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H25041"  "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "K635"   "K639"    " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z48815"  "K621"   "K648"    "I10"    " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I2510"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"R1013"   "K2970"  "K449"    "I10"    "E119"  " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H25041"  "E119"   "I10"     "E785"   " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "K635"   "G4733"   "J45909" " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I2510"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "G4733"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H25011"  "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2511"   "Z961"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "H2181"  "T446X5S" " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K219"    "G4730"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "G4733"  "I10"     "E119"   " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "E119"   "I10"     " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"S83232A" " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z8711"   "K449"   "K219"    "F17200" " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"M5417"   "Z91048" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "Z91040" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K5900"   "K635"   "K621"    "K2270"  "K222"  "K5730" "K648" "I10"    "I2510" "K9171" "Y838" " " " " " " " " " " " " " " " " " "
"M1288"   "I2510"  "Z885"    "Z882"   "Z888"  "Z886"  "Z881" "Z91041" " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H04301"  "H04201" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"H2512"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "J45909" " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"M5416"   "I10"    " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "D123"   "K5730"   "K645"   "I10"   " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"J320"    " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"R197"    "Z8379"  "K5730"   " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"R1310"   "K219"   " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "D124"   "K5730"   "I10"    "I2510" " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K529"    " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "D124"   "E119"    "I10"    "I2510" " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z86010"  "I8500"  "K766"    "K3189"  " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"K429"    "D176"   "E119"    "I10"    "I4891" " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"C50911"  " "      " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"D242"    "N6082"  " "       " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"Z1211"   "K6289"  "K644"    "E119"   "I341"  " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
"D509"    "K2210"  "K449"    " "      " "     " "     " "    " "      " "     " "     " "    " " " " " " " " " " " " " " " " " "
end

gen long obs_no = _n
reshape long dx, i(obs_no)
drop obs_no _j


gen DS = inlist(substr(dx, 1, 4), "M054", "M055", "M057", "M058", "M060", "M062", "M063") ///
    | inlist(substr(dx, 1, 4), "M068", "M069", "M120") ///
    | inlist(substr(dx, 1, 4),"M050", "M051", "M052", "M053", "M056", "M061")
 
 keep DS dx
 drop if missing(dx) | dx == " "

 //  IDENTIFY THE THREE MOST COMMON CODES AMONG THE VARIABLES
by DS dx, sort: gen freq = _N
by DS dx: keep if _n == 1
by DS (freq), sort: keep if _N-_n < 3
list, noobs sepby(DS)

By the way, when I suggested changing "0" to missing value string, it was perhaps not clear, but missing value string is "", with nothing between the quotes, not a blank space. The advantage of using that is that the -missing()- function recognizes it as such, so you don't have to spell out your idiosyncratic coding of missing value in the string variable.

[Hadi Kahalzadeh]

Perfect! thank you so much for your time

[Hadi Kahalzadeh]

As the last question, how we can test the gender difference relationships between these codes. Based on your codes, I found the most ten codes

 +-----------------------------+
  |     dx        Sdx      freq |
  |-----------------------------|
  |   E119                14061 |
  | J45909                14391 |
  |  Z1211                14772 |
  | F17210                15880 |
  |   K219                20558 |
  |  Z7982                21092 |
  | Z87891                26340 |
  |    I10                37658 |
  | Z79899                48828 |
  |                     6285744 |
  |-----------------------------|
  | M05141   Other_RA         1 |
  |  M0500   Other_RA         1 |
  | M05672   Other_RA         1 |
  |   M061   Other_RA         1 |
  | M05142   Other_RA         1 |
  |  M0560   Other_RA         1 |
  |  M0519   Other_RA         2 |
  | M05671   Other_RA         2 |
  |  M0510   Other_RA        20 |
  |-----------------------------|
  | M06851         RA         2 |
  | M06342         RA         2 |
  | M06341         RA         2 |
  |  M0570         RA         2 |
  | M06072         RA         3 |
  |  M0609         RA         4 |
  | M06871         RA         4 |
  |  M0600         RA         6 |
  |  M0579         RA        17 |
  |   M069         RA       863 |
  +-----------------------------+

But when we use reshape, how we can cross tab between gender and RA / Other_RA.

Thank you again for your time

[Clyde Schechter]

I don't understand any of these questions. Please provide suitable starting example data and a then show what the results you want to get would look like.

[Hadi Kahalzadeh]

I am sorry for the confusion. I want to know Gender differences between RA and Other_RA that appear here as 1 and 2.

I used your code to generate RA and Other_RA. but I don't know how to test gender differences. Because I have 20 variables with many observations (RA and Other_RA)

foreach v of varlist dx1-dx20 {
    gen S`v' = "RA"  if inlist(substr(`v', 1, 4), "M054", "M055", "M057", "M058", "M060", "M062", "M063") ///
        | inlist(substr(`v', 1, 4), "M068", "M069", "M120")
    replace S`v'= "Other_RA" if inlist(substr(`v', 1, 4), "M050", "M051", "M052", "M053", "M056", "M061")
    encode S`v', gen(DS`v')
    replace DS`v'=0 if DS`v'==.
 }

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(DSdx1 DSdx2 DSdx3 DSdx4 DSdx5 DSdx6 DSdx7 DSdx8 DSdx9 DSdx10 DSdx11 DSdx12 DSdx13 DSdx14 DSdx15 DSdx16 DSdx17 DSdx18 DSdx19 DSdx20) str6 gender
0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 "Male"  
0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 "Female"
0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 "Female"
0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 "Male"  
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 "Female"
0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Male"  
1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 "Female"
end
label values DSdx1 DSdx1
label def DSdx1 1 "Other_RA", modify
label def DSdx1 2 "RA", modify
label values DSdx2 DSdx2
label def DSdx2 2 "RA", modify
label values DSdx3 DSdx3
label values DSdx4 DSdx4
label values DSdx5 DSdx5
label def DSdx5 2 "RA", modify
label values DSdx6 DSdx6
label values DSdx7 DSdx7
label values DSdx8 DSdx8
label values DSdx9 DSdx9
label values DSdx10 DSdx10
label values DSdx11 DSdx11
label values DSdx12 DSdx12
label values DSdx13 DSdx13
label values DSdx14 DSdx14
label values DSdx15 DSdx15
label values DSdx16 DSdx16
label values DSdx17 DSdx17
label values DSdx18 DSdx18
label values DSdx19 DSdx19
label values DSdx20 DSdx20

I used this code, but I don't know if it gives the relationship between geder and being deognised as RA or Other_RA

regress DSdx1 DSdx2 DSdx3 DSdx4 DSdx5 DSdx6 DSdx7 DSdx8 DSdx9 DSdx10 DSdx11 DSdx12 DSdx13 DSdx14 DSdx15 DSdx16 DSdx17 DSdx18 DSdx19 DSdx20 i.gender

[Clyde Schechter]

Your code will not do what you want. Nor do I see any way to do it. The question as posed doesn't make sense.

I take it that each observation in your data is a single person, with multiple diagnosis codes. But you have some people who have both RA and OtherRA codes. In particular, look at rows 36, 37, and 66 in your example data, So you cannot classify such people as being either RA or OtherRA, they are both. To do a cross-classification and association, you need mutually exclusive categories on both variables (DSdx and gender).