creating a new variable which merges the conents from other variables

[Rose Matthews]

* Example generated by -dataex-. For more info, type help dataex
clear
input float(id mob_1 mob_2 mob_3 mobnew)
1 1 0 0 .
2 1 0 0 .
3 0 1 0 .
4 0 1 0 .
end

HI, I hope I’ll make my self clear




I have the following variables

Id - id of the patient

mob_1, mob_2, mob_3




These are mobility variables. Where by the numbers represent the value of the points within mobility.

Therefore if mob_1= 1 then mobility=1

If mob_2 = 1 then mobility=2

If mob_3 = 1 then mobility = 3




I would like to create a new variable mobility which will join all 3 mob* together




Which means that if for eg

ID=1 and mob_1 = 1 then mobility = 1

ID=2 and mob_2=1 then mobility=2







I have checked the data are for each ID only one of the mob_* = 1

So there will not be a situation whereby




mob_1 = 1 and mob_2 = 2




I would appreciate your advice

[Andrew Musau]

Here is one way:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(id mob_1 mob_2 mob_3 mobnew)
1 1 0 0 .
2 1 0 0 .
3 0 1 0 .
4 0 1 0 .
end

gen wanted=.
foreach var of varlist mob_*{
    replace wanted= real(ustrregexra("`var'", ".*_(\d+)$", "$1")) if `var'
}

Res.:

. l

     +----------------------------------------------+
     | id   mob_1   mob_2   mob_3   mobnew   wanted |
     |----------------------------------------------|
  1. |  1       1       0       0        .        1 |
  2. |  2       1       0       0        .        1 |
  3. |  3       0       1       0        .        2 |
  4. |  4       0       1       0        .        2 |
     +----------------------------------------------+

[Nick Cox]

Here are two more ways (no loop). For each way, the commented out version is slightly longer, but anyone is welcome to prefer more explicit code.

It seems that 0 0 0 never occurs, which doesn't undermine the code.

Nothing was said about missings, which would oblige more fastidious code.

clear
input float(id mob_1 mob_2 mob_3 mobnew)
1 1 0 0 .
2 1 0 0 .
3 0 1 0 .
4 0 1 0 .
end

* gen mobnew1 = cond(mob_3 == 1, 3, cond(mob_2 == 1, 2, mob_1))
gen mobnew1 = cond(mob_3, 3, cond(mob_2, 2, mob_1))

* gen mobnew2 = max(3 * mob_3, 2 * mob_2, 1 * mob_1)
gen mobnew2 = max(3 * mob_3, 2 * mob_2, mob_1)

list

     +---------------------------------------------------------+
     | id   mob_1   mob_2   mob_3   mobnew   mobnew1   mobnew2 |
     |---------------------------------------------------------|
  1. |  1       1       0       0        .         1         1 |
  2. |  2       1       0       0        .         1         1 |
  3. |  3       0       1       0        .         2         2 |
  4. |  4       0       1       0        .         2         2 |
     +---------------------------------------------------------+

The first way can be made more verbal. cond() corresponds to something like ifelse() in many other languages.

If mob_3 is 1 then score 3
else if mob_2 is 2 then score 2
else score mob_1 (either 1 or 0, depending)

The second way: consider the maximum over three expressions: 3 * mob_3, 2 * mob_2, 1 * mob_1

If mob_3 is 1, then the first term is 3 and necessarily the maximum over those three -- and you can fill in the rest yourself.

I considered also concatenating the values as strings like 111 to 000 and finding the position of the rightmost 1, which could be done without a loop, but seems more contrived.

Personal testimony: I was a bit leery of cond() until circumstances led to my being second author of a tutorial. Becoming second author helped me think harder about the function and how it worked and could be used. I imagine every teacher has gone through learning about stuff as a student and becoming more used to it (anything even vaguely mathematical or logical is something you find yourself returning to often) but every teacher often understands something better only when you have to teach it!

SJ-5-3 pr0016 . . Depending on conditions: a tutorial on the cond() function
. . . . . . . . . . . . . . . . . . . . . . . D. Kantor and N. J. Cox
Q3/05 SJ 5(3):413--420 (no commands)
tutorial on the cond() function

https://www.stata-journal.com/articl...article=pr0016

[Nick Cox]

Rose Matthews Please remember in due course -- modulo being awake, at work, and so forth -- to close or continue threads you start, with whatever makes sense: that helped, I did something else, the question is really different from what I said or what you guessed, etc.

Variations on "Thanks in advance", explicit or supposedly implicit, are less than we ask for here, as the FAQ Advice explains.