Announcement

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

  • Converting monthly data into quarterly for a panel with multiple variables

    Hi all,

    I am working with a panel dataset which contains the following variables:
    • wage bin (defined as bin)
    • region identifier (defined as region)
    • employment count (defined as emplcount)
    • month
    The panel covers wage bins from 5 to 1000 across 169 different regions and the observations are monthly from 200401 to 201812. I am sending an example of the data below. My apologies for the format, I will use dataex to display examples in future posts, but I am posting from a PC which does not have Stata on it.

    bin region emplcount month
    5 101 13 200401
    5 101 16 200402
    5 101 14 200403
    5 102 34 200401
    5 102 31 200402
    5 102 33 200403
    10 101 41 200401
    10 101 44 200402
    10 101 43 200403
    10 102 47 200401
    10 102 47 200402
    10 102 47 200403
    15 101 27 200401
    15 101 23 200402
    15 101 26 200403
    15 102 21 200401
    15 102 28 200402
    15 102 29 200403


    I would like to convert the monthly employment counts into quarterly employment counts for each wage bin in each region, but I am not sure how to do this for a panel with multiple variables.


    I will be very grateful for any advice.

    Thanks,

    Frank

  • #2
    The monthly date variable is not fit for purpose, even as a monthly date, as (e.g.) thinking about the jump from 200412 to 201501, a jump of 89 not 1, will show.

    This has often been discussed here and also in the Stata Journal: https://journals.sagepub.com/doi/pdf...867X1801800312 is as I write accessible, at least to me.

    Here are some finger exercises in technique,. First, I take apart the components of year and month and reassemble them. Then, I get a quarterly date in two different ways. The orthodox Stata way is just to push through one conversion function after another. The other way is a variation on the first.


    Code:
    . clear
    
    . set obs 8
    number of observations (_N) was 0, now 8
    
    . gen long month = cond(_n <= 4, 200408 + _n, 200500 + _n - 4)
    
    . list, sep(4)
    
         +--------+
         |  month |
         |--------|
      1. | 200409 |
      2. | 200410 |
      3. | 200411 |
      4. | 200412 |
         |--------|
      5. | 200501 |
      6. | 200502 |
      7. | 200503 |
      8. | 200504 |
         +--------+
    
    . gen mdate = ym(floor(month/100), mod(month, 100))
    
    . format mdate %tm
    
    . gen qdate1  = qofd(dofm(mdate))
    
    . gen qdate2 = yq(floor(month/100), ceil(mod(month, 100)/3))
    
    . format qdate* %tq
    
    . list, sep(4)
    
         +------------------------------------+
         |  month     mdate   qdate1   qdate2 |
         |------------------------------------|
      1. | 200409    2004m9   2004q3   2004q3 |
      2. | 200410   2004m10   2004q4   2004q4 |
      3. | 200411   2004m11   2004q4   2004q4 |
      4. | 200412   2004m12   2004q4   2004q4 |
         |------------------------------------|
      5. | 200501    2005m1   2005q1   2005q1 |
      6. | 200502    2005m2   2005q1   2005q1 |
      7. | 200503    2005m3   2005q1   2005q1 |
      8. | 200504    2005m4   2005q2   2005q2 |
         +------------------------------------+
    Although
    Code:
    help datetime
    remains indispensable, familiarity with a core set of Stata functions is invaluable. https://www.stata-journal.com/articl...article=dm0058 tried to cover the highlights concisely.

    Comment


    • #3
      Thank you very much for the advice and the useful tips Nick. I have read the articles and I will keep your tips in mind when coding monthly date indicators in the future.

      Once I have created the qdate2 indicator, would you have any advice on how to generate the quarterly employment counts in each wage bin in each region please? I was thinking of trying:

      Code:
      collapse (mean)
      but I am not sure how to implement it on a panel when I have 2 different variables (bin and region).

      I greatly appreciate all help and advice.

      Comment


      • #4
        Hi Nick Cox, I want to convert monthly data to quarterly data. I am using panel data. Previously, I used the following command to get their average value:

        foreach var of varlist x1-x10 {
        egen avg_q_`var' = mean(`var'), by(id tq)
        }


        Now I want to take their multiplied values to convert from monthly to quarterly. For example, for x1
        x1 id tm tq x1_q
        0.25 1 2001m1 2001q1 -0.01
        0.36 1 2001m2 2001q1 -0.01
        -0.16 1 2001m3 2001q1 -0.01
        0.89 1 2001m4 2001q2 0.46
        0.69 1 2001m5 2001q2 0.46
        0.75 1 2001m6 2001q2 0.46
        0.32 2 2001m1 2001q1 -0.13
        0.45 2 2001m2 2001q1 -0.13
        -0.89 2 2001m3 2001q1 -0.13
        0.23 2 2001m4 2001q2 0.05
        0.36 2 2001m5 2001q2 0.05
        0.56 2 2001m6 2001q2 0.05
        How can I revise my above command to get such a result? Please let me know. I really appreciate any help you can provide.

        Comment


        • #5
          Well, -0.01 is not the mean of 0.25 0.36 -0.16 and 0.46 is even more obviously not the mean of 0.89 0.69 0.75. So x1_q does not correspond to x1. Is it supposed to do that?

          The code looks right.

          Comment


          • #6
            Hi Nick, thank you so much for your quick response. You are right. They are multiplied values, i.e., 0.25*0.36*(-0.16) = -0.01. I want to get their multiplied values, not mean to convert from month to quarter. Then how I can revise the code?

            Comment


            • #7
              Fair enough.

              Code:
              bysort id tq : gen wanted = x1[1] * x1[2] * x1[3]
              will work at generating the product and will return missing if any value is missing or absent.

              Comment


              • #8
                Hi Nick, thank you once again. I have revised my code as

                foreach var of varlist x1 {
                bysort id tq : gen q_`var' = `var'[1]*`var'[2]*`var'[3]
                }


                It works!

                Comment


                • #9
                  A loop over one item is avoidable.

                  Just to note that a recipe sometimes seen for multiplication -- in essence exp(sum(log)) -- is quite inapplicable here where some terms are negative and presumably zero is not impossible either.

                  Comment


                  • #10
                    search tscollap

                    Comment

                    Working...
                    X