Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • creating dummy if value in var2 exists in var1 by year

    Hi all,

    I'm trying to create a dummy that takes a value of 1 when the owner of a company is also a company in the database for that given year.
    I have provided an example of the database below.


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input float(year company owner)
    1 1 .
    1 2 .
    1 3 2
    2 1 .
    2 2 .
    2 4 1
    3 1 .
    3 2 .
    3 4 .
    end
    I thus need a dummy to take the value of 1 in observation 2 and 4. I have tried the following without any succes:

    Code:
    sort year company
    by year: gen dummy=1 if company==owner
    But this only looks per observation if the owner and company are equal.

    Code:
    sort year company
    by year: gen dummy=1 if inlist(company, owner)
    This does the same I think.

    Code:
    sort year company
    by year: gen dummy=1 if inlist(company, owner[_n]/owner[_N])

    Hope somebody can help me.

    Many thanks in advance!

    Best,
    Niels

  • #2
    I think this can be handled as a -merge-. However, when you say that observations 2 and 4 meet your criterion, I'm confused: owner[2] and owner[4] are missing, and therefore, how can the owner of those observations be known at all, i.e., how could one know the owner of either of those companies? The only non-missing values of owner are in observation 3 and observation 6, so I'm hoping that's what you mean.

    That confusion aside, here's some code that fits my interpretation of what you said, and perhaps it's of some use.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input float(year company owner)
    1 1 .
    1 2 .
    1 3 2
    2 1 .
    2 2 .
    2 4 1
    3 1 .
    3 2 .
    3 4 .
    end
    //
    list
    preserve
    tempfile temp
    drop owner
    // company variable in this file will match to owner in main file
    rename company owner
    gen byte OwnerIsACompany = 1
    save `temp'
    restore
    //
    merge m:1 owner year using `temp', keepusing(OwnerIsACompany)
    keep if _merge != 2
    drop _merge
    sort year company // your apparent sort order
    list
    Last edited by Mike Lacy; 21 Jun 2018, 14:19.

    Comment


    • #3
      Hi Mike,

      Many thanks for your answer!

      Why I meant that observation 2 and 4 should have a dummy, is because I want a dummy that shows 1 when the company in that observation is an owner of an other company.
      So in observation 2 we have company 2, which turns out to be the owner of company 3 (obs 3). In obs 4 is company 1, which is the owner of company 4 based on obs 6.
      I want a dummy that shows that the observation is the holding company. Your method shows when a company is a subsidiary. Sorry if my writing gave any confusion.

      Luckily I could still use your code by changing owner to company, so it all works out.

      Again, many thanks.

      Best,
      Niels

      Comment


      • #4
        rangestat, a wonderful package created by Robert Picard and Nick Cox provides a more concise solution.
        Code:
        mvencode owner, mv(-999)
        rangestat (count) tag=company, interval(owner company company) by(year)
        replace tag=tag!=.
        mvdecode owner, mv(-999=.)

        Comment

        Working...
        X