Announcement

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

  • Collapse and Date problem: Months to Quarter average

    Dear All
    I have monthly series of interest rates and want to convert it into a quarterly series. I have tried to do this using collapse but I think I failed because of a problem in identifying dates in Stata.

    This is what I have done:
    Code:
    gen date = daily(DATE,"MDY")
    format date %td
    gen year = year(date)
    gen qua= quarter(date)
    gen mon = month(date)
    
    collapse (mean) Interest, by (qua)
    The date part of the code produces corrects year, quarter and month but when I run collapse the output is ony 4 quarters.
    I think the problem might be in the way date has been producted.

    Can anyone help to solve this problem?

    My data is:
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input str9 DATE float Interest
    "4/1/1953"  2.36
    "5/1/1953"  2.48
    "6/1/1953"  2.45
    "7/1/1953"  2.38
    "8/1/1953"  2.28
    "9/1/1953"   2.2
    "10/1/1953" 1.79
    "11/1/1953" 1.67
    "12/1/1953" 1.66
    "1/1/1954"  1.41
    "2/1/1954"  1.14
    "3/1/1954"  1.13
    "4/1/1954"   .96
    "5/1/1954"   .85
    "6/1/1954"   .82
    "7/1/1954"   .84
    "8/1/1954"   .88
    "9/1/1954"  1.03
    "10/1/1954" 1.17
    "11/1/1954" 1.14
    "12/1/1954" 1.21
    "1/1/1955"  1.39
    "2/1/1955"  1.57
    "3/1/1955"  1.59
    "4/1/1955"  1.75
    "5/1/1955"   1.9
    "6/1/1955"  1.91
    "7/1/1955"  2.02
    "8/1/1955"  2.37
    "9/1/1955"  2.36
    "10/1/1955" 2.39
    "11/1/1955" 2.48
    "12/1/1955" 2.73
    "1/1/1956"  2.58
    "2/1/1956"  2.49
    "3/1/1956"  2.61
    "4/1/1956"  2.92
    "5/1/1956"  2.94
    "6/1/1956"  2.74
    "7/1/1956"  2.76
    "8/1/1956"   3.1
    "9/1/1956"  3.35
    "10/1/1956" 3.28
    "11/1/1956" 3.44
    "12/1/1956" 3.68
    "1/1/1957"  3.37
    "2/1/1957"  3.38
    "3/1/1957"  3.42
    "4/1/1957"  3.49
    "5/1/1957"  3.48
    "6/1/1957"  3.65
    "7/1/1957"  3.81
    "8/1/1957"  4.01
    "9/1/1957"  4.07
    "10/1/1957" 4.01
    "11/1/1957" 3.57
    "12/1/1957" 3.18
    "1/1/1958"  2.65
    "2/1/1958"  1.99
    "3/1/1958"  1.84
    "4/1/1958"  1.45
    "5/1/1958"  1.37
    "6/1/1958"  1.23
    "7/1/1958"  1.61
    "8/1/1958"   2.5
    "9/1/1958"  3.05
    "10/1/1958" 3.19
    "11/1/1958"  3.1
    "12/1/1958" 3.29
    "1/1/1959"  3.36
    "2/1/1959"  3.54
    "3/1/1959"  3.61
    "4/1/1959"  3.72
    "5/1/1959"  3.96
    "6/1/1959"  4.07
    "7/1/1959"  4.39
    "8/1/1959"  4.42
    "9/1/1959"     5
    "10/1/1959"  4.8
    "11/1/1959" 4.81
    "12/1/1959" 5.14
    "1/1/1960"  5.03
    "2/1/1960"  4.66
    "3/1/1960"  4.02
    "4/1/1960"  4.04
    "5/1/1960"  4.21
    "6/1/1960"  3.36
    "7/1/1960"   3.2
    "8/1/1960"  2.95
    "9/1/1960"  3.07
    "10/1/1960" 3.04
    "11/1/1960" 3.08
    "12/1/1960" 2.86
    "1/1/1961"  2.81
    "2/1/1961"  2.93
    "3/1/1961"  2.88
    "4/1/1961"  2.88
    "5/1/1961"  2.87
    "6/1/1961"  3.06
    "7/1/1961"  2.92
    end

  • #2
    The problem is using the wrong function. Stata is doing what you tell it to do.

    As you report,
    quarter() yields results that are 1 to 4, so your collapse will yield means over all years for all 4 quarters. The fact that year and month and quarter are all correct does not help if you just tell collapse about the quarter

    You should use qofd() to get quarterly dates and then use the resulting variable in the collapse. To make it explicit, 2017q1 is a quarterly date and 1 is a quarter.

    You would get the same results if you used quarter and year jointly, but at some point sooner or later you will predictably need a quarterly date variable.

    Comment


    • #3
      Originally posted by Lisa Wilson View Post
      Dear All
      I have monthly series of interest rates and want to convert it into a quarterly series. I have tried to do this using collapse but I think I failed because of a problem in identifying dates in Stata.
      As suggested by Nick, you can try:
      Code:
      gen date = daily(DATE,"MDY")
      format date %td
      gen yq = qofd(date)
      format yq %tq
      
      collapse (mean) Interest, by(yq)
      Ho-Chuan (River) Huang
      Stata 17.0, MP(4)

      Comment

      Working...
      X