Converting a variable to a quarterly data

[Michael Craig]

Hey there,

I imported data which was formatted like Q12007 and then reshaped from wide to long, eliminated the "Q". Now I want to convert the variable called quarter into a useful quarterly data that can be merged with another database.

Any idea how to do that? I tried to create a new date variable based on "quarter" but it was not working.

Here is an excerpt from the data (some data for Q is missing but that does not relate to the question).

* Example generated by -dataex-. To install: ssc install dataex
clear
input str61 Description str7 ID_S str9 Datatypes float quarter str18 Q
"DR HOENLE AG - NET PROCEEDS FROM SALE/ISSUE C" "D13410M" "WC04251A" 12007 ""    
"DR HOENLE AG - EARNINGS BEF INTEREST & TAXES"  "D13410M" "WC18191A" 12013 "1490"
"DR HOENLE AG - NET PROCEEDS FROM SALE/ISSUE C" "D13410M" "WC04251A" 22014 ""    
"DR HOENLE AG - RESEARCH & DEVELOPMENT"         "D13410M" "WC01201A" 12015 "236" 
"DR HOENLE AG - PROPERTY, PLANT & EQUIP - NET"  "D13410M" "WC02501"  22010 "6027"
"DR HOENLE AG - PROPERTY, PLANT & EQUIP - NET"  "D13410M" "WC02501"  12009 "6210"
end

[Michael Craig]

12007 basically means first quarter in 2007 or 22010 means second quarter of 2010 etc.

Maybe I should use some string to extract the year and the number for quarter and then put it together?

[Nick Cox]

Thanks for your data example. I can't comment on the code you tried to explain what was wrong.

Solutions follow from reading

help datetime 

and knowing about other functions. There are many functions to choose from but https://www.stata-journal.com/sjpdf....iclenum=dm0058 highlights those I have found repeatedly useful, including all those mentioned here.

I added code for working with your original string variable, which involves about as much code as working with the numeric variable.

clear
input float quarter
12007
12013
22014
12015
22010
12009
end
 
gen qdate = yq(mod(q, 10000), floor(q/10000)) 

* original string variable reconstructed 
gen sqdate = "Q" + string(quarter)  

gen qdate2 = quarterly(substr(sqdate, 3, 4) + " " + substr(sqdate, 2, 1), "YQ") 

format qdate*  %tq 
 
list, sep(0) 

     +------------------------------------+
     | quarter    qdate   sqdate   qdate2 |
     |------------------------------------|
  1. |   12007   2007q1   Q12007   2007q1 |
  2. |   12013   2013q1   Q12013   2013q1 |
  3. |   22014   2014q2   Q22014   2014q2 |
  4. |   12015   2015q1   Q12015   2015q1 |
  5. |   22010   2010q2   Q22010   2010q2 |
  6. |   12009   2009q1   Q12009   2009q1 |
     +------------------------------------+

.

Typically it's a two-step process.

1. Create the numeric variable you need.

2. Apply an appropriate date display format.

[Michael Craig]

Hallo Nick,

this is perfect. I ran the code for the entire dataset (4 million observations) and it seems that I can now use the variable qdate2 as a quarterly date!

Thanks for the link. This is however probably something not so straightforward as it requires some manual work. (maybe it is better for me next time to prepare the date in Excel before so that Stata can better understand that).

I guess it is now possible to merge this data with another dataset based on the unique ID (called ID_S) and on qdate2. This is what I am gonna do next.

[Nick Cox]

Thanks for the thanks, but I don't understand this comment (or is it a question):

This is however probably something not so straightforward as it requires some manual work. (maybe it is better for me next time to prepare the date in Excel before so that Stata can better understand that).

I've given you two ways to get a quarterly date variable, one from your string variable and one from your numeric variable. It seems that something is still puzzling you, but I don't know what. As you've indicated that the numeric variable is in your Stata dataset, I can't see what else you might need to do.

[Michael Craig]

I am quite new to Stata, learning it by my own on a trial and error basis. It was just my personal impression as some things seem straightforward to me, while other things are not. However, this is always the case when I learn new commands like the substr command, which I did not know before. So there is nothing puzzling. It is simply new to me and part of the learning process. Again thanks!

[Nick Cox]

OK. Part of the long learning process is the important detail that commands and functions are different in Stata. In particular, substr() is a function, not a command. This is more than just terminology, as the ways you use commands and the ways you use functions are different. Again, the paper linked earlier explains.