Generate a new date variable from a panel data

[Rochelle Zhang]

Dear all,
I tried coding (see below) to generate a new date variable in month from a large panel data with ticker and a date variable that gives the star and end date for each firm. Below I show a few observations.

clear
input str6 ticker long statpers
"TLMR" 20140417
"TLMR" 20140515
"TLMR" 20140619
"TLMR" 20140717
"TLMR" 20140814
"TLMR" 20140918
"TLMR" 20141016
"TLMR" 20141120
end

I want to add a month variable for each observation, 201404, 201405, …. 201411 , 8 months for this ticker. What I tried (my coding is inefficient).

sort ticker statpers
by ticker: gen s_date=statpers if _n==1
 
by ticker: gen e_date=statpers if _n==_N
bysort ticker: carryforward s_date, gen(s_fill)
 
 gsort ticker - e_date
 
bysort ticker: carryforward e_date, gen(e_fill)

at this point, I think s_fill e_fill are no longer edate, I can’t generate month variable out of it. Can you show me a better and correct way ?

Thank you,

Rochelle

[Wouter Wakker]

Maybe you mean like this?

clear
input str6 ticker long statpers
"TLMR" 20140417
"TLMR" 20140515
"TLMR" 20140619
"TLMR" 20140717
"TLMR" 20140814
"TLMR" 20140918
"TLMR" 20141016
"TLMR" 20141120
end

gen month = floor(statpers / 100)
bysort ticker (statpers): gen long start = statpers[1]
bysort ticker (statpers): gen long end = statpers[_N]
list in f


     +--------------------------------------------------+
     | ticker   statpers    month      start        end |
     |--------------------------------------------------|
  1. |   TLMR   20140417   201404   20140417   20141120 |
     +--------------------------------------------------+

Although personally I would prefer to have a SIF (Stata internal form) date variable.

[Rochelle Zhang]

Thanks so much Wouter !!!

1. would it be correct to say your start and end variables are edate variables

when I do

format start %tm

the display is "2.0e+07" , so I doubt it is an edate. I am confused about datetime variables in stata.

2. I will merge this dataset with another panel data using ticker and month 1:1, is the month variable in the data ready for merge, or do I have to generate another month variable?

my other data set has month variable type INT, FORMAT : %9.0g

[Wouter Wakker]

1. No, these are just integers, to get a SIF monthly variable you would need to use functions ym() or monthly().

gen month = ym(floor(statpers / 10000), real(substr(string(statpers, "%10.0g"), 5, 2)))
bysort ticker (month): gen long start = month[1]
bysort ticker (month): gen long end = month[_N]
format month start end %tm

2. To merge you would need the date variables in both datasets to be the same, so either SIF monthly variables or integers like in #2. I recommend converting your date variables in both datasets to SIF before merging.

Working with datetime variables can be quite difficult, help datetime is always a good place to return to if you run into probems with date variable conversions.

[Rochelle Zhang]

Wouter, thank you for being so helpful. I understand much better about SIF, HIF, now.


Best wishes!