Changing content of time information (e.g. January to March)

[Hans Albrecht]

Dear all,
I am experiencing some trouble in the last steps of dealing with my time series dataset. I had to collapse my dataset from monthly to quarterly observations. For this I had to use the "collapse" command. I cannot use the "tscollap" command for reasons that I am happy to explain but I think this would just go offtopic a bit.

After collapsing the data should be quarterly data containing the information:
31.03.1970
30.06.1970
30.09.1970
31.12.1970
etc.

However, once I format the data, Stata sets it as.
01.01.1970
01.04.1970
01.07.1970
01.10.1970
etc.

It does not change the calculations but for the output file I would need to change this back somehow.
I could not find anything helpful so far any advice on how to do this would be very much appreciated.

Thank you very much in advance!

Best
Hans

[Nick Cox]

In general, not showing us data directly (e.g. using dataex (SSC)) and not showing us real code that you used (please read FAQ Advice #12 again) just increases the amount of guessing we have to do.

You have some date variable there. Please show us the results of

describe datevar
su datevar

where clearly you should replace datevar with the variable name you are not showing us.

Independently of that, the difference here is, or appears to be, just between the last day of each quarter and the first. Mapping to quarterly dates with qofd() and assigning a quarterly display format should fix the problem.

Independently of that too, a collapse to one observation per quarter won't itself produce quarterly dates or a quarterly date display. collapse doesn't know anything about that conversion, which is just your private view of what you are doing.

But, as said, I am guessing. In particular, I am puzzled about how monthly data have daily dates associated. Perhaps it is just that you have data for the last day of each month. Note that those are daily data with gaps, so far as Stata is concerned, not monthly data. The difference is subtle but may be behind your problem with tscollap (STB) that you do not explain.

[Hans Albrecht]

Dear Nick,

thank you for your reply. I'll try to be as detailed as possible now: first of all, here is the output of the commands:

. describe date

              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------
date            float   %tq                   

. su date

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
        date |        189         129    54.70375         35        223

The variable is set as a quarterly time series set and Stata recognises the delta as one quarter. I know that this does not happen automatically after the collapse function so I set this up using the tsset command and formatted it as quarterly data after I had collapsed the monthly set (see code below).


Concerning my time series definitions and the reason why the tscollap function did not work for me:
My set is quite large containing more than 150 variables so in collapsing them I did not want to actually write down all of them. What I did is I used the following commands to collapse my monthly set (this is the relevant excerpt):

gen date_q = qofd(date) /* with date being a monthly time variable
format date_q %tq

tsunab allvars: _all
loc vars_to_exclude "date_q var1 var2 var3 var4"
collapse (last) `:list allvars - vars_to_exclude' (sum) var1 var2 var3 var4, by (date_q)

rename date_q date
format date %tq
tsset date

For whatever reason this "method" of listing all variables does not work with the tscollap command which is why I stuck with collapse.

Concerning the point you made on how to fix the problem:
What you describe about the difference as being simply between the last day of each quarter and the first is exactly right. This is what I experience. However, I do not see how I could now format the quarterly data differently to get the desired end day of the quarter as the displaying format. This is where I would need help.

Thanks for your help!

Best
Hans

[Nick Cox]

Thanks for the details.

Although you did not use dataex (SSC), reconstructing your date as first given is easy:

. clear

. set obs 189
obs was 0, now 189

. gen date = 34 + _n

. format date %tq

. l if _n == 1 | _n == _N

     +--------+
     |   date |
     |--------|
  1. | 1968q4 |
189. | 2015q4 |
     +--------+

In short, date is a perfectly formed quarterly date.

Your second and longest code block cannot be run as you don't give sample data. Copying that here

gen date_q = qofd(date) /* with date being a monthly time variable
format date_q %tq

tsunab allvars: _all
loc vars_to_exclude "date_q var1 var2 var3 var4"
collapse (last) `:list allvars - vars_to_exclude' (sum) var1 var2 var3 var4, by (date_q)

rename date_q date
format date %tq
tsset date

qofd() only applies to daily date variables. But evidently you had one such; otherwise it is difficult to see how that code could have worked.

As for your dates showing up as (e.g.) 01.01.1970 again I have to guess, but it seems that the command you don't show us is equivalent to assigning a display format %tq_D.N.CY

. di %tq_D.N.CY yq(1970, 1)
  01.01.1970

I don't think you can get the last day of the quarter directly through a quarterly display format. But you can assign value labels.

Here's the trick. The last day of this quarter is the day before the first day of the next quarter. The first day of the next quarter is an easy calculation based on dofq() Hence we just have to format that and put the resulting string into a value label. Here's some code; it could be made a little shorter at the cost of some greater obscurity.

forval i = 1/`=_N' {
    local thisq = date[`i']
    local lastd : di %td_D.N.CY dofq(`thisq' + 1) - 1
    label define myquarter `thisq' "`lastd'", modify
}

label val date myquarter
l if _n == 1 | _n == _N

     +------------+
     |       date |
     |------------|
  1. | 31.12.1968 |
189. | 31.12.2015 |
     +------------+

[Hans Albrecht]

Thanks a lot Nick!

Assigning the labels works perfectly but I think I will have to find a way somehow to change the data itself.

The data as calculated by my Stata code must be exported to Excel on a regular basis every quarter. Even if I set the format to normal quarterly data (2015Q1) in Stata once you export it to Excel it will give you the daily format of 01.01.2015. The same happens even with the changed value labels (31.03.2015) so I somehow have to deal with this.

I suppose in the worst case I will just have to solve this in Excel. Thanks a lot for your time and help.

Best
Anselm

[Nick Cox]

(The sudden appearance of "Anselm" may imply that, contrary to request, you are not using a full real name.)

Solving this in Excel really is the worst case. You have all the ingredients you need for just about any Stata solution (although, once more, you don't give any code for how you export to Excel).

For example, you can take your value labels and produce the corresponding string variable. Then you can map that to a daily date variable.

decode date, gen(sdate)
gen ddate = daily(sdate, "DMY")
format ddate %td
l if _n == 1 | _n == _N

     +---------------------------------------+
     |       date          sdate       ddate |
     |---------------------------------------|
  1. | 31.12.1968     31.12.1968   31dec1968 |
189. | 31.12.2015     31.12.2015   31dec2015 |
     +---------------------------------------+

[Hans Albrecht]

Hi Nick,

what took you a coupld of minutes (I suppose), took me the last hour to get to the second line of that code. Thank you very much. This solved it perfectly!

Cheers
Hanselm

PS: I go by Hans mostly since I migrated as the pronounciation of Anselm has resulted in quite substantial deformations of my beloved birthname and spending the beginning of each conversation with pronounciation lessons. If we are being strict here and go by passport names I am indeed not using my full real name, but then again: none of my friends do and aren't we all friends here! ;-)

[Nick Cox]

Thanks for closure and explanations.