Transforming a string variable into a time/date

[Rolf Bosch]

I have a string variable called month containing; "Jan 2018" till "Dec 2020". How do I separate this variable so I can for instance make a regression with data of only 2018 or data of Jan 2018, Jan 2019, Jan 2020?

[Nick Cox]

clear 
input str8 given 
"Jan 2018"  
"Dec 2020"
end 

gen mdate = monthly(given, "MY")
format mdate %tm 
gen year = real(word(given, 2))

list 

     +---------------------------+
     |    given     mdate   year |
     |---------------------------|
  1. | Jan 2018    2018m1   2018 |
  2. | Dec 2020   2020m12   2020 |
     +---------------------------+

[Rolf Bosch]

Thanks for your respond but I still don't get what I want;

I have a dataset containing household incomes of thousands of households with income values for each month from jan 2018 untill dec 2020.
like this;

month	household income
Jan 2018	45
Jan 2018	34
Feb 2018	66
Feb 2018	77

untill dec 2020
but I want something like this:

month	household income	mdate	year
Jan 2018	45	2018m1	2018
Jan 2018	34	2018m1	2018
Feb 2018	66	2018m2	2018
Feb 2018	77	2018m2	2018

There must be an easy way to do this

[Nick Cox]

Nothing in your example seems at odds with my solutions. Please give a data example using dataex or otherwise explain what my code is missing.

[Rolf Bosch]

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 month
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
end

[Rolf Bosch]

I don't know how to show the other variables

[Rolf Bosch]

If I run your code I lose all month and end with this;
given mdate year
Jan 2018 2018m1 2018
Dec 2020 2020m12 2020

[Nick Cox]

Sorry, but I am almost lost here. The example at #5 is exactly what I was presuming. The result in #7 seems to be what you want. For your own data you should not type clear and you need not generate a monthly date variable because you already have it.

[Rolf Bosch]

okay I don't need mdate but still I try year I only get;

. gen year = real(word(given, 2))
(6,089,149 missing values generated)


. tabulate year

year | Freq. Percent Cum.
------------+-----------------------------------
2018 | 12 33.33 33.33
2019 | 12 33.33 66.67
2020 | 12 33.33 100.00
------------+-----------------------------------
Total | 36 100.00

So I get missing values, observation 333 in Jan 2018 is not matching year = 2018 but gives year = missing value

[Nick Cox]

Ok, so you need to give a data example of data where it doesn't work. The code manifestly works for data like #5. Besides, you can think too about what is happening. The code takes word 2 of the string date and turns it into a number. The problem could be that there is no word 2, as there is only one word. Or something else.

dataex month if year == .

[Rolf Bosch]

dataex month if year == .

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 month
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
"Jan 2018"
end

Sorry for all trouble I really just have basic Stata knowledge, I have no idea why it is not working this should be so easy. I have tried a lot of things but don't what is wrong.

[Nick Cox]

The "spaces" may be uchar(160).

. di wordcount("Jan 2018")
2

. di wordcount("Jan" + uchar(160) + "2018")
1

So see if

 
replace month = subinstr(month, uchar(160), " ", .)

does anything to change the variable. if so, then try again.

[Rolf Bosch]

I tried something and it look likes it works, thank you for your time!

[Rolf Bosch]

Nevermind haha; I still need to know one thing;

| date date2 |
|---------------------|
1. | Jan 2018 Jun 3725 |
2. | Jan 2018 Jun 3725 |
3. | Jan 2018 Jun 3725 |
4. | Jan 2018 Jun 3725 |
5. | Jan 2018 Jun 3725 |
|---------------------|
6. | Jan 2018 Jun 3725 |
7. | Jan 2018 Jun 3725 |
8. | Jan 2018 Jun 3725 |
9. | Jan 2018 Jun 3725 |
10. | Jan 2018 Jun 3725 |

Which code do I need to adjust Jun and 3725 to Jan and 2018?

[Rolf Bosch]

Nevermind I also fixed this, thank for your help