Converting variable "Date" from string to numeric

[Matthew Hosseini]

Hello Forum:

I have a variable named Date and in string format, displayed in the Data Editor as:

Date
Q1 2001
Q2 2001
Q3 2001
Q4 2001
....

Q1 2017
Q2 2017
Q3 2017
Q4 2017

Its type and format are str263 and %263s, respectively.

My question is how to convert Date into a numeric format?

Please note, I've been a heavy user of sas and matlab for a number of years and have just started learning about STATA today. Your help is greatly appreciated.

Thank You
Matthew

[William Lisowski]

Welcome to Statalist, and for that matter, to Stata!

Stata's online help is very comprehensive.

You will want to follow the instructions for converting your string variable to a SIF monthly date variable following the guidance in help datetime, which is without a doubt the most visited documentation on my system, with the second-most-visited being Chapter 24 (Working with dates and times) of the Stata User's Guide PDF available from the PDF Documentation item on Stata's Help menu. Before working with dates and times, any Stata user should read the very detailed Chapter 24 thoroughly. After that, the help documentation will usually be enough to point the way. Some people may be able to remember everything without have to continually refer to the documentation, but I for one am not such a person.

As a more general piece of advice, when I began using Stata in a serious way, I started by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. All of these manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through Stata's Help menu. The objective in doing this was not so much to master Stata as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax.

[Nick Cox]

It's a bit alarming that you are taking up str263: look carefully for garbage somewhere in the variables concerned.

One way to approach this:

. clear 

. input str7 qin 

           qin
  1. "Q1 2001"
  2. "Q2 2001"
  3. "Q3 2001"
  4. "Q4 2001"
  5. end 

. replace qin = subinstr(qin, "Q", "", .) 
(4 real changes made)

. gen qout = quarterly(qin, "QY") 

. format qout %tq 

. list 

     +-----------------+
     |    qin     qout |
     |-----------------|
  1. | 1 2001   2001q1 |
  2. | 2 2001   2001q2 |
  3. | 3 2001   2001q3 |
  4. | 4 2001   2001q4 |
     +-----------------+

Spellings are versa vice there: it's SAS, MATLAB and Stata according to their respective companies.

[William Lisowski]

Following on Nick's example, and demonstrating the distinctive substring syntax for Stata,

. input str7 qin 

           qin
  1. "Q1 2001"
  2. "Q2 2001"
  3. "Q3 2001"
  4. "Q4 2001"
  5. end

. generate qout = quarterly(substr(qin,2,.),"QY")

. format qout %tq 

. list 

     +------------------+
     |     qin     qout |
     |------------------|
  1. | Q1 2001   2001q1 |
  2. | Q2 2001   2001q2 |
  3. | Q3 2001   2001q3 |
  4. | Q4 2001   2001q4 |
     +------------------+

[William Lisowski]

Following on Nick's example, and demonstrating some distinctive substring syntax for Stata,

. input str7 qin

           qin
  1. "Q1 2001"
  2. "Q2 2001"
  3. "Q3 2001"
  4. "Q4 2001"
  5. end

. generate qout = quarterly(substr(qin,2,.),"QY")

. format qout %tq

. list

     +------------------+
     |     qin     qout |
     |------------------|
  1. | Q1 2001   2001q1 |
  2. | Q2 2001   2001q2 |
  3. | Q3 2001   2001q3 |
  4. | Q4 2001   2001q4 |
     +------------------+
​

[Matthew Hosseini]

Nick Cox Yes, agreed I noticed the rather large storage of 263 too, when viewing the properties of the variable.
William Lisowski I appreciate your welcome message and pointers for further readings.

Both recommended solutions worked and I found both useful: step-by-step vs. compact versions, as suggested by Nick and William.

Cosmetic Question: is there any reason for quarterly dates to appear as yyyyqq rather than qqyyyy?

[Nick Cox]

You can format quarterly dates either way: entirely your choice.

A check for long strings could be

list Date if length(trim(Date)) > 7

[Matthew Hosseini]

Thanks again.

I'm beginning to see a lot of similarities between sas and stata. Hopefully, the learning curve will not be that steep as I move into scrubbing data and performing various tests relating to predictive and time-series regressions.

[Nick Cox]

The learning curve is steep. You put in a little effort, you learn a lot. (What else could a learning curve be?)

[Matthew Hosseini]

So true.