Replacing 2 variables at once?

[Nate Fitzpatrick]

I am working through some data cleaning and I was wondering if there is some obvious more efficient way to replace 2 variables at once.

id	var_level1	var_level2	var_level3	var_total
1	1	0	0	1
2	0	1	1	2
3	0	1	0	1

Very basic example, but in this case I only want one observation across the 3 levels of this variable, being the highest level (3) for each id. In this case I want to replace the var_level2 for id==2 to 0, and then replace the var_total for id==2 to 1 to continue exploring the data.

The way I have been going about it would be (once I already know which one I need to remove):

tab id if var_level2==1 & var_total==2
replace var_level2 = 0 if var_level2==1 & var_total==2

and then I would like to immediately change that var_total to 1 for id==2.

The two ways I can think of would be creating a temporary dummy variable to identify those subjects, change the 2 variables, and then drop the dummy variable, or just drop var_total and re-run the code to generate var_total again now that the values have changed. But both these options seem inefficient to me.

I hope this makes sense, I know that both these options would work, but ultimately I am trying to get better at Stata so I wanted to ask if there is a more efficient way to do this!

Thank you.

[Nick Cox]

I don't understand your set-up or your questions.

What do levels 1, 2 and 3 mean? What implies what is to be changed? And what are the rules implying that your data are wrong?

A short answer is that you can't change two variables at once. You need to change them one at a time.

I can't follow either why the alternative code you sketch introducing and then discarding a further variable could possibly be better than what you are doing.

Here is your sample data presented using dataex. Please see FAQ Advice #12.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id var_level1 var_level2 var_level3 var_total)
1 1 0 0 1
2 0 1 1 2
3 0 1 0 1
end

[Daniel Schaefer]

If I understand correctly, you want to make some changes to the values in the level 2 column, then update the total column to reflect those changes, right? As an example, you've got this arbitrary change you'd like to make to the second observation, and you're wondering if you can update the observation and change the total simultaneously because this would be "more efficient". So you haven't actually given us a real problem, just an abstract toy problem (one that is maybe not clearly specified). You propose a couple of ways to update both columns, though I somewhat prefer the latter. Just recalculate the row totals once your done modifying var_level2. Using Nick's data example, something along these lines:

clear
input byte(id var_level1 var_level2 var_level3 var_total)
1 1 0 0 1
2 0 1 1 2
3 0 1 0 1
end

replace var_level2 = 0 if var_level2==1 & var_total==2
drop var_total
egen var_total = rowtotal(var_level1 var_level2 var_level3)

I'm not sure that syntax is absolutely correct, but something like that. Now you wonder if there is something more "efficient". By "efficient" I think you mean idiomatic, i.e., the way a "native speaker" of Stata might write this, rather than "efficient" in computation time and memory the way we would formally define efficiency in programming. Ideally, you want a nice single line of code that updates the total column as you update the var_level2 column.

The somewhat-longer-than-Nick's answer is that Stata can't tell a total from any other ordinary column, so you have to explicitly define how to update both the var_level2 column and the total column, so you need at least one line per column. There are a few commands that will modify columns in batches, but they work because you are doing the exact same procedure on each column. You could write your own custom command to do this specific update procedure, but I'm not sure how much utility the command would have outside of this narrow scope.

[Leonardo Guizzetti]

I'll add a different thought from the other helpful advice here. One take on what might be "efficient" is to first perform your data cleaning steps, then define your total variable (and anything else derived from those variables). This minimizes the risk of updating one variable but not the other, and cleanly separates independent tasks (cleaning vs derivation).