Stat Computing > Stata > FAQ				Help the Stat Consulting Group by giving a gift
Loading

Stata FAQ: How can I collapse a daily time series to a monthly time series?

Let's say that the time series of our analysis comes as a daily time series but we would want to analyze it as a monthly time series. We need to collapse the daily data to monthly data. Stata has a great collection of date conversion functions for this type of tasks. We will show an example on how to collapse our daily time series to a monthly time series by making use of a function of this kind. It is helpful to know these functions before we start our task. We will issue command "help dcfcns" to display these available date conversion functions. 

help dcfcns

----------------------------------------------------------------------------------------------
help for dcfcns                             manual:  [U] 27.3.4 Translating between time units
----------------------------------------------------------------------------------------------

Date-conversion functions

    Conversion to %d (%td) dates:
.. dofd(%td_daily_date_exp) ...         returns %d (%td) date
.. dofw(%tw_weekly_date_exp) ...        returns %d (%td) date
.. dofm(%tm_monthly_date_exp) ...       returns %d (%td) date
.. dofq(%tq_quarterly_date_exp) ...     returns %d (%td) date
.. dofh(%th_halfyearly_date_exp) ...    returns %d (%td) date
.. dofy(%ty_yearly_date_exp) ...        returns %d (%td) date

    Conversion from %d (%td) dates:
.. dofd(%td_daily_date_exp) ...         returns %d (%td) date
.. wofd(%td_daily_date_exp) ...         returns %tw date
.. mofd(%td_daily_date_exp) ...         returns %tm date
.. qofd(%td_daily_date_exp) ...         returns %tq date
.. hofd(%td_daily_date_exp) ...         returns %th date
.. yofd(%td_daily_date_exp) ...         returns %ty date

....

The data set we use here is from Stata 8 manual and it can be accessed over the internet. If you have an internet connection, you can get the data set by the following command.

webuse dow1, clear

list in 1/10

     +--------------------------------------+
     | dowclose        date    t     ln_dow |
     |--------------------------------------|
  1. |    292.1   02jan1953    1   5.677096 |
  2. |    293.8   05jan1953    2   5.682899 |
  3. |    292.2   06jan1953    3   5.677439 |
  4. |    290.8   07jan1953    4   5.672636 |
  5. |    290.4   08jan1953    5   5.671259 |
     |--------------------------------------|
  6. |    287.5   09jan1953    6   5.661223 |
  7. |    285.2   12jan1953    7   5.653191 |
  8. |    286.9   13jan1953    8   5.659134 |
  9. |    287.4   14jan1953    9   5.660875 |
 10. |    288.2   15jan1953   10   5.663655 |
     +--------------------------------------+

Variable date apparently is in a daily format. Here is how to generate a variable from it with only information on month and year.

gen dm = mofd(date)

format dm %tm

list in 1/10

     +------------------------------------------------+
     | dowclose        date     t     ln_dow       dm |
     |------------------------------------------------|
  1. |    292.1   02jan1953     1   5.677096   1953m1 |
  2. |    293.8   05jan1953     2   5.682899   1953m1 |
  3. |    292.2   06jan1953     3   5.677439   1953m1 |
  4. |    290.8   07jan1953     4   5.672636   1953m1 |
  5. |    290.4   08jan1953     5   5.671259   1953m1 |
     |------------------------------------------------|
  6. |    287.5   09jan1953     6   5.661223   1953m1 |
  7. |    285.2   12jan1953     7   5.653191   1953m1 |
  8. |    286.9   13jan1953     8   5.659134   1953m1 |
  9. |    287.4   14jan1953     9   5.660875   1953m1 |
 10. |    288.2   15jan1953    10   5.663655   1953m1 |
     |------------------------------------------------|

We are now ready to collapse the data to monthly level. By default, the collapse command collapses every variable to its mean.

collapse dowclose ln_dow, by(dm)

list in 1/10

     +-------------------------------+
     |      dm   dowclose     ln_dow |
     |-------------------------------|
  1. |  1953m1   288.4524   5.664502 |
  2. |  1953m2   283.9611   5.648786 |
  3. |  1953m3   286.7909   5.658709 |
  4. |  1953m4   275.2857   5.617776 |
  5. |  1953m5   276.9381   5.623769 |
     |-------------------------------|
  6. |  1953m6   266.8864   5.586792 |
  7. |  1953m7   270.3261   5.599611 |
  8. |  1953m8   272.2048   5.606421 |
  9. |  1953m9   261.9048   5.567924 |
 10. | 1953m10   270.7191   5.600968 |
     +-------------------------------+

tsset dm, monthly
        time variable:  dm, 1953m1 to 1990m2

UCLA Researchers are invited to our Statistical Consulting Services
We recommend others to our list of Other Resources for Statistical Computing Help
These pages are Copyrighted (c) by UCLA Academic Technology Services