Assistance Required for Merging Private and Government School Data in Stata

[Shahla Akram]

I am working with a dataset containing both private and government school data, where the variables in each dataset have identical names. I am attempting to merge the two datasets, but the identical variable names are causing issues during the merging process. To resolve this, I would like to add a prefix (e.g., 'pvt') to all the variables in the private school dataset prior to merging. Although I have consulted the Stata guide (https://www.stata.com/manuals/drenamegroup.pdf), I have been unable to achieve the desired outcome. I would appreciate any suggestions or guidance you may have.

Gove School

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 VlgCode int SID byte(STYPE S006 S00) str16 S00a
"10" 4422 2 . 3 "1 to 10"
"19" 4423 2 . 4 ""      
"20" 4424 2 . 2 ""      
"1"  4425 2 . 2 ""      
"9"  4426 2 . 1 ""      
end

------------------ copy up to and including the previous line ------------------
private school

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 VlgCode int SID byte(STYPE S006 S00) str16 S00a
"10" 1 1 . 4 "N TO 5"
"19" 2 1 . 3 ""      
"20" 3 1 . 3 ""      
"1"  4 1 . 1 ""      
"9"  5 1 . 2 ""      
end

"VlgCode` is the merging ID, but I would like to change all variable names before merging. For the government school data, I would like to add either the prefix or suffix 'gov', and for the private school data, I would use 'pvt'.

[Clyde Schechter]

You do not provide example data, so I will try to describe abstractly how to do this. There's a good chance I will fail, in which case you should post back with example data from the two data sets you are trying to use.

First, we have to distinguish two kinds of variables in your data sets: the -merge- key and everything else. The -merge- key consists of all of the variables that must match up in the variables you are combining. This might be just a single variable such as year, or it might involve several variables. Another way to think of it is that in your -merge- command, the -merge- key consists of the variables that you list after the 1:1, 1:m or m:1 and before -using-. Then you can do this:

use private_data, clear
local merge_key list_your_merge_key_variables_here
ds `merge_key', not
rename (`r(varlist)') pvt=
merge 1:1 `merge_key' using government_data

Note: replace 1:1 by 1:m or m:1 if appropriate.

All of that said, it is legal, but pretty unusual to be -merge-ing two data sets whose variables are identically named. That sounds more like a candidate for -append-. Are you sure -merge- is really what you need here?

[Shahla Akram]

Thanks, I got your point. Could you please tell me how I can change the name of all datasets to 'pvt' without merging?

[Clyde Schechter]

I'm not sure I understand your question. Changing the name of a dataset is not something you can do in Stata. You have to do that in your operating system. (Indirectly, you can of course invoke your operating system's file renaming command using the Stata -shell- command, but that is still the OS doing the renaming.)

If you mean adding a pvt_ prefix to all the variables in a data set, the first four lines of the code shown in #2 do that. Just skip the -merge-. Do it for all the files where you want to rename the variables in this way.

If you mean that you have many such files and you would like to automate the process of doing them all, it hinges on how you can create a list of the names of all and only those files. And how you do that depends on how those files are named and where they are located. As the possibilities here are numerous, I will skip that part.

// ASSUMING YOU HAVE A COMPLETE LIST OF THE FILES WHOSE VARIABLES
// ARE TO BE RENAMED WITH A pvt_ PREFIX.  WE ASSUME THIS LIST IS IN
// LOCAL MACRO files_to_process

foreach f of local files_to_process {
    use "`f'", clear
    ds VlgCode, not
    rename (`r(varlist)') pvt_=
    save "`f'_renamed", replace
}

Notes:

1. This will leave the files themselves unchanged, instead creating a new file whose name has _renamed added to it.
2. Since Stata variable names are limited to 32 characters, and you are adding a four character prefix, if any of the original variable names is longer than 28 characters, the -rename- command will fail and will halt execution. Since you may or may not have any such problems, I have not written the code to be robust to this possibility. If you do encounter that problem, the solution is to first rename the original variable name to something that is 28 characters or shorter--typically just by truncating to the first 28, although that would not be a good approach if the 29th and later characters are actually informative. So exactly how to deal with that would depend on the specific problematic variable names.