How to get resistance status in single variable?

[Rayhan Islam]

Dear Stata altruistic,
These forum always encourage me to use Stata more and more. Thanks for your continuous support.

I'm working on antimicrobial resistance so I have individual antibiotic resistance status (1=Sensitive, 2= Intermediate, 3= Resistance) for different antibiotics (S_CN_10 S_MEM_10 S_ETP_10 S_IPM_10 S_FOX_30 S_F_300 S_TZP_110) like following table

* Example generated by -dataex-. To install: ssc install dataex
clear
input str11(PId UniqueId) double(S_CN_10 S_MEM_10 S_ETP_10 S_IPM_10 S_FOX_30 S_F_300 S_TZP_110) float(SampleTypeCat2 ASStatus)
"TR001" "111011TR001" . . . . . . . 1 .
"TR002" "111011TR002" . . . . . . . 1 .
"TR003" "111011TR003" 1 1 1 1 1 1 1 1 .
"TR004" "111011TR004" . . . . . . . 1 .
"TR005" "111011TR005" 1 1 1 1 1 1 1 1 .
"TR006" "111011TR006" 1 1 1 1 1 1 2 1 .
"TR007" "111011TR007" . . . . . . . 1 .
"TR008" "111011TR008" . . . . . . . 1 .
"TR009" "111011TR009" 1 1 1 1 1 1 1 1 .
"TR010" "111011TR010" 1 1 1 1 1 1 1 1 .
end
label values S_CN_10 S_CN_10
label def S_CN_10 1 ">=15", modify
label values S_MEM_10 S_MEM_10
label def S_MEM_10 1 ">=23", modify
label values S_ETP_10 S_ETP_10
label def S_ETP_10 1 ">=22", modify
label values S_IPM_10 S_IPM_10
label def S_IPM_10 1 ">=23", modify
label values S_FOX_30 S_FOX_30
label def S_FOX_30 1 ">=18", modify
label values S_F_300 S_F_300
label def S_F_300 1 ">=17", modify
label values S_TZP_110 S_TZP_110
label def S_TZP_110 1 ">=21", modify
label def S_TZP_110 2 "18-20", modify
label values SampleTypeCat2 SampleTypeCat2l
label def SampleTypeCat2l 1 "Human", modify

Now want to generate a variable ASStatus to put all resistance status in the single variable so my code look like

gen     ASStatus=.
replace ASStatus=1 if S_CN_10==3
replace ASStatus=2 if S_MEM_10==3 | S_ETP_10==3 | S_IPM_10==3
replace ASStatus=3 if S_FOX_30==3

[/CODE]

Problem is number of observations changed if any individual (UniqueID) fall in both group (ASStatus=1 & ASStatus=2) but I need something which calculate separately in each value label in the same variable (resistance status).

Helpful suggestion is appreciated.

[Clyde Schechter]

I don't understand what you want here. Your three conditions defining ASStatus are not mutually exclusive, so for an observation which meets more than one of the criteria, it will end up with the highest value of ASStatus for which it qualifies (because of the order of the commands). I can imagine that is not what you want, but you have not explained what you do want in any way I can follow. In particular, I do not understand "I need something which calculate separately in each value label in the same variable." Perhaps you could illustrate what you want with a few examples.

Also, when you post back, please post a data example that could be used to test out some code in. What I do understand from #1 is that the criteria you want all involve some variable(s) having the value 3. But the value 3 never occurs anywhere in your data example, so it will never generate any positive results. Please be sure your data example in your repost includes some 3's.

[Rayhan Islam]

My apologies for previous post . I think above picture may clarify your query.
d,e,f are three raw variables and g is my expected variable. a,b,c are the label of the common variable.
d,e,f are not mutually exclusive.
how can I generate expected table.

[Clyde Schechter]

I do not understand any of the tables in #3 or how they relate to your Stata data set.

Please post an example using Stata data sets, displayed with the -dataex- command. Without seeing the actual data layout you are working with, it isn't possible to write code. If you are running version 15.1 or a fully updated version 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.

When asking for help with code, always show example data. When showing example data, always use -dataex-.