Import new variable from Excel to existing dataset?

[Jennifer Bashford]

Hi there - I built a new dataset in Excel and have been using it in Stata (copied and pasted in the data editor, no problem). But now I have new columns of data that I need to add to it and I can't figure out how, without starting all over and just copying and pasting the entire Excel spreadsheet all over again in a new dataset. There has to be an easier to way to add to my existing dataset.

Can anyone help?
Thanks!

(It's a dataset of info on the 50 states (rows in excel), with about 20 variables for each state (columns in excel))

[Clyde Schechter]

First of all, if you are doing this for any reason other than killing time, you should never create a data set by copy/pasting from Excel to the data editor. There are two reasons. First, it is error prone: it is too easy to omit a row or column and not realize it. Second, it leaves no audit trail of how the data set was created--so if a question arises about it a year from now, nobody will know what was done. Data management and analysis without a complete audit trail from beginning to end cannot and should not be trusted.

If the Excel data set is well laid out (which it needs to be for copy/paste to appear to work anyway) the correct way to bring it into Stata is with the -import excel- command. Read -help import excel- for information on how to use it, and, in particular, what options to use for particular situations.

With that out of the way, the solution to the question you posed is to create a new data set for the additional variables and then use the -merge- command to put them together. If you are not familiar with using -merge-, read -help merge- and also the associated PDF documentation (blue link near the top of the -help merge- page). If you are unsure how to proceed, or if you have difficulty when you try, do post back, showing examples of both Stata data sets. (Do that using the -dataex- command so that the examples are usable by others. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.)

[Jennifer Bashford]

Great, thanks. I'm used to Excel, so that's why I built everything there over time. Would you suggest building it directly in the data editor instead? (For next time?)

[Mike Lacy]

I'd second Clyde's comments about copy/paste from Excel. But, no, I would never do serious data entry in the Stata data editor. Besides being a relatively cumbersome tool for that purpose, it means that your data entry people (if you're lucky enough to have them) have to have Stata. I know a lot of Stata users, but I don't think I know any who do their data entry in Stata. Do your data entry in Excel or in any simple spreadsheet clone you like. Then, import to Stata. There are also other nice freeware data entry packages around, the names of which escape me at the moment.

[Jennifer Bashford]

Okay, thanks! And then just merge additional variables as a new dataset?

[Clyde Schechter]

Yes, exactly.

[Nkira Anyika]

What type of merge would it be ? I just want to add a new column to the end of my data set. There isn't a common variable so i dont think its a 1:1 merge.
The examples ive read seem to only use examples with a common column ? (didn't think this needed a dataex- upload as its simple to explain )


example

Dataset 1
name age gender height weight shoe size


Dataset 2
eye-colour skin-tone college-education


Final merged dataset

name age gender height weight shoe size eye-colour skin-tone college-education <- so i want to add dataset 2 on the end of dataset 1 ?

[Nkira Anyika]

ignore me i have figured it out!!