Announcement

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

  • Sort panel data according to country and year

    Hello,

    I want to generate a dummy variable which takes the value of 1 if the variable "Score" is bigger or equal than the country-year median and 0 otherwise. I have a list of firms in different countries in different years taking a loan, so for example I have a firm from the US taking a loan in 2005 and also in 2010. Now I want to sort my panel data according to country and year and then calculate for each country and year the median of "Score". In a next step I want to compare the median Score to the individual Score for each firm. For example I have calculated the median score for borrowers in the US in 2010 and then I compare this score with each individual firm taking a loan in the US in 2010, and if the individual score is higher or equal to the median the dummy variable is 1 and 0 otherwise. I dont know how to do this and would be grateful for every help!

  • #2
    Jana:
    I'm not sure I got your query right, but I read it as the two steps you've in mind actually overlap.
    That said, I do hope that the following toy-example can be helpful:
    Code:
    . use "https://www.stata-press.com/data/r16/nlswork.dta"
    (National Longitudinal Survey.  Young Women 14-26 years of age in 1968)
    
    . bysort idcode (year): egen country_median=median( ln_wage )
    
    . bysort idcode: gen wanted_dummy=1 if ln_wage>= country_median
    
    . replace wanted_dummy=0 if wanted_dummy==.
    
    . list idcode year ln_wage country_median wanted_dummy in 1/10
    
         +------------------------------------------------+
         | idcode   year    ln_wage   countr~n   wanted~y |
         |------------------------------------------------|
      1. |      1     70   1.451214   2.100267          0 |
      2. |      1     71    1.02862   2.100267          0 |
      3. |      1     72   1.589977   2.100267          0 |
      4. |      1     73   1.780273   2.100267          0 |
      5. |      1     75   1.777012   2.100267          0 |
         |------------------------------------------------|
      6. |      1     77   1.778681   2.100267          0 |
      7. |      1     78   2.493976   2.100267          1 |
      8. |      1     80   2.551715   2.100267          1 |
      9. |      1     83   2.420261   2.100267          1 |
     10. |      1     85   2.614172   2.100267          1 |
         +------------------------------------------------+
    
    .
    Kind regards,
    Carlo
    (Stata 18.0 SE)

    Comment


    • #3
      Thanks a lot! This is nearly what I was looking for. But I want to calculate the median of the country in the respective year, for example the median for the country US in 2010 is 10.2 and for the US in 2011 it is 15.0 etc. In your example the median was calculated for the country irrespective of the year.

      Comment


      • #4
        So, your solution is just a twist on @Carlo Lazzaro's solution. With some other simplifications,

        Code:
        bysort year: egen year_median = median( ln_wage ) 
         gen wanted_dummy = ln_wage >= year_median

        Comment


        • #5
          But then I would just look at the year median irrespective of the country, right? But I need a country-year median

          Comment


          • #6
            Jana:
            you can try:
            Code:
            bysort country year: egen year_median = median( ln_wage )
            However, depending on what you're after, please note that:
            Code:
            bysort idcode (year):egen year_median = median( ln_wage )
            gives you back the country specific median of -ln_wage- calculated across all the years -year- is composed of (that is the whole time period the panel stretches over),
            whereas:
            Code:
            bysort country year: egen year_median = median( ln_wage )
            gives you back the country-year specific median of -ln_wage- calculated for each year of -year- (that in this example=country-year specific -ln_wage-).
            Kind regards,
            Carlo
            (Stata 18.0 SE)

            Comment


            • #7
              Thanks! It looks now exactly how I need it!

              Comment

              Working...
              X