Use foreach with a Local Macro for similar suffixes on multiple variables

[Yvonne Renard]

Hello!

I am trying to using a local macro to convert units in a dataset that has a type of mass and a unit of measurement, which is set up like this:

totq_water	totq_soil	totq_oil	totq_gas	unit_water	unit_soil	unit_oil	unit_gas
434	6546	67456	6543	kg	lb	kg	lb
3464	3464	456434	45345	lb	kg	oz	kg
2345	436	44332	5436	kg	lb	oz	oz

The actual dataset I'm using has 26 different mass types with 7 different unit types. I'm trying to convert everything to kilograms, and figured using a macro containing the suffixes would be easier than writing the logic for each conversation out repeatedly since the suffixes are the same for the quantity and unit variables. Ultimately I'll be able to say in the label that the quantities of mass are all in kilograms, but I've gotten an error on every troubleshooting attempt.

The code I'm using is:
local wt "_water _soil _oil _gas"

foreach x of local wt {
replace totq`wt' = totq`wt' * 0.453592
if unit`wt' == "lb"
replace totq`wt'= totq`wt' * 0.0283495
if unit`wt' == "oz"
}

I end up getting an error that says "variable _soil not found"

When I write the logic in long-hand, it looks like this and runs fine:
replace totq_water = totq_water * 0.453592 if unit_water=="lb"
replace totq_water = totq_water * 0.0.0283495 if unit_water=="oz"

Is it possible to use a macro in this case? I'm using Stata 17.

Thank you in advance!

[George Ford]

foreach var in water soil oil gas {
     replace totq_`var' = totq_`var'*0.453592 if unit_`var'=="lb"
     replace totq_`var' = totq_`var'*0.0283495 if unit_`var'=="oz"
}

[Yvonne Renard]

Thank you so much! This worked perfectly.