Announcement

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

  • Generating missing quarters(observations) based on the available quarters(observations).

    Hi, I am having trouble in finding the missing quarter for every company. Companies have separate IDs. As shown in "Data I Have", I have the data on companies, but there are some years where I do not have the data for each of the 4 quarters of each year. Please see the below example of the data

    DATA I HAVE
    Old_year Old_Quarter Count ID
    2000 2 1 1
    2000 3 2 1
    2000 4 3 1
    2001 1 4 1
    2001 3 5 1
    2001 4 6 1
    2003 1 7 1
    2003 2 8 1
    *The code for the above input can be found at the end of the post.

    Now What I want from stata is to generate New_Year and New_Quarter based on the missing quarters from "data I have". For example, the 1st quarter of 2000 is missing, therefore, I want Stata to generate a new observation where the year is 2000 and the Quarter is 1. Since the 1st quarter of 2000 missing.

    Furthermore, if there is a full year which is missing, like in the "data I have" you can see the year 2002 is missing, then I want Stata to generate 4 quarters for 2002 as shown in "Data I want".

    DATA I WANT
    New_Year New_Quarter New_Count New_ID Old_year Old_Quarter Count ID
    2000 1 1 1 . . .
    2000 2 2 1 2000 2 1 1
    2000 3 3 1 2000 3 2 1
    2000 4 4 1 2000 4 3 1
    2001 1 5 1 2001 1 4 1
    2001 2 6 1 . . . .
    2001 3 7 1 . . . .
    2001 4 8 1 2001 4 5 1
    2002 1 9 1 . . . .
    2002 2 10 1 . . . .
    2002 3 11 1 . . . .
    2002 4 12 1 . . . .
    2003 1 11 1 2003 1 7 1
    2002 2 12 1 2002 2 8 1


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input Old_year Old_Quarter count ID
    2000 2 1 1
    2000 3 2 1
    2000 4 3 1
    2001 1 4 1
    2001 3 5 1
    2001 4 6 1
    2003 1 7 1
    2003 2 8 1
    end

    Any help would be greatly appreciated.

    Thanks
    Ahmed

  • #2
    So the first step is to get a real Stata quarterly date variable; the separate quarter and year variables will soon prove inadequate for other purposes anyway. Once we have that, -tsfill- does precisely what you ask for.

    Code:
    gen qdate = yq(Old_year, Old_Quarter)
    assert missing(qdate) == missing(Old_year, Old_Quarter)
    format qdate %tq
    tsset ID qdate
    tsfill

    Comment


    • #3
      Hi Clyde

      Thanks a lot for your quick and perfect solution.

      I have another question from the solution that you have provided.

      1) Is there any way in which we can convert the qdate into year and quarter separately? please see the below example
      qate Year quarter
      2001q1 2001 1
      2001q2 2001 2
      please note that I do not want the years or quarters in Stata date format. These are just simple numbers.

      I hope I have explained my question.

      Again, thanks a lot for your help.

      Regards
      Ahmed

      Comment


      • #4
        Code:
        gen year = year(dofq(qdate))
        gen quarter = quarter(dofq(qdate))
        will do it. But be aware that you will find it difficult to do any calculations with the separate year and quarter variables.

        Comment


        • #5
          Thank you for your quick and perfect response. I just need them to be there as a future reference and to know how to find it if need be, I will do the calculations using the qdate which you suggested earlier.
          Last edited by Ahmed Khan; 19 Feb 2020, 23:20.

          Comment

          Working...
          X