Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

From |
Nick Cox <njcoxstata@gmail.com> |

To |
statalist@hsphsun2.harvard.edu |

Subject |
Re: st: Assign observations to the right week number |

Date |
Wed, 17 Oct 2012 11:14:14 +0100 |

I take it that you mean that a week starts on Wednesday (first day) and ends on Tuesday (last day). This explanation includes things you do know already: I am writing it out step by step because it may be helpful to others. In your case, each week can be characterised by the Wednesday that starts it (or the Tuesday that ends it, etc.). I will take the former. As we write, it's a Wednesday today and so the start of a week by that definition. . di dow(mdy(10,17,2012)) 3 Stata's -dow()- function, given daily dates, returns 3 for Wednesday (0 = Sunday). Build a small sandpit of dates centred on today . clear . set obs 7 obs was 0, now 7 . gen date = mdy(10,17,2012) + (_n - 4) . format date %td . l, sep(0) +-----------+ | date | |-----------| 1. | 14oct2012 | 2. | 15oct2012 | 3. | 16oct2012 | 4. | 17oct2012 | 5. | 18oct2012 | 6. | 19oct2012 | 7. | 20oct2012 | +-----------+ For today through to Saturday, today is the start of the week, but for yesterday back to Sunday it's a week earlier. Some general code is given by gen week_start = cond(dow(date) >= 3, date - dow(date) + 3, date - dow(date) - 4) . format week_start %td . l, sep(0) +-----------------------+ | date week_st~t | |-----------------------| 1. | 14oct2012 10oct2012 | 2. | 15oct2012 10oct2012 | 3. | 16oct2012 10oct2012 | 4. | 17oct2012 17oct2012 | 5. | 18oct2012 17oct2012 | 6. | 19oct2012 17oct2012 | 7. | 20oct2012 17oct2012 | +-----------------------+ Once you have a variable -week_start- it's the same for all days in the same week, and could be used for summary or aggregation. With this approach, first there is no need even for dates to be in sequence; also whether there are gaps in the data does not matter at all. For more discussion, see Cox, N.J. 2010. Stata tip 68: Week assumptions. Stata Journal 10(4): 682-685 Cox, N.J. 2012. Stata tip 111: More on working with weeks. Stata Journal 12(3): 565-569. On Wed, Oct 17, 2012 at 10:48 AM, "Marco Müller" <endlevel@gmx.ch> wrote: > I have to calculate weekly returns (from Wednesday to Wednesday) from daily returns for several companies. Therefore, I assigned the observations to week numbers per company. > > The problem is that some dates are missing, e.g. there is a week (from Wed to Wed) where only 4 entries exist. > I managed to assign observations to the right week if there are days other than Wednesday (Mon, Tue, Thu, Fri, Sat, Sun) are missing. > However, if Wednesday (= the cutoff day) is missing, Tuesdays should be chosen as cutoff day (if Tuesday is missing too, Monday should be chosen). With my code, it assigns all days to the same week number until the next Wednesday is available (see observations 16-22) Unfortunately I get stuck with that problem. > > Example & code are provided below (I'm sure there is a nicer way to compute it - i tried it with a nested forvalue expression - but I didn't manage it). > > > Thank you very much for your help. > > Best regards, > Marco > > > *---- Code example ---* > gen dow=dow(date) > gen check=1 if dow==3 > sort id check date > by id: gen n=_n if check==1 > gen N=. > > by id: replace N=n[_n-1] if n==. & n[_n-1]!=. > by id: replace N=n[_n-2] if n==. & n[_n-1]==. & n[_n-2]!=0 > by id: replace N=n[_n-3] if n==. & n[_n-1]==. & n[_n-2]==. & n[_n-3]!=0 > by id: replace N=n[_n-4] if n==. & n[_n-1]==. & n[_n-2]==. & n[_n-3]==. & n[_n-4]!=0 > by id: replace N=n[_n-5] if n==. & n[_n-1]==. & n[_n-2]==. & n[_n-3]==. & n[_n-4]==. & n[_n-5]!=0 > by id: replace N=n[_n-6] if n==. & n[_n-1]==. & n[_n-2]==. & n[_n-3]==. & n[_n-4]==. & n[_n-5]==. & n[_n-6]!=0 > by id: replace N=N[_n-1] if n!=. > *---- Code ends ----* > > > > > id = company, date=date, ret=return, dow= day of the week, check= Wednesday-dummy, n = week number per company, N = assigns days to the right week > > +---------------------------------------------------+ > | id date ret dow check n N | > |---------------------------------------------------| > 1. | 1 16/10/2012 -.010101 2 . . . | > 2. | 1 18/10/2012 .020202 4 . . 1 | > 3. | 1 19/10/2012 .009901 5 . . 1 | > 4. | 1 22/10/2012 -.0196078 1 . . 1 | > 5. | 1 23/10/2012 .02 2 . . 1 | > |---------------------------------------------------| > 6. | 1 24/10/2012 .0196078 3 1 2 1 | > 7. | 2 25/10/2012 .0192308 4 . . 2 | > 8. | 2 26/10/2012 .0188679 5 . . 2 | > 9. | 2 29/10/2012 -.0185185 1 . . 2 | > 10. | 2 30/10/2012 -.0283019 2 . . 2 | > |---------------------------------------------------| > 11. | 2 31/10/2012 -.0194175 3 1 3 2 | > 12. | 2 02/11/2012 -.019802 5 . . 3 | > 13. | 2 05/11/2012 -.010101 1 . . 3 | > 14. | 2 07/11/2012 -.0204082 3 1 4 3 | > 15. | 2 09/11/2012 -.03125 5 . . 4 | > |---------------------------------------------------| > 16. | 2 12/11/2012 .0430108 1 . . 4 | > 17. | 2 13/11/2012 .0206186 2 . . 4 | > 18. | 2 15/11/2012 .020202 4 . . 4 | > 19. | 2 16/11/2012 -.009901 5 . . 4 | > 20. | 2 19/11/2012 -.01 1 . . 4 | > |---------------------------------------------------| > 21. | 2 20/11/2012 -.010101 2 . . . | > 22. | 2 21/11/2012 -.0204082 3 1 5 . | > +---------------------------------------------------+ > * * For searches and help try: * http://www.stata.com/help.cgi?search * http://www.stata.com/support/faqs/resources/statalist-faq/ * http://www.ats.ucla.edu/stat/stata/

**Follow-Ups**:**Re: st: Assign observations to the right week number***From:*Nick Cox <njcoxstata@gmail.com>

**References**:**st: Assign observations to the right week number***From:*"Marco Müller" <endlevel@gmx.ch>

- Prev by Date:
**Re: st: replace part of observations in a string variable** - Next by Date:
**Re: st: replace part of observations in a string variable** - Previous by thread:
**st: Assign observations to the right week number** - Next by thread:
**Re: st: Assign observations to the right week number** - Index(es):