Announcement

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

  • Count the number of observations that have a change in value for a particular variable across the years

    Hi all,

    I am still a novice at using Stata, and I would like to ask a question.

    I have a panel data containing information about hospitals from years 2007 to 2016, and I would like to count the number of observations that have a change in value for a particular variable across the years. More specifically, I would like to count the number of hospitals that have changed ownership from public to private across the years.

    My thought process is to first bys hospital_id, and then count the number of observations where private changes from 0 to 1 across the years.
    However, I am not too sure what would be the command for this on Stata version 14.

    Your inputs will be greatly appreciated. Thank you in advance.

    Regards,
    Priscilla

  • #2
    Priscilla:
    welcome to this forum.
    As you do not provide a data example, the advice is theoretically only: most of the code to be created in Stata depends on whether multiple ownership type changes (eg, public, then private and public again) are possible during the 2007-2016 timespan
    Kind regards,
    Carlo
    (Stata 19.0)

    Comment


    • #3
      Hi Priscilla,

      You could try:

      Code:
      egen double change = sd(private), by(hospital_id)
      keep if change
      unique hospital_id

      Comment


      • #4
        Hi Carlo,

        Thank you very much for your advice, indeed, I did not consider if there are multiple ownership type changes, which I should.

        May I also ask how will the commands differ if there are multiple ownership type changes, as compared to if there is only a single ownership type change (from public to private)?

        I have quickly eye-balled my dataset, and it seems that mine should only have a single ownership type change.
        I have attached an image regarding an example of a hospital (highlighted in blue) where it changed ownership once from public to private, and I would like to count how many such hospitals are there in total.

        Thank you so much!

        Regards,
        Priscilla

        Attached Files

        Comment


        • #5
          Hi Iuri,

          I managed to run your suggested commands, but I don't really understand the thought process. Would you mind explaining?

          Thank you so much.

          Regards,
          Priscilla

          Comment


          • #6
            Priscilla:
            as per FAQ, please use -dataex-, not screenshots to post an excert of your data. Thanks.
            The following toy-example consider two waves of data and two ids;
            Code:
            set obs 4
            g id=1 in 1/2
            replace id=2 if id==.
            bysort id: g year=2018 if _n==1
            replace year=2019 if year==.
            bysort id: g Ownership=0 if _n==1
            replace Ownership=0 if id==1 & Ownership==.
            replace Ownership=1 if Ownership==.
            label define Ownership 0 "Public" 1 "Private"
            label val Ownership Ownership
            bysort id: g Changed_Ownership=0 if Ownership[1]==Ownership[2]
            bysort id: replace Changed_Ownership=1 if Ownership[1]!=Ownership[2]
            
            . list
            
                 +---------------------------------+
                 | id   year   Owners~p   Change~p |
                 |---------------------------------|
              1. |  1   2018     Public          0 |
              2. |  1   2019     Public          0 |
              3. |  2   2018     Public          1 |
              4. |  2   2019    Private          1 |
                 +---------------------------------+
            Kind regards,
            Carlo
            (Stata 19.0)

            Comment


            • #7
              Hi Carlo,

              Thanks for your reply and reminder, I really appreciate it

              Using your logic, I have tried to incorporate it into my dataset, but I am still unable to get what I want.

              This is the command I have been using:

              bys hospital_id: gen ChangedOwnership=0 if private[_n+1]==private[_n]==0
              bys hospital_id: replace ChangedOwnership=0 if private[_n+1]==private[_n]==1
              bys hospital_id: replace ChangedOwnership=1 if private[_n+1]>private[_n]


              Is there something wrong with my command?

              Thank you.

              Regards,
              Priscilla

              Comment


              • #8
                Originally posted by Priscilla Teo View Post
                Hi Iuri,

                I managed to run your suggested commands, but I don't really understand the thought process. Would you mind explaining?

                Thank you so much.

                Regards,
                Priscilla
                Hi Priscilla,
                I generated a "change" variable with the standard deviation of the variable of interest (private) by firm. For those firms with no change in ownership, the "change" value will be zero. You can either recode it to zeros and ones or drop the zeros (my second command) and count the firms that changed ownership.
                This procedure works with several years per firm and several changes (private->public, the other way around, or public->private->public) over the years.
                I hope this helps.
                Iuri.

                Comment


                • #9
                  Hi luri,

                  Thanks for your explanation - I see it now.
                  However, how should I edit your codes if I am only interested with those hospitals that changed from public->private, be it a once-off change or a 'several changes' scenario.
                  This is because I am only interested to know how many hospitals changed "from public to private", and not the other way round.

                  Thank you.

                  Regards,
                  Priscilla

                  Comment


                  • #10
                    Priscilla,
                    It would help a lot if you posted part of you data using the -dataex- command. Install it (if not installed) with ssc install dataex
                    Don't worry about the data leakage. dataex just extracts a portion of your data.
                    You should drop the identifying data, such as the hospital name, before dataex.
                    Copy and paste the result of the dataex command in the forum so we can test something before responding.
                    Best,
                    Iuri.

                    Comment


                    • #11
                      I would just count the number of runs or spells in the ownership variable

                      I'll adapt the sandbox example of Carlo Lazzaro

                      Code:
                      clear
                      set obs 9
                      egen id = seq(), to(3)
                      bysort id : gen year = 2017 + _n
                      
                      gen ownership = inlist(_n, 4, 5, 8)
                      label define ownership 0 "Public" 1 "Private"
                      label val ownership ownership
                      
                      list, sepby(id)
                      
                           +----------------------+
                           | id   year   owners~p |
                           |----------------------|
                        1. |  1   2018     Public |
                        2. |  1   2019     Public |
                        3. |  1   2020     Public |
                           |----------------------|
                        4. |  2   2018    Private |
                        5. |  2   2019    Private |
                        6. |  2   2020     Public |
                           |----------------------|
                        7. |  3   2018     Public |
                        8. |  3   2019    Private |
                        9. |  3   2020     Public |
                           +----------------------+
                      
                      
                      tsset id year
                      tsspell ownership
                      
                      list, sepby(id)
                      
                          +---------------------------------------------+
                           | id   year   owners~p   _spell   _seq   _end |
                           |---------------------------------------------|
                        1. |  1   2018     Public        1      1      0 |
                        2. |  1   2019     Public        1      2      0 |
                        3. |  1   2020     Public        1      3      1 |
                           |---------------------------------------------|
                        4. |  2   2018    Private        1      1      0 |
                        5. |  2   2019    Private        1      2      1 |
                        6. |  2   2020     Public        2      1      1 |
                           |---------------------------------------------|
                        7. |  3   2018     Public        1      1      1 |
                        8. |  3   2019    Private        2      1      1 |
                        9. |  3   2020     Public        3      1      1 |
                           +---------------------------------------------+
                      The number of spells is just one more than the number of changes. The spells can be obtained more directly by


                      Code:
                      bysort id (year) : gen spell = sum(Ownership != Ownership[_n-1])
                      but the tsspell command from SSC has some extra features for more complicated situations and needs.

                      The principles of identifying spells are discussed within https://www.stata-journal.com/sjpdf....iclenum=dm0029

                      Comment


                      • #12
                        Hi Nick, Carlo and luni,

                        Thank you for your inputs.
                        Your suggestions are really helpful, but it is still not exactly what I need.
                        Maybe I should make myself clearer, and I have pasted part of my data example below.

                        Basically, 'private' variable below is the same as 'ownership' which you guys have created in the sandbox example mentioned in the previous replies.
                        What I'm interested to count is the number of hospitals that have converted ownership from public (private==0) to private (private==1) only(not the other way round).
                        It doesn't matter how many times there is a change of ownership for any particular hospital within that time period of year 2007 to 2016, as long as it did convert from public to private at any point in time within 2007 to 2016, then that hospital is what I'm interested in and I'll have to count that in.
                        So for the data example below, the number of hospitals that have converted from public to private is: 2 (due to hospital_id = 9 & hospital_id =12)


                        Code:
                        * Example generated by -dataex-. To install: ssc install dataex
                        clear
                        input long hospital_id float(Year private)
                         9 2007 1
                         9 2008 1
                         9 2009 1
                         9 2010 0
                         9 2011 0
                         9 2012 1
                         9 2013 1
                         9 2014 1
                         9 2015 1
                         9 2016 1
                        10 2007 0
                        10 2008 0
                        10 2009 0
                        10 2010 0
                        10 2011 0
                        10 2012 0
                        10 2013 0
                        10 2014 0
                        10 2015 0
                        10 2016 0
                        11 2007 0
                        11 2008 0
                        11 2009 0
                        11 2010 0
                        11 2011 0
                        11 2012 0
                        11 2013 0
                        11 2014 0
                        11 2015 0
                        11 2016 0
                        12 2007 1
                        12 2008 1
                        12 2009 1
                        12 2010 0
                        12 2011 0
                        12 2012 1
                        12 2013 1
                        12 2014 1
                        12 2015 1
                        12 2016 1
                        end
                        label values hospital_id hospital_id
                        label def hospital_id 9 "602", modify
                        label def hospital_id 10 "604", modify
                        label def hospital_id 11 "606", modify
                        label def hospital_id 12 "608", modify
                        Any ideas what can I do?

                        Thank you so much again!

                        Regards,
                        Priscilla
                        Last edited by Priscilla Teo; 03 Mar 2019, 09:55.

                        Comment

                        Working...
                        X