Merging

[Morten Gravesen]

Hi

I have a paneldataset which contains 1549 unique isin numbers (identification number) with each of them having 13 observations for the years 2005 - 2017.

However, I have a list of only 1,399 of these identifiers that I need and I want to drop the rest of them.

Is there any easy way I can do this?

I have tried merging without luck using the following approach:


sort masterfile based on isin (the unique identifier)

--- this creates content like:

id
1
1
1
1
2
2
2
2

I have then created a second file called "identifier" containing the 1,399 identifiers that I want. I have sorted them as well:

id copy
1 1
2 2
3 3
4 4


I then merged from the masterfile hoping that it would create a new variable called "copy" matching the id from both files:

merge id using identifier

id copy
1 1
1 1
1 1
1 1
2 2
2 2
2 2
2 2

Because there will be no match for the 150 uniquely identifiers, the "copy" variable would have missing values here. Thus, my idea was to just drop if missing(copy).

However, what is returned is one big mess.

Any help please?

Best regards, Morten

[William Lisowski]

Your general approach is correct, and even though you are using outdated syntax for the merge command, I would have expected it to work. Unfortunately, you do not explain in what way your merged data are "one big mess" and so it's impossible to tell you why that is so.

Let me instead provide an example of code that should start you in the correct direction.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id
1
3
end
tempfile keepme
save `keepme'
* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id year)
1 2005
1 2006
1 2007
1 2008
2 2005
2 2006
2 2007
2 2008
3 2005
3 2006
3 2007
3 2008
end
merge m:1 id using `keepme', keep(match)
drop _merge
list, noobs sepby(id)

. list, noobs sepby(id)

  +-----------+
  | id   year |
  |-----------|
  |  1   2005 |
  |  1   2006 |
  |  1   2007 |
  |  1   2008 |
  |-----------|
  |  3   2005 |
  |  3   2006 |
  |  3   2007 |
  |  3   2008 |
  +-----------+