Filling in date when missing in time series panel data

[Leslie Edwards]

Hello Stata friends! I have panel data that I have declared as time series based on a unique time variable named "elapsedtime" with 1 minute delta between each observation. I am now trying to fill in the date variable and components of the date (year, doy, hr, min) for those new observations created during the tsfill.

The following code did not work but I hope it is close.

replace time = (time[-n-1]+1) if time==.

replace time = time[-n-1]+1 if time>=.

This is an example of the data

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess date year doy hr min elapsedtime)
1 21827 2019 278 18 19 1517
1 21827 2019 278 18 20 1518
1 21827 2019 278 18 21 1519
1     .    .   .  .  . 1520
1     .    .   .  .  . 1521
1     .    .   .  .  . 1522
1     .    .   .  .  . 1523
1     .    .   .  .  . 1524
1     .    .   .  .  . 1525
1     .    .   .  .  . 1526
1     .    .   .  .  . 1527
end
format %td date

Thanks in advance for your thoughts on this

[Rich Goldstein]

I don't follow but I do note a couple of issues: (1) there is no variable called "time" in your dataex example; (2) your code has at least one typo - within the square brackets you want "_n" not "-n"

[Leslie Edwards]

Thanks Rich - variable "time" has been added to the data chunk below. The code was updated to reflect _n and not -n and is almost there, but this was a static replace instead of the time increasing by 1 minute for each observation.

This is the data before I used the above code.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess date year doy hr min elapsedtime) double time
1 21827 2019 278 18 19 1517 1885918740000
1 21827 2019 278 18 20 1518             .
1 21827 2019 278 18 21 1519 1885918860000
1     .    .   .  .  . 1520             .
1     .    .   .  .  . 1521             .
1     .    .   .  .  . 1522             .
1     .    .   .  .  . 1523             .
1     .    .   .  .  . 1524             .
1     .    .   .  .  . 1525             .
1     .    .   .  .  . 1526             .
1     .    .   .  .  . 1527             .
end
format %td date
format %tc time

And the code I used

replace time = time[_n-1]+1 if time>=.

Data after code was used. Note that I see an increase in time below after pasting the dataex data but the time appears as "05oct2019 18:21:00" when I browse the data.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess date year doy hr min elapsedtime) double time
1 21827 2019 278 18 19 1517 1885918740000
1 21827 2019 278 18 20 1518 1885918740001
1 21827 2019 278 18 21 1519 1885918860000
1     .    .   .  .  . 1520 1885918860001
1     .    .   .  .  . 1521 1885918860002
1     .    .   .  .  . 1522 1885918860003
1     .    .   .  .  . 1523 1885918860004
1     .    .   .  .  . 1524 1885918860005
1     .    .   .  .  . 1525 1885918860006
1     .    .   .  .  . 1526 1885918860007
1     .    .   .  .  . 1527 1885918860008
end
format %td date
format %tc time

[William Lisowski]

Time is measured in milliseconds, so to increment time by one second you need to add 1000, not by 1. Or in your case, to increment time by one minute you need to add 60000, not 1, and you can see from the difference between 1885918860000 and 1885918740000 of 120000 for values that are two minutes apart.

[Leslie Edwards]

Thank you William. Using the code below, the "time" variable was replaced with 1 minute intervals.

replace time= time[_n-1]+60000 if time>=.

Data after using the code:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID_Sess date year doy hr min elapsedtime) double time
1 21827 2019 278 18 19 1517 1885918740000
1 21827 2019 278 18 20 1518 1.8859188e+12
1 21827 2019 278 18 21 1519 1885918860000
1     . 2019   .  .  . 1520 1885918920000
1     . 2019   .  .  . 1521 1885918980000
1     . 2019   .  .  . 1522 1885919040000
1     . 2019   .  .  . 1523 1.8859191e+12
1     . 2019   .  .  . 1524 1885919160000
1     . 2019   .  .  . 1525 1885919220000
1     . 2019   .  .  . 1526 1885919280000
1     . 2019   .  .  . 1527 1885919340000
end
format %td date
format %tc time

[William Lisowski]

replace time = time[_n-1]+60000 if time>=.
replace min  = mm(time)   if min==.
replace hr   = hh(time)   if hr==.
replace date = dofc(time) if date==.
replace doy  = doy(date)  if doy==.
replace year = yofd(date) if year==.
list, clean abbreviate(12)

. list, clean abbreviate(12)

       ID_Sess        date   year   doy   hr   min   elapsedtime                 time  
  1.         1   05oct2019   2019   278   18    19          1517   05oct2019 18:19:00  
  2.         1   05oct2019   2019   278   18    20          1518   05oct2019 18:20:00  
  3.         1   05oct2019   2019   278   18    21          1519   05oct2019 18:21:00  
  4.         1   05oct2019   2019   278   18    22          1520   05oct2019 18:22:00  
  5.         1   05oct2019   2019   278   18    23          1521   05oct2019 18:23:00  
  6.         1   05oct2019   2019   278   18    24          1522   05oct2019 18:24:00  
  7.         1   05oct2019   2019   278   18    25          1523   05oct2019 18:25:00  
  8.         1   05oct2019   2019   278   18    26          1524   05oct2019 18:26:00  
  9.         1   05oct2019   2019   278   18    27          1525   05oct2019 18:27:00  
 10.         1   05oct2019   2019   278   18    28          1526   05oct2019 18:28:00  
 11.         1   05oct2019   2019   278   18    29          1527   05oct2019 18:29:00

[Leslie Edwards]

Thanks William - the code worked beautifully! Love the support from all the brilliant minds on this site!