Obtain annually and quarterly data in a date variable

[Enzo Wu]

Hi, Statalists

I have a problem dealing with a string date variable which is with annual and quarterly data at the same time, as the data following.
I wish I can convert the string date variable which is quarterly and annually into a date variable

  
 * Example generated by -dataex-. For more info, type help dataex  
 clear  
input str3 countryid str7 date float value
 
"ARG"    "2014"    0.3386826
"ARG"    "2015"    0.1357258
"ARG"    "2016"    0.3204299
"ARG"    "2017"    0.1795446
"ARG"    "2018"    0.3334255
"ARG"    "2019"    0.3455194
"ARG"    "2020"    0.3332398
"ARG"    "2013-Q1"    2185.1
"ARG"    "2013-Q2"    3251.5
"ARG"    "2013-Q3"    2832.1
"ARG"    "2013-Q4"    1552.9
"ARG"    "2014-Q1"    1128.5
"ARG"    "2014-Q2"    -1496.9
"ARG"    "2014-Q3"    2432.7
"ARG"    "2014-Q4"    3001
"ARG"    "2015-Q1"    3514.3
"ARG"    "2015-Q2"    3360.8
"ARG"    "2015-Q3"    2702.3
"ARG"    "2015-Q4"    2181.6


end

So far, I have attempted using yearly() and quarterly() commands; however, they didn't work as I expected.
And the code below is currently in my procedure.

    gen ydate = yearly(date, "Y")
    format ydate %ty
    gen qdate = quarterly(date, "YQ")
    format qdate %tq
    drop year
    gen year = yq(ydate, qdate)

And I expect the data would be like the data below

  

 
countryid    year    value
"ARG"    "2014"    0.3386826
"ARG"    "2015"    0.1357258
"ARG"    "2016"    0.3204299
"ARG"    "2017"    0.1795446
"ARG"    "2018"    0.3334255
"ARG"    "2019"    0.3455194
"ARG"    "2020"    0.3332398
"ARG"    "2013q1"    2185.1
"ARG"    "2013q2"    3251.5
"ARG"    "2013q3"    2832.1
"ARG"    "2013q4"    1552.9
"ARG"    "2014q1"    1128.5
"ARG"    "2014q2"    -1496.9
"ARG"    "2014q3"    2432.7
"ARG"    "2014q4"    3001
"ARG"    "2015q1"    3514.3
"ARG"    "2015q2"    3360.8
"ARG"    "2015q3"    2702.3
"ARG"    "2015q4"    2181.6

Appreciate your reading and expect your response

Enzo

[William Lisowski]

You cannot hope for a single Stata date variable to contain both yearly values and quarterly values, and then have Stata understand which is which.

Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

You seem to have two different datasets - one with quarterly data for one type of value, the other with annual data for another type of value - appended into a single dataset. This is not a helpful organization of data in Stata. An explanation of what your data represents might lead to advice on dealing with it in a way that is useful in Stata.

[Enzo Wu]

William Lisowski, I have appreciated your response. I will check Chapter 24 out.

About the dataset, it's an OECD sub-dataset that records the recent years quarterly and others yearly.

[William Lisowski]

it's an OECD sub-dataset that records the recent years quarterly and others yearly

Your example data in post #1 doesn't match this description. You have both yearly and quarterly data for 2013-2015, and it's hard to imagine a series with yearly values <1 and quarterly values >1000 - for the same years!

I think your example data must represent pieces of two different series. As such, it's difficult to give concrete advice on how to proceed. Here's a demonstration of some technique based on the code you presented.

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 countryid str1 series str7 date float value
"ARG" "A" "2014"    .3386826
"ARG" "A" "2015"    .1357258
"ARG" "A" "2016"    .3204299
"ARG" "A" "2017"    .1795446
"ARG" "A" "2018"    .3334255
"ARG" "A" "2019"    .3455194
"ARG" "A" "2020"    .3332398
"ARG" "B" "2013-Q1"   2185.1
"ARG" "B" "2013-Q2"   3251.5
"ARG" "B" "2013-Q3"   2832.1
"ARG" "B" "2013-Q4"   1552.9
"ARG" "B" "2014-Q1"   1128.5
"ARG" "B" "2014-Q2"  -1496.9
"ARG" "B" "2014-Q3"   2432.7
"ARG" "B" "2014-Q4"     3001
"ARG" "B" "2015-Q1"   3514.3
"ARG" "B" "2015-Q2"   3360.8
"ARG" "B" "2015-Q3"   2702.3
"ARG" "B" "2015-Q4"   2181.6
end
generate ydate = yearly(substr(date,1,4),"Y")
format ydate %ty
generate qdate = quarterly(date,"YQ")
format qdate %tq
list, clean abbreviate(12)
// make all series yearly ???
collapse (sum) value, by(countryid series ydate)
list, clean abbreviate(12)

. list, clean abbreviate(12)

       countryid   series      date      value   ydate    qdate  
  1.         ARG        A      2014   .3386826    2014        .  
  2.         ARG        A      2015   .1357258    2015        .  
  3.         ARG        A      2016   .3204299    2016        .  
  4.         ARG        A      2017   .1795446    2017        .  
  5.         ARG        A      2018   .3334255    2018        .  
  6.         ARG        A      2019   .3455194    2019        .  
  7.         ARG        A      2020   .3332398    2020        .  
  8.         ARG        B   2013-Q1     2185.1    2013   2013q1  
  9.         ARG        B   2013-Q2     3251.5    2013   2013q2  
 10.         ARG        B   2013-Q3     2832.1    2013   2013q3  
 11.         ARG        B   2013-Q4     1552.9    2013   2013q4  
 12.         ARG        B   2014-Q1     1128.5    2014   2014q1  
 13.         ARG        B   2014-Q2    -1496.9    2014   2014q2  
 14.         ARG        B   2014-Q3     2432.7    2014   2014q3  
 15.         ARG        B   2014-Q4       3001    2014   2014q4  
 16.         ARG        B   2015-Q1     3514.3    2015   2015q1  
 17.         ARG        B   2015-Q2     3360.8    2015   2015q2  
 18.         ARG        B   2015-Q3     2702.3    2015   2015q3  
 19.         ARG        B   2015-Q4     2181.6    2015   2015q4  

. // make all series yearly ???
. collapse (sum) value, by(countryid series ydate)

. list, clean abbreviate(12)

       countryid   series   ydate      value  
  1.         ARG        A    2014   .3386826  
  2.         ARG        A    2015   .1357258  
  3.         ARG        A    2016   .3204299  
  4.         ARG        A    2017   .1795446  
  5.         ARG        A    2018   .3334255  
  6.         ARG        A    2019   .3455194  
  7.         ARG        A    2020   .3332398  
  8.         ARG        B    2013     9821.6  
  9.         ARG        B    2014     5065.3  
 10.         ARG        B    2015      11759  

.

[Enzo Wu]

I think your example data must represent pieces of two different series. As such, it's difficult to give concrete advice on how to proceed. Here's a demonstration of some technique based on the code you presented.

My data is exactly what you demonstrate.

Although my current method is turning these yearly dates and quarterly dates into %td form, I think your demonstration is a preferable method.

I will take your advice and modify my code.

I am very grateful for your help!