Merging many datasets - gen newvar if variable was numeric in one set and string in the other
Hello! This probably isn't the most efficient way to merge multiple databases (suggestions are welcome), however my dilemma is Stata treating my "nonmissing conflict _merge==5" values as missing when I use the replace and force options when merging.
Context:
I'm working with health system data that changes over time, in both added and dropped patients, as well as status changes over time (i.e. "most recent pain assessment rating"). The example code below runs through roughly 25 datasets, each with 3000 observations and 30 variables. For variables like the pain rating above, it is sometimes coded as a "3" on a Likert-type scale or as "Moderate" pain; therefore some databases are numeric/double while others are string/str3. I'm looking for a way to generate a new var in the middle of the merge process, something like "gen newvar = oldvar if _merge==5" that way the values of the oldvar are not overwritten/turned into missing data and the newvar values are retained in their own varlist.
This post seems similar to what I'm asking, but was not quite resolved:
https://www.statalist.org/forums/for...udinal-dataset
Thanks!
Context:
I'm working with health system data that changes over time, in both added and dropped patients, as well as status changes over time (i.e. "most recent pain assessment rating"). The example code below runs through roughly 25 datasets, each with 3000 observations and 30 variables. For variables like the pain rating above, it is sometimes coded as a "3" on a Likert-type scale or as "Moderate" pain; therefore some databases are numeric/double while others are string/str3. I'm looking for a way to generate a new var in the middle of the merge process, something like "gen newvar = oldvar if _merge==5" that way the values of the oldvar are not overwritten/turned into missing data and the newvar values are retained in their own varlist.
Code:
use "DatabaseA.dta" merge 1:1 studyId using "DatabaseB.dta", replace update force drop _merge save "DatabaseAB.dta" clear use "DatabaseAB.dta" merge 1:1 studyId using "DatabaseC.dta", replace update force drop _merge save "DatabaseABC.dta" clear use "DatabaseABC.dta" merge 1:1 studyId using "DatabaseD.dta", replace update force drop _merge save "DatabaseABCD.dta" clear
https://www.statalist.org/forums/for...udinal-dataset
Thanks!
Comment