gen third var with val of two other vars

[Marius Kaltenbach]

Good Morning everybody,

I would like to merge two variables in a certain way.
The situation is, that I have two variables, one has "text" and -97 for missin values, the other one is a dummy (0/1) for text or no text and -97 for missing valies.
Now I would like to generate a third variable which takes only the missing values from my dummy variables and fills the rest with the "text" from the string variable.

Example:
Var1 Var2 Var3
"Text" 1 "Text"
-97 -97 -97
"Text" -97 -97
"Text" 1 Text

I tried

egen newvar = concat( var1 var2), decode p(" ")

which gives me both values from both variables in the new variable. And I tried also

gen VAR3 = VAR1
replace VAR3 = -97 if VAR2 == -97

for which stata gave me a "type missmatch". Both Variables are coded as type string and I use Stata 15.1

I would be happy about every advice

Best Regards

[Joseph Coveney]

Maybe

generate str var3 = cond(var2 == "-97", var2, var1)

I assume that var1 is blank (missing-valued) if var2 is "0". Otherwise, you'll need to tell us what you want to do if var2 is "0".

[Marius Kaltenbach]

Good question Joseph, the dataset is new to me and unfornutaley I do not have the codebook. However, even if Var2 is "0", it should be replaced by the "text" of var1.
(i guess, that the "0" means something like wrong text, but that is just speculation)

I tried also
the code you provided, but stata gives me constantly a type mismach r(109)

Best Regards

[Nick Cox]

If you're getting a type mismatch, then the implication is that your variables are really numeric with value labels -- despite what you say.

Discussion would be easier and go faster if you gave a data example, as we request (FAQ Advice #12). Then we wouldn't have to guess.

If that's right, then I would decode these variables and work with the string equivalents.

[Marius Kaltenbach]

Ah, you are right Nick. I just decoded the variables and used Joseph Code with success. Thank You!

 generate str var3 = cond(var2 == "-97", var2, var1)

Is it possible to use this code in a loop?
I have like 30 variable pairs like in the example (var1 and Var2). Each pair should generate a third variable as shown in the code.

You guys would safe me hours of trial and error if you helped me out here.

Best Regards

[Nick Cox]

Yes, a loop is possible to speed this up for other pairs of variables. But we can have no idea what it should be unless you tell us what variable names you have and what you want.

Even if you wrote 30 extra lines it should not take you hours!

[Marius Kaltenbach]

fair enough, I was exaggerating a bit. Anyways, I found a loop which produces the wanted results.

local ALL_VARS_2 "[list of all variables which are like var1 in the example]"

foreach varname of varlist `ALL_VARS_2' {
    gen str `varname'Xmerg = cond(`varname' == "-97", `varname', `varname _2')
}

Thanks for your help, I know my description was not really precise.

Best Regards
marius

[Nick Cox]

I doubt that code worked properly as `varname _2' is unlikely to be evaluated as you wish. Experiment shows that _2 given like that will just be ignored.

. local varname whatever

. di "`varname'"
whatever

. di "`varname _2'"
whatever

[Marius Kaltenbach]

You are of course right with your example. However, in my case half oy my variables are called XXX and the other half XXX_2.
That might be the reason that it worked?!
I just checked two observations for now which seemed ok. Still have to find a method to evaluate whether or not all the new generated variables have the correct values.

Best Regards

[Nick Cox]

I can't explain more about what worked and why as I can't see a complete, reproducible example of your data and code. You need to do much, much more than check two observations. Note that

foreach varname of local ALL_VARS_2 {    
    gen str `varname'Xmerg = cond(`varname' == "-97", `varname', `varname')
}

-- which is in effect what you typed -- looks like legal syntax so long as the local macro contains variable names. But it copies each variable into a clone, regardless. That is not what you are asking for.