Adding days between 2 specific dates

[Navin Singh]

Hi all,

I have a date variable which has specific dates for a company, E.G. Company A mentions 01-01-2014 and the next observation is on 31-01-2014. I want to create a new daily observation for each day in between. I was trying to use tsappend but this doesnt work, any help?

Greetings,
Navin

[Nick Cox]

Why do you want to do this? From your example your dataset will be mostly missings. I'd back up and say why you think this will help you. There are usually much better solutions than using tsfill, which may be the command you are seeking.

[Navin Singh]

I will fill the blanks with the last value recorded for a different variable. I need the additional days in my dataset to allow for testing. I set my data to paneldata with tsset and tried tsfill but it doesnt create additional days for my data variable.

[Nick Cox]

It works for me. Do please read and act on FAQ Advice #12 which explains that you need to show data and code and results whenever you tried and failed to get what you want.

. clear

. set obs 2
number of observations (_N) was 0, now 2

. gen dailydate = cond(_n == 1, mdy(1,1,2014), mdy(1,31,2014))

. format dailydate %td

. tsset dailydate
        time variable:  dailydate, 01jan2014 to 31jan2014, but with a gap
                delta:  1 day

. tsfill

. l

     +-----------+
     | dailydate |
     |-----------|
  1. | 01jan2014 |
  2. | 02jan2014 |
  3. | 03jan2014 |
  4. | 04jan2014 |
  5. | 05jan2014 |
     |-----------|
  6. | 06jan2014 |
  7. | 07jan2014 |
  8. | 08jan2014 |
  9. | 09jan2014 |
 10. | 10jan2014 |
     |-----------|
 11. | 11jan2014 |
 12. | 12jan2014 |
 13. | 13jan2014 |
 14. | 14jan2014 |
 15. | 15jan2014 |
     |-----------|
 16. | 16jan2014 |
 17. | 17jan2014 |
 18. | 18jan2014 |
 19. | 19jan2014 |
 20. | 20jan2014 |
     |-----------|
 21. | 21jan2014 |
 22. | 22jan2014 |
 23. | 23jan2014 |
 24. | 24jan2014 |
 25. | 25jan2014 |
     |-----------|
 26. | 26jan2014 |
 27. | 27jan2014 |
 28. | 28jan2014 |
 29. | 29jan2014 |
 30. | 30jan2014 |
     |-----------|
 31. | 31jan2014 |
     +-----------+

[Navin Singh]

code:

tsset
panel variable: ui (weakly balanced)
time variable: date2, 31oct2012 to 31may2013
delta: 1 day

. list ui date2

+-------------------+
| ui date2 |
|-------------------|
1. | 303 12dec2012 |
2. | 304 08jan2013 |
3. | 496 01nov2012 |
4. | 497 12nov2012 |
5. | 498 13nov2012 |
|-------------------|
6. | 499 05feb2013 |
7. | 500 07feb2013 |
8. | 501 18feb2013 |
9. | 502 19feb2013 |
10. | 503 21feb2013 |
|-------------------|
11. | 504 04mar2013 |
12. | 505 08mar2013 |
13. | 506 08apr2013 |
14. | 507 09apr2013 |
15. | 508 23apr2013 |
|-------------------|
16. | 509 29apr2013 |
--Break--
r(1);

. tsfill

. list ui date2

+-------------------+
| ui date2 |
|-------------------|
1. | 303 12dec2012 |
2. | 304 08jan2013 |
3. | 496 01nov2012 |
4. | 497 12nov2012 |
5. | 498 13nov2012 |
|-------------------|
6. | 499 05feb2013 |
7. | 500 07feb2013 |
8. | 501 18feb2013 |
9. | 502 19feb2013 |
10. | 503 21feb2013 |
|-------------------|
11. | 504 04mar2013 |
12. | 505 08mar2013 |
13. | 506 08apr2013 |
14. | 507 09apr2013 |
15. | 508 23apr2013 |
|-------------------|
16. | 509 29apr2013 |



Hope this helps?

[Nick Cox]

Try another tsset which has as side-effect sorting the data once more.

[Navin Singh]

tsset
panel variable: ui (weakly balanced)
time variable: date2, 31oct2012 to 31may2013
delta: 1 day

. tsfill

. list ui date2

+-------------------+
| ui date2 |
|-------------------|
1. | 303 12dec2012 |
2. | 304 08jan2013 |
3. | 496 01nov2012 |
4. | 497 12nov2012 |
5. | 498 13nov2012 |
|-------------------|
6. | 499 05feb2013 |
7. | 500 07feb2013 |
8. | 501 18feb2013 |
9. | 502 19feb2013 |
10. | 503 21feb2013 |
|-------------------|
11. | 504 04mar2013 |
12. | 505 08mar2013 |
13. | 506 08apr2013 |
14. | 507 09apr2013 |
15. | 508 23apr2013 |
|-------------------|
16. | 509 29apr2013 |
--Break--
r(1);

.

[William Lisowski]

You misunderstood Nick's advice. After the tsfill, repeat your tsset command. The observations created by tsfill are at the end of the dataset, and another tsset will sort them into the correct order.

tsfill
tsset
list ui date2

[Navin Singh]

This didn't work either...

[Navin Singh]

tsfill

. tsset
panel variable: ui (weakly balanced)
time variable: date2, 31oct2012 to 31may2013
delta: 1 day

. list date2

+-----------+
| date2 |
|-----------|
1. | 12dec2012 |
2. | 08jan2013 |
3. | 01nov2012 |
4. | 12nov2012 |
5. | 13nov2012 |
|-----------|
6. | 05feb2013 |
7. | 07feb2013 |
8. | 18feb2013 |
9. | 19feb2013 |
10. | 21feb2013 |
|-----------|
11. | 04mar2013 |
12. | 08mar2013 |
13. | 08apr2013 |
14. | 09apr2013 |
15. | 23apr2013 |
|-----------|
16. | 29apr2013 |
--Break--
r(1);

.

[William Lisowski]

Can I point out that the data you show us includes just one observation per ui? Perhaps the observations you are looking at are ones that do not have a second observation to fill in between.

For further advice, provide a reproducible example of the problem. Select a subset of your data with two or three values of ui with at least two observations per ui, show us the data, run tsfill, and show us the output.

Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

[Navin Singh]

I just solved the problem, Thanks for the support and I will follow the proper guidelines you pointed out in the future!