Quarterly date variable

[Lisa Wilson]

Hi, I am struggling a bit with dates in Stata. I have a variables that has the form (199103, 199106, 199109, 199112, 199203, 199206....). The variable period represents the date for my quarterly data. The first four digits are the year and the last two digits are the quarter. I want to convert the variable to the format 1991q1, 1991q2, 199q3, 1991q4 and so on). Then, to tell Stata that it represents a date variable.

Initially, the code

 format periods %tq

results in periods being converted to a variable that looks exactly the same for all entries (e.g. 2.0e+05 )

Can anyone help in that please?

Thanks and Happy new year to you all.
Lisa

[Lisa Wilson]

I also tried

 gen  fqdate = quarterly(periods, "YQ")

but I got an error message

 type mismatch
r(109);

I use dataex to show a sample of my data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long period float gdp
198312 11.7282
198403 12.8756
198406 10.8643
198409  7.4104
198412  6.0227
198503  8.9046
198506  6.3087
198509  8.8616
198512   5.432
198603  5.7989
198606  3.4901
198609  5.7761
198612  4.3823
198703  5.8463
198706  7.4011
198709  6.7168
198712 10.3538
198803  5.5181
198806  9.5237
198809  7.2397
198812  8.8219
198903  8.7496
198906  7.5118
198909  6.0453
198912  3.6774
199003  9.1395
199006  5.8199
199009  3.7196
199012  -.4107
199103  2.1151
199106  5.9897
199109  4.9598
199112  3.9753
199203  6.6242
199206  7.1751
199209  5.9313
199212  6.9198
199303  3.0564
199306   4.913
199309  4.4413
199312  7.6613
199403  6.0179
199406  7.6955
199409  4.6172
199412  6.9417
199503  3.7209
199506  3.1972
199509   5.452
199512  4.9152
199603  4.8874
199606  8.8111
199609  4.9263
199612  6.4296
199703  5.6674
199706  7.3245
199709  6.7059
199712  4.5155
199803  4.6957
199806  4.8089
199809  6.9235
199812  8.0702
199903  5.3097
199906  4.7351
199909  6.6601
199912  9.1004
200003  4.2947
200006 10.2321
200009   3.114
200012  4.5118
200103  1.3744
200106   5.053
200109   .0414
200112  2.3437
200203  5.0687
200206  3.7586
200209   3.795
200212  2.4393
200303   4.628
200306  5.1028
200309  9.2542
200312   6.761
200403  5.9364
200406  6.5967
200409  6.2593
200412  6.4405
200503  8.2519
200506  5.1019
200509  7.3241
200512   5.445
200603  8.2327
200606  4.4962
200609  3.1882
200612   4.619
200703  4.8283
200706  5.4212
200709  4.1512
200712  3.2117
200803  -.4595
200806  4.0019
200809   .8126
end

[Clyde Schechter]

The %tq format (and other Stata date display formats) will only work properly when the underlying variable is a correct Stata internal format numeric date --which yours are not. You have a numeric coding that is easy for humans to read, but not appropriate for Stata to work with.

So first we have to convert these numbers to Stata internal format dates.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float lisa_date
199103
199106
199109
199112
199203
199206
end

//    CONVERT THIS TO A STATA INTERNAL FORMAT QUARTERLY DATE
//    STEP 1: EXTRACT YEAR AND MONTH
gen year = int(lisa_date/100)
gen month = mod(lisa_date, 100)

//    STEP 2: CALCULATE DAILY DATE [mdy()] AND THEN MAKE THAT QUARTERLY [qofd()]
gen qdate = qofd(mdy(month, 1, year))

​​​​​​​//    FINALLY APPLY A DATE FORMAT TO MAKE IT LOOK NICE IN DISPLAY
format qdate %tq

list, noobs clean

[Lisa Wilson]

Thanks a lot Clyde. It worked very well. I always struggle with dates in Stata. Thank you

[William Lisowski]

We all struggle, to some degree, in working with dates and times in Stata. If you have not already done so, you should thoroughly review the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF. After that, the help datetime documentation will usually be enough to point the way. All Stata manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

Even with a few year's experience, I never write date and time code without checking with help datetime, if not when I'm writing the code, then when the code I've written fails, as it inevitably does when I haven't checked first.

Added: Crossed with #6, where the much more experienced Clyde Schecter makes me feel better about always needing to check the documentation, by explaining how complicated the matter really is.

[Clyde Schechter]

I always struggle with dates in Stata.

To some extent, we all do.

At a very fundamental level, dates are inherently problematic because there are so many different ways of representing them in general use. We get our data sets from a variety of sources, and different sources often use different ways of representing dates. Even the same source often is inconsistent: I've seen plenty of data sets that mix-and-match dates in formats like 2jan2017, 1/2/17, and 20170102 all in the same dataset (and sometimes even in the same string variable)!

When there are so many different ways of writing dates, and when computations with dates require a regularized, uniform approach, then necessarily the apparatus needed to navigate among the various representations is complicated. It has to be in order to have sufficient flexibility for the task. That's why it seems like Stata has a million different functions for going between different types of date representations. But that makes it hard to remember which function does what, and exactly what the syntax for each one is, even though Stata has taken a pretty systematic approach to the names and syntax it gives these functions.

I think the fundamental thing that has to be remembered is that any calculations with dates in Stata requires the use of Stata internal format (SIF) dates, and that these SIF dates are counts of the number of time units from 1 Jan 1960 to the given date. (That is, a daily SIF date is the number of days from 1 Jan 1960; a quarterly SIF date is the number of quarters from 1 Jan 1960. A clock SIF datetime is the number of milliseconds from 1 Jan 1960 00:00:00.) So if you have a numeric variable that looks like a date to the human eye, you know right away it can't be right. It has to be a number that generates no immediate brain recognition as a date, and it must be of the right magnitude given the dates being represented and the unit of time involved.

The chapter on datetimes in [D] is well written and has lots of examples. But it is, of course, impossible to remember all the details for long. Everyone who uses Stata with any regularity needs to read this chapter, and probably re-read it periodically as well. Fortunately -help datetime- is also very well organized and has lots of internal links to help you quickly track down the right function. So if you are familiar with the general concept and have read the manual chapter a few times, most of the time you can find what you need in the help file without too much difficulty. But I don't think even the most experienced among us can consistently handle dates without going back to the help files, and sometimes to the manual: we may get really good at handling a few specific types of date representations that come up most often in our work. But when we encounter something infrequently, memory just isn't adequate.

All of that said, Nick Cox recently authored a program -numdate- which can be obtained from SSC. It's pretty good at "looking" at both string and human-readable-numeric dates and then figuring out the appropriate transformations for you. It's not full blown artificial intelligence, but it certainly handles a wide variety of cases with relatively little effort.

Added: Crossed with #5, where William Lisowski makes most of the same points far more succinctly!

[Clyde Schechter]

William Lisowski messged me privately to ask if I meant the chapter on datetimes in [U] (rather than in [D] which is almost the same as what's in -help datetimes-.) He is indeed right. Learning the basics from Chapter 24 Working with dates and times in [U] is the best starting point. Sorry for the misdirection.

[Nick Cox]

All the advice here is excellent. But because there are so many date functions in Stata, there is usually more than one way to do it. Here's another, a variant on one of Clyde's that just requires noticing that 1 2 3 4 are 3 6 9 12 divided by 3.

clear
input float lisa_date
199103
199106
199109
199112
199203
199206
end
 
gen qdate = yq(floor(lisa_date/100), mod(lisa_date, 100)/3) 
format qdate %tq 

list , sep(0) 

     +-------------------+
     | lisa_d~e    qdate |
     |-------------------|
  1. |   199103   1991q1 |
  2. |   199106   1991q2 |
  3. |   199109   1991q3 |
  4. |   199112   1991q4 |
  5. |   199203   1992q1 |
  6. |   199206   1992q2 |
     +-------------------+

[William Lisowski]

While Nick's code wins for brevity and elegance, Clyde's code wins for pedagogy.

Clyde's code instructs us in a general approach: initially convert whatever you are given into a SIF date of some form (a daily date in this example), then use Stata SIF-to-SIF conversion functions as required to convert that SIF date into the SIF date with the periodicity you need (a quarterly date in this example), and then apply the appropriate format to that result. He could have created a SIF monthly date as a more obvious starting point, but then would have had to convert it first to a SIF daily date before converting that to a quarterly date, since there is no function that goes directly from monthly to quarterly.

Clyde's approach is consistent with the principles discussed in his post at #6, especially, for me, dealing with the seemingly infinite varieties of dates and times with my most definitely (small) finite capacity for remembering fiddly details.

[Fadi Ansar]

Can anyone please help me the command to convert qaurterly data into yearly .Whatever Command I was used ,it wasnt working

[Clyde Schechter]

While the words quarterly and yearly appear in your post, it really isn't on the topic of this thread, which is concerned with conversions of date variables. Converting quarterly to yearly date variables raises other issues, such as what is the most meaningful way to aggregate four quarterly observations into a single yearly summary value.

So, because it is important to keep threads on topic, I suggest you re-post your question as a new topic in this Forum. Also, you need to provide more information about your problem and your data. It makes a big difference what the variables you are working with mean in the real world. Also, "Whatever Command I was used ,it wasnt working" is really not helpful at all. If you cannot remember what the command was and describe (or, better still, actually show) what went wrong, better not to even mention it. I would also suggest posting example data--it is sometimes possible to suggest code without an example data, but that is the exception and it will probably speed up solving your problem if you show an example. The helpful way to do that is with the -dataex- command. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.